Contact Details for the Contract Contact

[Suzanne Stoakley]

I am trying to produce a report which lists the address, telephone number and e-mail address of the contact person assigned to our service contracts. After numerous attempts, I can work out the names of the main contact details for the organisation and for the service, but I haven't yet been able to do it for the contract contact. I have tried to find the table containing the details held in the "Assigned To" information box, but can't find that either.

Does anyone have any suggestions?

[Peter Row]

The table V_AHR_Contracts has a field ContractContactID which can be linked to V_AHR_Organisation_Contacts table to the get the contacts name and address amongst other things.

See the Ad-Hoc Reporting Reference Guide on your Start menu in Windows in the same place as the SPOCC (Live) shortcut for further details.

[Suzanne Stoakley]

Peter

I have tried this, linking the ContractContactID to the OrganisationContactID, because there doesn't appear to be anywhere else to link it to that works. Unfortunately this brings up the name of the person who is the main contact for the organisation and not the details of the person assigned to the contract, which shows on the contact tab under each contract. We often have different people as the main contact person to those who actually deal with the contract side of things.

[Peter Row]

I have just verfied my claim my set the Organisation contact to a different contact than the contact for a Contract that belongs to the same Organisation. Join the 2 tables I mentioned gives me the correct contact details.

Are you sure that the one you're looking at doesn't just happen to have the same person as both the Organisation contact and Contract contact?

[Debbie Bruce]

Hi Suzanne

I have just this morning created a very similar query, please see below the SQL for this. Please bear in mind I set this for only Payable contracts which you might want to edit. I have also not included email or telephone but you can just add them to this once run.

SELECT dbo_V_AHR_Contracts.OrganisationName, dbo_V_AHR_Contracts.ContractID, dbo_V_AHR_Contracts.ContractReference, dbo_V_AHR_Organisation_Contacts.Forename, dbo_V_AHR_Organisation_Contacts.Surname, dbo_V_AHR_Organisation_Contacts.Address1, dbo_V_AHR_Organisation_Contacts.Address2, dbo_V_AHR_Organisation_Contacts.Address3, dbo_V_AHR_Organisation_Contacts.Address4, dbo_V_AHR_Organisation_Contacts.Town, dbo_V_AHR_Organisation_Contacts.Postcode
FROM dbo_V_AHR_Organisation_Contacts RIGHT JOIN dbo_V_AHR_Contracts ON dbo_V_AHR_Organisation_Contacts.OrganisationContactID = dbo_V_AHR_Contracts.ContractContactID
WHERE (((dbo_V_AHR_Contracts.ContractStatus)="payable"));