C
ClaimsDatabase
How do I get rid of duplicate entries in a joined query where one entry has more than one related entry? My report total is overstate due to these automatic duplicate entries.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
L
Larry Linson
How do I get rid of duplicate entries in
a joined query where one entry has more
than one related entry? My report total is
overstate due to these automatic duplicate
entries.
Right click in the upper part of the Query Builder, select Properties, and
investigate Unique Rows and Unique Values. That may or may not do what you
need.
But, with just a little clarification and detail, someone might be able to
give you more than a generic answer.
Larry Linson
Microsoft Access MVP
C
ClaimsDatabase
Thank you for your response. I had previously tried to set the Unique Rows / Unique Values to "yes" but it did not work? Here is my attempt to explain in more detail:
I created two queries that I joined into one query linked by an ID number. When I joined the two queries I selected "Include ALL records from "the main query" and only those records from "the sub query" where the joined fields are equal." The problem is that the "main query sometimes has more than one entry with the same ID number so the related "sub query" value is listed more than once to fill the row. It also applies the other way when the "sub query" has more than one ID number that matches one ID from the "main query" the main query duplicates that one entry to fill the row for the related "sub query" entries? Any help would be very much appreciated.
Thanks!
I created two queries that I joined into one query linked by an ID number. When I joined the two queries I selected "Include ALL records from "the main query" and only those records from "the sub query" where the joined fields are equal." The problem is that the "main query sometimes has more than one entry with the same ID number so the related "sub query" value is listed more than once to fill the row. It also applies the other way when the "sub query" has more than one ID number that matches one ID from the "main query" the main query duplicates that one entry to fill the row for the related "sub query" entries? Any help would be very much appreciated.
Thanks!
C
ClaimsDatabase
Thank you for your response. I had previously tried to set the "Unique Rows" / "Unique Values" to yes but it didn't work? Here is my attempt to explain the problem in more detail:
I created two queries that I joined on an assiged ID number for related entries. For example in the table "Joe Smith" may have four different entries so all four entries received the same ID #. When I joined the two queries I selected "Include ALL records from "Main Query" and only those records from "sub query" where the joined fields are equal. The problem is that the Main query may have two entries for "Joe Smith" and the Sub Query may only have one entry for Joe Smith so the query duplicates the one Sub Query entry for Joe Smith to fill the rows for the related Main Query entries. This also applies the other way when the Sub Query has more than one entry and the Main query has only one. This automatic fill of duplicate entries is overstating my report totals. Any help would be very much appreciated.
Thank you!
I created two queries that I joined on an assiged ID number for related entries. For example in the table "Joe Smith" may have four different entries so all four entries received the same ID #. When I joined the two queries I selected "Include ALL records from "Main Query" and only those records from "sub query" where the joined fields are equal. The problem is that the Main query may have two entries for "Joe Smith" and the Sub Query may only have one entry for Joe Smith so the query duplicates the one Sub Query entry for Joe Smith to fill the rows for the related Main Query entries. This also applies the other way when the Sub Query has more than one entry and the Main query has only one. This automatic fill of duplicate entries is overstating my report totals. Any help would be very much appreciated.
Thank you!
J
John Vinson
How do I get rid of duplicate entries in a joined query where one entry has more than one related entry? My report total is overstate due to these automatic duplicate entries.
On a Report, one way would be to use the Report's Sorting and Grouping
dialog to put the "one" side table fields in a group header, and sum
the value in the group footer. The "many" side table values could be
in the detail section of the Report.