Duplicate Lines Occurring Due to Relationship

[CharlesWhiteman]

In my Db i have TblDatabase (for company records) TblContacts (contats in the companies) & TblCalls (contact history)

When I run a company history report (based on a query with all the above tables in QBE) if the company has more than one contact the report will duplicate the call history by the number of contacts

Please help?

 

[georgedwilkinson]

I'd love to help so, what do you expect? It sounds like it's giving you exactly what you asked for so we need to know why that is wrong.

If you don't want a separate record for each contact, you need to remove the contact table from the join.

If you want a list of companies with their main contact, you have to define how you determine the main contact.

 

[chaimribs]

Duplicate results

I have a similar problem. I would like to sort a table through a relationship with another query but I dont want all the results of the query only the first time every record in the table shows up in the query.

 

[georgedwilkinson]

My question to both of you is:
If the data from the joined table is not meaningful enough to view it all, why do you bother joining to the second table?

You're making your life difficult for no reason or you have some reason for having at least one record...neither of you defined what that reason is. Remove the joined table and you'll get good data without any duplicates.

 

[CharlesWhiteman]

Thanks George but your answer was incorrect too since removing the relationship simply meant that all records for any company were listed all at once. The solution was to create a separate report and use the insert sub report function in the main report.

 

[georgedwilkinson]

I'm glad you got it fixed. My assumption was that the query was giving you a problem, not the report.

 

[chaimribs]

Reson for adding the second Table

All the data I need is in the first table but I need to sort it base in related records in the second table. The second table is a one to many therefore I get repeated results. Is there any other way to sort from a second table without including that in the query?

 

[jal]

All the data I need is in the first table but I need to sort it base in related records in the second table. The second table is a one to many therefore I get repeated results. Is there any other way to sort from a second table without including that in the query?

I should think so.

Do the JOIN and use an ORDER BY to order the joined records. (Store these results in a table called JoinedTable. To help guarantee preserving the order do this:

ALTER TABLE JoinedTAble ADD Column AutoNum COUNTER

Then select the desired values from JoinedTable
SELECT whatever FROM JoinedTAble
ORDER BY AtuoNum

 

[Pat Hartman]

It is not necessary to make a temp table to do this.

Create a totals query for table2 that groups on the joining field and the sorting field. Assuming that there will be only one sorting field value for the joining field, you will end up with a single row per joining field from the second table. Then join this query to the main query and your records will not be duplicated.

The structure of the tables may be incorrect if there truely is only one "sorting" value per each instance of the "joining" key. If there are multiple "sorting" values then your data will be duplicated and there isn't anything you can easily do about it.