Getting duplicates entries in query

[AccessThomas]

Using Access 2013.



In the query for B Church I should be receiving 6 records, but it is bringing back 12 records. I think if the qty is more than one or more than one type, it is causing a problem. How would I go about fixing this? Thanks for any help.

Table 1
Name Type Qty Connection
J Smith 170 1 2
S Jones 171 1 3
B Church 170 2 2
B Church 171 4 3

Table 2
Name Type serial
J Smith 170 55
S Jones 171 56
B Church 170 57
B Church 170 58
B Church 171 59
B Church 171 60
B Church 171 61
B Church 171 62


Query
Name Type serial Connection
J Smith 170 55 2
S Jones 171 56 3
B Church 170 57 2
B Church 170 57 3
B Church 170 58 2
B Church 170 58 3
B Church 171 59 2
B Church 171 59 3
etc, etc

[ranman256]

if you don't need the serial# remove it , pull uniques.
otherwise the Tbl1 2 recs X tbl2 6 recs = 12 recs.

join the namne fields and the type fields. not sure what you want as a result.

[John_G]

Your query is doing exactly what it should do. I assume you are joining the tables on the field "name" (only). There two records for "Church" in table 1, and for each of them there are 6 matches in table 2. That gives you 2 x 6 = 12 possible matches, and it is what you should expect. It has nothing to do with whether "quantity" is > 1 or not.

How you fix it depends entirely on what you want your query to do, and you haven't stated that.

[AccessThomas]

I want the query to have these results for B Church. Shouldn't be any duplicate serial numbers.

NAME TYPE SERIAL CONNECTION
B Church 170 57 2
B Church 170 58 3
B Church 171 59 2
B Church 171 60 3
B Church 171 61 2
B Church 171 62 3

There should be 6 records. Two for type 170 and four for type 171.

[John_G]

Try linking the tables on both Name and Type. That will uniquely define the Connection for each Name - Type combination.