CODEWITHSUNDEEP
sqlms-access
user3471881
user3471881

Reputation: 2724

Get specific non-matches from joined tables with SQL/ACCESS

I have two tables

The first one is called Event

date                                 id
2010-04-20                           97019
2010-04-20                           28617
2010-04-20                           96698
2010-04-20                           38601
2010-05-20                           96835
2010-05-21                           28221
2010-05-21                           4432
2010-05-21                           96898
2010-05-21                           4521
...                                  ...

And the second one is a Register

company                              id2
abcd                                 4521
abcde                                96898
abcdef                               3452
abcdefg                              3489
abcdefgh                             66855

What I want to do is check which dates the companies in my Register were not attending the Event. So I want my result for theese examples to look something like this:

Result

company         ids                        date, didn't attend
abcd            4521                       2010-04-20
abcd            4521                       2010-05-20
abcdefgh        66855                      2010-04-20
abcdefgh        66855                      2010-05-20
abcdefgh        66855                      2010-05-21
...            ...                        ...

I'm using SQL/Access to try and accomplish this. 

SELECT * FROM Register
 WHERE Register.id2
 NOT IN
  (SELECT Event.id FROM Event);

Gives me all the Event attendees that aren't companies in my Register.

SELECT * FROM Register r
 LEFT JOIN Event e 
 ON e.id=r.id2
 WHERE r.id2 IS NULL;

Gives me nothing...

Any ideas on what I'm doing wrong?

Upvotes: 1

Views: 64

Answers (1)

ron tornambe
ron tornambe

Reputation: 10780

I believe this is what you are looking for. I needed to use VBA to work out a solution to this interesting query. I think it can be changed into some queries and sub-queries, but I will leave that as an exercise for you. You will need to copy this code into a Module and use the Immediate Window to test, ex: ?RegistrarAttended. Please let me know how it goes.

Public Function RegistrarAttended()

Dim db As DAO.database
Set db = CurrentDb
Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef("")
qd.sql = "SELECT edate from Event GROUP BY edate" ' gather unique dates
Dim rsDistinctDates As DAO.recordSet
Set rsDistinctDates = qd.OpenRecordset()
Dim rsReg As DAO.recordSet
Set rsReg = db.OpenRecordset("SELECT * FROM Register") 'get contents of Register table

Dim rsCP As DAO.recordSet
Dim qdCP As New DAO.QueryDef
Set qdCP = db.CreateQueryDef("")
' create a cartesian product of Register and Event table that requires 2 parameters: event-date and id
qdCP.sql = "SELECT DISTINCT Register.id2, Register.company, Event.id, Event.edate" & _
    " FROM Register, Event" & _
    " WHERE (((Event.id)=[inpid]) AND ((Event.edate)=[inpdate]));"


Debug.Print "Company", "id", "Event Date"
Do Until rsDistinctDates.EOF

    Do Until rsReg.EOF
       qdCP![inpdate] = rsDistinctDates("edate")
       qdCP![inpid] = rsReg![id2]
       Set rsCP = qdCP.OpenRecordset
       If rsCP.RecordCount = 0 Then
           Debug.Print rsReg![company], rsReg![id2], rsDistinctDates![edate]
       End If
       rsReg.MoveNext
    Loop
    rsReg.MoveFirst
    rsDistinctDates.MoveNext
Loop

End Function

Upvotes: 1

Related Questions