Query results without duplicates

[Kayleigh]

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:

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.

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:

 

[jdraw]

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

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]

Will the unmatched query work with text values?

 

[isladogs]

Yes. Here's a simple example

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:

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]

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:

SELECT DISTINCTROW lkptblIForms.fldIFormID, lkptblIForms.fldICaptionName, qryIncidentStudentType.fldIncidentLogStudentID
FROM lkptblIForms LEFT JOIN qryIncidentStudentType ON lkptblIForms.fldIFormName = qryIncidentStudentType.Type;

And results are this:

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]

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

 

[Pat Hartman]

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.