CODEWITHSUNDEEP
ms-accessms-access-2007
Argee
Argee

Reputation: 1224

Getting all NOT matching entries across a link table in ms-access

I have following relations in my database: relations

There are different trainings in a company and they are repeated like 2-3 times a year.

Now I want to get all the tbl_events entries which a tbl_employee entry has NO relation to and display them in a report.

So far, I've tried to make a query giving me all the employess with the ev_name they already visited.

qry_visited:

SELECT tbl_employee.em_number
    ,tbl_event.ID AS ev_visited_id
FROM (
    tbl_event INNER JOIN tbl_date ON tbl_event.[ID] = tbl_date.[da_f_event]
    )
INNER JOIN (
    tbl_employee INNER JOIN tbl_training ON tbl_employee.[ID] = tbl_training.[tr_f_employee]
    ) ON tbl_date.[ID] = tbl_training.[tr_f_date];

And then I wrote a second query: qry_unvisited

SELECT qry_visited.em_number
    ,tbl_event.ev_name
    ,qry_visited.ev_visited_id
FROM qry_visited
RIGHT JOIN tbl_event ON qry_visited.ev_visited_id = tbl_event.ID
WHERE (((qry_visited.ev_visited_id) IS NULL));

These queries only work together, if I limit the first one to only one employee. I would rather like to have one recordset with em_number and ev_name for all the employees.

I also researched for a couple of days now on working with (and working around) LEFT OUTER, RIGHT OUTER and FULL OUTER JOIN, but none did the trick.

Thanks for your help!

edit 1

Example: There are 3 employees (emp1, emp2, epm3) and 3 events (ev1, ev2, ev3)

Let's say

I want my result to look like this:

| employee | not visited events |
  emp1       ev3
  emp2       ev1
  emp3       ev1
  emp3       ev2
  emp3       ev3

Upvotes: 0

Views: 51

Answers (1)

Zev Spitz
Zev Spitz

Reputation: 15357

This should give you a list of events with no related employees.

SELECT DISTINCTROW tbl_event.*
FROM ((tbl_event
LEFT JOIN tbl_date ON tbl_event.ID = tbl_date.da_f_event)
LEFT JOIN tbl_training ON tbl_date.ID = tbl_training.tr_f_date)
LEFT JOIN tbl_employee ON tbl_training.tr_f_employee = tbl_employee.ID
WHERE tbl_employee.ID IS NULL

If I understand correctly, what you want is all possible employee-event combinations, excluding the ones which actually happened.

Something like this:

SELECT possibles.*
FROM (
    SELECT tbl_event.ID AS eventID, tbl_employee.ID AS empID
    FROM tbl_event, tbl_employee
) AS possibles
LEFT JOIN (
    SELECT tbl_date.da_f_event AS eventID, tbl_training.tr_f_employee AS empID
    FROM tbl_date
    INNER JOIN tbl_training ON tbl_date.ID = tbl_training.tr_f_date
) AS actuals ON possibles.eventID = actuals.eventID AND possibles.empID = actuals.empID
WHERE actuals.eventID IS NULL

You can add more fields from tbl_event and tbl_employee to the possibles subquery.

Following is an informal description of join types:

INNER JOIN

Includes only records which match on both sides of the join; duplicates the records for multiple matches

SELECT tbl_event.*, tbl_date.*
FROM tbl_event
INNER JOIN tbl_date ON tbl_event.ID = tbl_date.da_f_event

LEFT JOIN

Includes all records from the first table, and only records which match from the second table. Second-table columns for rows without a match in the second table will have a value of NULL. Duplicates the records for multiple matches.

SELECT tbl_event.*, tbl_date.*
FROM tbl_event
LEFT JOIN tbl_date ON tbl_event.ID = tbl_date.da_f_event

(There is also RIGHT JOIN, which does the same except it reverses the order of the tables. I haven't yet found any good reason to use RIGHT JOIN, so I suggest you avoid it.)

FULL OUTER JOIN (not supported by MS Access)

Includes all records from both tables even when there is no match for a given record on the other side of the join. Duplicates the records for multiple matches.

SELECT tbl_event.*, tbl_date.*
FROM tbl_event
FULL OUTER JOIN tbl_date ON tbl_event.ID = tbl_date.da_f_event

Cross join, AKA Cartesian product

Includes each record from the first table for each record in the second table.

SELECT tbl_event.*, tbl_date.*
FROM tbl_event, tbl_date

Upvotes: 1

Related Questions