Query results without duplicates (1 Viewer)

Kayleigh

Member
Local time
Today, 06:33
Joined
Sep 24, 2020
Messages
706
I'm having trouble writing a query to display all items which are NOT existing in related table. But when there are multiple items related to key, it shows each item multiple times in query. I know this is probably to do with the join but since can't find any way to join the lookup table to main table in query. Perhaps someone can help.

The first query finds items related to key field and displays appropriate text in Type field:
SQL:
SELECT tblIncidentLogDetails.fldIncidentLogDetailsID, jtblIncidentLogStudent.fldIncidentLogStudentID, jtblIncidentLogStudent.fldStudentID, tblIncidentLogDetails.fldTypeMed, tblIncidentLogDetails.fldTypeSG, tblIncidentLogDetails.fldTypeBe, tblIncidentLogDetails.fldTypeGen, IIf([tblIncidentLogDetails]![fldTypeMed]=True,"Medical",(IIf([tblIncidentLogDetails]![fldTypeSG]=True,"Safeguarding",(IIf([tblIncidentLogDetails]![fldTypeBe]=True,"Behaviour",(IIf([tblIncidentLogDetails]![fldTypeGen]=True,"General",Null))))))) AS Type
FROM jtblIncidentLogStudent INNER JOIN tblIncidentLogDetails ON jtblIncidentLogStudent.fldIncidentLogStudentID = tblIncidentLogDetails.fldIncidentLogStudentID;

Next query, finds all items not matched to the results of above query. Here I am finding duplicated results where the key field has more than one entry.
SQL:
SELECT DISTINCT lkptblIForms.fldICaptionName, qryIncidentStudentType.fldIncidentLogStudentID, qryIncidentStudentType.Type
FROM qryIncidentStudentType, lkptblIForms
WHERE (((lkptblIForms.fldICaptionName)<>[qryIncidentStudentType]![Type]) AND ((qryIncidentStudentType.Type) Is Not Null))
ORDER BY qryIncidentStudentType.fldIncidentLogStudentID;

Here are the results of the first and second queries:
qry1 result.png
qry 2 results.png
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:33
Joined
Jan 23, 2006
Messages
15,364
You're showing table and values - can you step back and, in plain , simple English tell us about your application and what exactly this proposed table(s) is intended to support.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:33
Joined
Oct 29, 2018
Messages
21,358
I don't see any duplicates in your second query result. Can you post a sample result of what you expect to see where the duplicates are taken out?
 

isladogs

MVP / VIP
Local time
Today, 06:33
Joined
Jan 14, 2017
Messages
18,186
Your second query is what is sometimes called a cartesian join but in reality that means there is no join between the table and your first query.
As a result you get all combinations of each record from both of those. So if the First query has 10 records and the table has 200, the second query will have 10*200=2000 records.

For an unmatched query you need a LEFT JOIN. Suggest you use the unmatched query wizard to make your life simple
 

Kayleigh

Member
Local time
Today, 06:33
Joined
Sep 24, 2020
Messages
706
Will the unmatched query work with text values?
 

isladogs

MVP / VIP
Local time
Today, 06:33
Joined
Jan 14, 2017
Messages
18,186
Yes. Here's a simple example

Code:
SELECT DISTINCTROW Courses.Year, Courses.CourseID, Courses.SubjectID, Courses.Active, Subjects.SubjectID
FROM Courses LEFT JOIN Subjects ON Courses.SubjectID = Subjects.SubjectID
WHERE (((Subjects.SubjectID) Is Null));

The joined field is text. Note the left join and that the WHERE condition is for that field to be null in the right table.

And here's a very complex example involving 4 tables one of which is used twice:
Code:
SELECT DISTINCT LeaversGCSEResults.ResultID, LeaversGCSEResults.PupilID, LeaversGCSEResults.Surname, LeaversGCSEResults.Forename, ArchivedClasses.ClassID, LeaversGCSEResults.SubjectID, Subjects.FacultyID, LeaversArchivedClassRecords_1.TeacherID, LeaversArchivedClassRecords_1.CRGrade, LeaversArchivedClassRecords_1.TgtPoints, GetExamYear()-1 AS [Year]
FROM (Subjects INNER JOIN (ArchivedClasses INNER JOIN (LeaversGCSEResults LEFT JOIN LeaversArchivedClassRecords ON (LeaversGCSEResults.PupilID = LeaversArchivedClassRecords.PupilID) AND (LeaversGCSEResults.SubjectID = LeaversArchivedClassRecords.SubjectID)) ON ArchivedClasses.SubjectID = LeaversGCSEResults.SubjectID) ON Subjects.SubjectID = ArchivedClasses.SubjectID) INNER JOIN LeaversArchivedClassRecords AS LeaversArchivedClassRecords_1 ON (LeaversGCSEResults.PupilID = LeaversArchivedClassRecords_1.PupilID) AND (Subjects.FacultyID = LeaversArchivedClassRecords_1.FacultyID) AND (ArchivedClasses.ClassID = LeaversArchivedClassRecords_1.ClassID)
WHERE (((ArchivedClasses.ShowOnReport)=True) AND ((LeaversGCSEResults.Year)=GetExamYear()) AND ((LeaversArchivedClassRecords.PupilID) Is Null) AND ((LeaversArchivedClassRecords.SubjectID) Is Null))
ORDER BY LeaversGCSEResults.Surname, LeaversGCSEResults.Forename, LeaversGCSEResults.SubjectID;

Notice also the use of DISTINCT & DISTINCTROW
 

Kayleigh

Member
Local time
Today, 06:33
Joined
Sep 24, 2020
Messages
706
Thanks but I'm still having trouble here.
I used unmatched query with LEFT JOIN on text field but it displays all options in the lookup table with no rows for each incidentStudent.
This is the SQL:
SQL:
SELECT DISTINCTROW lkptblIForms.fldIFormID, lkptblIForms.fldICaptionName, qryIncidentStudentType.fldIncidentLogStudentID
FROM lkptblIForms LEFT JOIN qryIncidentStudentType ON lkptblIForms.fldIFormName = qryIncidentStudentType.Type;
And results are this:
1615736283998.png


The results I would like is, for each IncidentLogStudentID entry, display the unmatched captionName (<> type) fields but where there are multiple of same incidentLogStudentID, only show those unmatched to all.
This may need combination of a few queries...
 

isladogs

MVP / VIP
Local time
Today, 06:33
Joined
Jan 14, 2017
Messages
18,186
The query SQL shown is a left join but not an unmatched query. Compare with those I showed in post #6.
If you used the wizard, you then altered it removing the fields specified as not null.
Try using the wizard again to create an unmatched query based on the criteria you want. Unfortunately its difficult to give you a definitive answer based on the limited info provided
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:33
Joined
Feb 19, 2002
Messages
42,981
We're just guessing. Can you please post a picture of the relationship diagram and make sure we can see all the PK and FK relationships.
 

Users who are viewing this thread

Top Bottom