Comparing two Tables in Access using VB

CrazyOrangeOne

ICT Teacher
Local time
Today, 09:18
Joined
Dec 9, 2007
Messages
1
I've got two queries in Access, one displays all students who got A's or B's for a subject (query A) the other displays students who didn't. (query B)

Now I'm stuck as to how to get a table which displays only the students who appear in query A but not in query B.

The problem is that the each student can have multiple records in each of the query results tables as they all have a record for every subject they take.

Can anyone give me any idea as to how to sort this out please? I'm guessing VB is the only way but its been ages since I programmed anything. Or is there away of doing the comparison using Queries.

Please help
Melissa
 
Now I'm stuck as to how to get a table which displays only the students who appear in query A but not in query B.

The problem is that the each student can have multiple records in each of the query results tables as they all have a record for every subject they take.
If there are multiple records, use can use a DISTINCT predicate along with a WHERE clause and the (NOT) EXISTS subquery to specify which records you want.

How about a make-table table query, with the record requirements you need from Query A??
 
Melissa,

Code:
Select Distinct(A.StudentID)
From   QueryA As A Left Join QueryB As B On
       A.StudentID = B.StudentID
Where  B.StudentID Is Null

Wayne
 

Users who are viewing this thread

Back
Top Bottom