Testing if a record exists in another table [Resolved]

[olamm2k]

Hi,

I have two tables:
- [Members] containing the fields MemID (a unique number), Surname and Forenames
- [RegMem] containing the fields SDate and MemID

The database holds information about my local hockey club. [RegMem] is a register of the members present on any date, so it contains a list of dates each linked to one MemID if the member was present on that date.
For example:
01/01/2005 - 007
01/01/2005 - 015
01/01/2005 - 126
01/01/2005 - 018
etc.

The program I am writing (in VB6, using ADO) has to output a list of the names of all the members and then a 'Y' or 'N' depending on whether they were present on that date.

At the moment, I am doing this by doing:
"SELECT Surname & Chr(44) & Chr(32) & Forenames AS FullName, MemID
FROM [Members];"
Then looping through each item in the created Recordset and doing (where dteSDate and intSDate are my passed arguments):
"SELECT MemID
FROM [RegMem]
WHERE SDate=#" & Format(dteSDate, "yyyy-mm-dd") & "# AND MemID=" & intMemID & ";"
Then testing if the RecordCount property of the Recordset is 0.

This is quite slow, as it involves creating one Recordset for the member list, then another one to test for each separate MemID. How could I improve this?

I thought of something along the lines of this:
"SELECT Surname & Chr(44) & Chr(32) & Forenames AS FullName
FROM Members
INNER JOIN RegMem
ON Members.MemID = RegMem.MemID
WHERE SDate = #" & Format(dteSDate, "yyyy-mm-dd") & "#;"
which returns the name of everyone present on a date, but I still need the full list of members.

Can this be done with something like RIGHT JOIN?