Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Duplicate query not showing all duplicates

Duplicate query not showing all duplicates

Duplicate query not showing all duplicates


I used the query wizard to create a duplicates query on one table. The query seems to work as expected.

But much of the data in the main table is mostly normalized, i.e., many fields are filled with numbers relating to other tables where the texts reside.

Because I wanted to get this info to the person responsible for making the corrections, I wanted to link the 'text' tables to the main table and show the text associated with a given field instead of a number.

When I did this and ran the query, I got fewer records than with the original query. Also, several of the records in the modified query showed only one record with the field that was supposed to contain the duplicated records.

I'm a bit confused as to why this should happen since I selected only one field that might contain duplicated data.

Here are the SQL codes for each query. The first is based solely on the main table (Members). The second links some of the numbered fields with their associated 'text' tables in order to show text instead of numbers.

This query returns 147 records; most Card Numbers have 2 records duplicated; one has 3 dups and one has 4 dups.


SELECT tblMembers.MemCardNo, tblMembers.MemEmpID, tblMembers.MemLName, tblMembers.MemFName, tblMembers.MemAddress1,
tblMembers.MemAddress2, tblMembers.MemCity, tblMembers.MemST, tblMembers.MemZipcode, tblMembers.MemSSNo, tblMembers.MemSex,
tblMembers.MemDOB, tblMembers.MemMemberTypeID, tblMembers.MemJoinedUnion, tblMembers.MemJoinedCompany, tblMembers.MemEffective,
tblMembers.MemStatusID, tblMembers.MemJobID, tblMembers.MemSeniority, tblMembers.MemClassID, tblMembers.MemLocation,
tblMembers.MemDivision, tblMembers.Mem_UnitNo, tblMembers.MemEmp, tblMembers.MemCounty, tblMembers.HideRec
FROM tblMembers
WHERE (((tblMembers.MemCardNo) In (SELECT [MemCardNo] FROM [tblMembers] As Tmp GROUP BY [MemCardNo] HAVING Count(*)>1 )))
ORDER BY tblMembers.MemCardNo; 
Here's the design mode for the above query:

This query returns 139 records. Now 8 records don't show their respective duplicates.


SELECT tblMembers.[MemCardNo], tblMembers.[MemEmpID], tblMembers.[MemLName], tblMembers.[MemFName], tblMembers.[MemAddress1],
tblMembers.[MemAddress2], tblMembers.[MemCity], tblMembers.[MemST], tblMembers.[MemZipcode], tblMembers.[MemSSNo], tblMembers.[MemSex],
tblMembers.[MemDOB], tblMembers.[MemMemberTypeID], tblMembers.[MemJoinedUnion], tblMembers.[MemJoinedCompany],
tblMembers.[MemEffective], tblStatus.fldStatus, tblJobs.fldTitle, tblMembers.[MemSeniority], tblEmployeeClass.fldClass,
tblMembers.[MemLocation], tblMembers.[MemDivision], tblMembers.[Mem_UnitNo], tblEmployers.emp_code, tblMembers.MemCounty,
FROM tblJobs INNER JOIN (tblEmployeeClass INNER JOIN (tblStatus INNER JOIN (tblEmployers INNER JOIN tblMembers ON 
tblEmployers.comp_ID = tblMembers.MemEmp) ON tblStatus.fldStatusID = tblMembers.MemStatusID) ON 
tblEmployeeClass.fldClassID = tblMembers.MemClassID) ON tblJobs.fldJobID = tblMembers.MemJobID
WHERE (((tblMembers.[MemCardNo]) In (SELECT [MemCardNo] FROM [tblMembers] As Tmp GROUP BY [MemCardNo] HAVING Count(*)>1 )))
ORDER BY tblMembers.[MemCardNo]; 

And here's the design mode for the other query:

I don't understand why there is this discrepancy. I lost 8 records in the second query.

Any suggestions?


RE: Duplicate query not showing all duplicates

Inner joins limit the records to to those that are in both tables. Try outer joins (left or right joins instead). In the qbe double click the join line and select option 2 or 3 as appropriate instead of 1.

RE: Duplicate query not showing all duplicates

If you outer join tblMembers to any single table from the left on your last image (in new query), add any of its fields with the condition Is Null you will find missing data in joined table.


RE: Duplicate query not showing all duplicates

Your picture also tells me that your connections to the 'text' tables are not based on Primary Key - Foreign Key relations.
I would add one 'text' table at the time and check the number of records before I add another 'text' table.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicate query not showing all duplicates

lameid & combo

Thanks for reminding me that the join properties play an important part in the results. Changing all the joins to RIGHT JOINs did the trick. (I gotta be getting too old for this. The little grey cells are dying off! LOL)
Stars for you both.

Andrzejek, I don't understand your comment. All of the 'text' tables are joined from their Primary key into a column of the Members table containing only the numbers of the 'text' tables' Primary key. Is that not the definition of a Primay key-Foreign key relationship?

But thank you all for your diligence in giving me advice.

Great forum!!


RE: Duplicate query not showing all duplicates

Well, your statement is (kind of) correct, but...
In your tblMembers.MemStatusID you have numbers that correspond to numbers in tblStatus.fldStatusID and everything seams to be OK.
But let's say you have this:

Will your data base allow you to have a value of 9 in tblMembers.MemStatusID ?

If you have a relations established, it is usually represent by a small yellow key symbol on one side and the infinity symbol on the other side of the line between tables. Your picture does not have those symbols, so I assume your data base does not know about the relation between the 2 tables. You create this relation when you build your query.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicate query not showing all duplicates


I think I know what you're saying.

Yes, my relationships are defined in the building of the query. I don't have any overt relationships defined in the DB.

The numbers that get stored in those 'foreign' fields can come from two sources. One is when a spreadsheet is sent to my client containing membership info. For those fields the spreadsheet can contain either a letter, a value or text which my code converts to a number with lookups before entering into the Members table. Another way is when my client needs to change something, the form they use has dropdowns in those fields whose recordsource is based on the 'text' tables. So basically only the Primary key in those tables can be stored in the 'foreign' fields.

Thanks for your advice.


RE: Duplicate query not showing all duplicates

I understand, but I would strongly advise to set up the Foreign Key / Primary Key relation in the data base. I know your code enforces this role, but setting the data base right is a lot better way (on top of your code).

it is like setting the unique key in the code vs. Auto Number done by Access. Your code will do what you want, but data base will prevent and not allow any ooopses from code or done by hand.

Just a suggestion.... smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close