Duplicate query not showing all duplicates 2

[VicM]

Hi,

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,
tblMembers.[HideRec]
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?

Thanks,
Vic

 

[lameid]

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.

 

[combo]

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 [tt]Is Null[/tt] you will find missing data in joined table.

combo

 

[Andrzejek]

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.

 

[VicM]

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!!

Vic

 

[Andrzejek]

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:[tt]
tblStatus
fldStatusID
1
2
3
4[/tt]

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.

 

[VicM]

Andy,

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.

Vic

 

[Andrzejek]

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....

Have fun.

---- Andy

There is a great need for a sarcasm font.