A way to find "no match" when two columns needed for linking?

[Laurel]

I have a table, tblScores, containing Student_id and score_date and another
table, tblAttendance, containing student_id and att_date. Is there a way I
can select all of the rows in tblScores which have no match in tblAttendance
on BOTH student_id and date? (score_date = att_date)?

 

[Steve]

Open to the database window and go to queries. Click New, select Find
Unmatched Query Wizard and follow the directions.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[louisjohnphillips]

Open to the database window and go to queries. Click New, select Find
Unmatched Query Wizard and follow the directions.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)






- Show quoted text -

Have you tried:

SELECT A.*
from tblScores as A
where not exists
( select 'true'
from tblAttendance
where student_id = a.student_id
and att_date = a.score_date )

?

The Unmatched Query Wizard generates a complicated outer join query.
This is much more to the point.

 

[John Spencer]

The problem is that the wizard only lets you pick one field. So do so
and then


You can use the wizard to build the query with one field and then after
the query is saved set up a second join line by dragging from the field
that is not joined in tblScores to the corresponding field in
tblAttendance. Then double click on the join line and select choice 2
and click OK. The two join lines should point in the same direction.

You should end up with a query that looks something like the SQL
statement below

SELECT tblScores.*
FROM tblScores LEFT JOIN tblAttendance
WHERE tblScores.StudentID = tblAttendance.Student_ID
AND tblScores.Score_date = tblAttendance.Att_Date
WHERE tblAttendance.StudentID is Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Laurel]

I did that, but it doesn't seem to handle two linking columns. And I
couldn't edit the result in SQL view in a way that was acceptable.

 

[Laurel]

THANKS!
I didn't know how to do a left join with two columns.

 

[Dale Fye]

Actually, I think John meant the following.

SELECT tblScores.*
FROM tblScores LEFT JOIN tblAttendance
ON tblScores.StudentID = tblAttendance.Student_ID
AND tblScores.Score_date = tblAttendance.Att_Date
WHERE tblAttendance.StudentID is Null

Dale