I have three tables (Main, Item, Suspect) that have the same key in each table, called DrNumber. I created a select query with an inner joins. As follows:
SELECT DISTINCT MainTable.DrNumber, MainTable.OfficerLast, ItemTable.Item, ItemTable.LabAnalysis, SuspectTable.SuspectLast, SuspectTable.SuspectFirst
FROM (MainTable INNER JOIN SuspectTable ON MainTable.DrNumber = SuspectTable.DrNumber) INNER JOIN ItemTable ON MainTable.DrNumber = ItemTable.DrNumber;
When I run this query, I get duplicate rows. I know it's because I am looking for a match on both tables. Is there a way I can structure this to avoid duplicates. I am using this query in a report. I have played around with the subreports to avoid this duplication, but I was unsuccessful.
Any ideas?
SELECT DISTINCT MainTable.DrNumber, MainTable.OfficerLast, ItemTable.Item, ItemTable.LabAnalysis, SuspectTable.SuspectLast, SuspectTable.SuspectFirst
FROM (MainTable INNER JOIN SuspectTable ON MainTable.DrNumber = SuspectTable.DrNumber) INNER JOIN ItemTable ON MainTable.DrNumber = ItemTable.DrNumber;
When I run this query, I get duplicate rows. I know it's because I am looking for a match on both tables. Is there a way I can structure this to avoid duplicates. I am using this query in a report. I have played around with the subreports to avoid this duplication, but I was unsuccessful.
Any ideas?
