Report duplicating data

[tarhim47]

Hi,

I have a report which brings in data from two different tables. The report is showing data from the two tables but is duplicating data from one of the tables.

How can I fix this?

My dbase is attached. Please see "Lead Activity Report1" for reference.



MANY THANKS!

[kennejd]

Use a union query to eliminate your duplicates. You're trying to join 2 many to many tables based on COI...and that's not going to give you unique records. It looks like you're trying to report based on COI...a union query will do what you want.

[tarhim47]

See below.

[tarhim47]

Hey Ken,

Thanks for your help!

My access knowledge is limited but I figured out the union query you were talking about. I made one using the help notes in Access and did exactly what they said but the union query doesnt combine all the result - just shows the results for the lead discussions with COIs.

Here's what I did:

I made a query for lead discussions with COIs
- form: Lead Activity Report
- fields: date of last contact, COI and comments - COI

I then made a qury for general discussions with COIs
- form: COI Discussion Form
- fields: date, COI and general comments

Fianlly, I created the union query in SQL view. See below.

SELECT [Lead Activity Report].[Date of last contact], [Lead Activity Report].COI, [Lead Activity Report].[Comments - COI]
FROM [Lead Activity Report]
UNION ALL SELECT [COI Discussion Form].Date, [COI Discussion Form].COI, [COI Discussion Form].[Discussion points]
FROM [COI Discussion Form];

Any ideas?

Thanks again for your help!