Testing if a record exists in another table [Resolved]

[PilgrimPete]

Double up your double quotes (or use CHR$(34)).

'doubling up double quotes
strQuery = "SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
          "AS FullName, IIF(IsNull(RegMem.MemID), ""N"", ""Y"") " & _
          "AS Present From Members LEFT JOIN RegMem " & _
          "ON Members.MemID = RegMem.MemID WHERE SDate = #" & _
          Format(#7/4/2005#, "yyyy-mm-dd") & "#;"
                objRS.Open strQuery, objConn, adOpenStatic, adLockPessimistic

[olamm2k]

That works for displaying the character, but the query only returns the members who were present on that date, not those who were absent.

[PilgrimPete]

Oh, I get it. I wasn't paying attention!

You'll be needing a subquery then...

strQuery = "SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
          "AS FullName, IIF(IsNull(RegMem.MemID), ""N"", ""Y"") " & _
          "AS Present From Members LEFT JOIN (SELECT MemID FROM RegMem WHERE SDate = #" & Format(#7/4/2005#, "yyyy-mm-dd") & "#) RegMem " & _
          "ON Members.MemID = RegMem.MemID

I doubt if that is syntactically spot on, but you get the idea?

[olamm2k]

It works, but I'm not quite clear on the part between the 'LEFT JOIN' and 'ON' - the subquery followed by a table name. How does that work?

[PilgrimPete]

(SELECT MemID FROM RegMem WHERE SDate = #" & Format(#7/4/2005#, "yyyy-mm-dd") & "#) AS AnyNameYouLike "

The subquery is effectively like an inline query, I just happened to have aliased it to the same name as the table it came from in the previous post (and I was lazy and skipped the 'AS').
You would get the same effect if you created a saved query in access with that SQL, and JOINed on that instead. Does that make it any clearer?

[olamm2k]

The missing 'AS' did throw me.

I've now got it as:

strQuery = "SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
      "AS FullName, IIF(IsNull(QueryDateOnly.MemID), ""N"", ""Y"") " & _
      "AS Present FROM Members LEFT JOIN " & _
      "(SELECT MemID FROM RegMem WHERE SDate = #" & _
      Format("2005-04-07", "yyyy-mm-dd") & "#) AS QueryDateOnly " & _
      "ON Members.MemID = QueryDateOnly.MemID;"

But why is the subquery needed?

[PilgrimPete]

Because you don't want to limit the whole result set, just the subset of members present on that date.

The original query I posted had the following logic:
Return rows for all members, joining all records in RegMem, marking those not present with an 'N', BUT that have a row in RegMem with an SDate of xyz. This is contradictory rubbish.
The revised query has this logic:
Return rows for all members, joining ONLY those rows in RegMem with an SDate of xyz, marking those not present with an 'N'.

This is surprisingly hard to explain!
Hope I got close though

[olamm2k]

It's an odd concept to get your head around, but I think the first query would be the same as doing:

"SELECT Surname & Chr(44) & Chr(32) & Forenames " & _
  "AS FullName, IIF(IsNull(RegMem.MemID), ""N"", ""Y"") " & _
  "AS Present FROM Members, RegMem WHERE SDate = #" & _
  Format("2005-04-07", "yyyy-mm-dd") & "# AND " & _
  "Members.MemID = RegMem.MemID;"

Thanks for your help.