Query Doubling Results

[CharlesWhiteman]

In My Db I have TblDatabase (contains company details) and TblContacts (contact details relating to the company joined on a one-many relationship.
I also have Tbl calls which records contact history with each company.

I made a query to output a report based on the source of the Company. What i am finding is that companies which have more than one contact, the contact history repeats itself by the number of contacts in the company, can anybody point me in the right direction. Here is my query:

SELECT DISTINCT TblDatabase.CompanyName, TblDatabase.RegisteredDate, TblDatabase.City, TblDatabase.WebSiteAddress, TblDatabase.BusinessDescription, TblCalls.[Call Note], TblDatabaseContacts.[First Name], TblDatabaseContacts.[Email Address], TblCalls.Result
FROM (TblDatabase INNER JOIN TblCalls ON TblDatabase.CompanyCode = TblCalls.CompanyCode) INNER JOIN TblDatabaseContacts ON TblDatabase.CompanyCode = TblDatabaseContacts.CompanyCode
WHERE (((TblDatabase.Source)=[forms].[FrmSourceReport].[ComboSource]));

 

[neileg]

Step back from the query and think what it is you want. If your contact history is linked to the company, then every time you have a company record, you get a copy of the history. If your contacts table is linked with the company, then every time you get a contact record you get the company. It follows therefore that you are going to get a copy of the contact history for every contact. No way round this in a single query.

So...

Why do you want this query? You don't view data in a query, you view it in a form or a report. If you had a form that shows the company details you could have one subform that shows the contacts and a second one that shows the contact history. The subforms would be linked to the main form via the parent/child links (company ID I presume). Result is a display of all the data for a company, with no duplicates.

 

[SRAB]

The results you are getting are expected, you are joining the company to the call table, so you get one record for each call. Distinct isn't going to help you eliminate the duplicate. What call records are supposed to be in the report?