Based on the structure in your post, here is the query, paste it in
SQL View
SELECT A.Field1, A.Field2, A.Field3
FROM Table2 AS B
RIGHT JOIN
(SELECT Table1.Field1, Table1.Field2, Table3.Field3
FROM Table1, Table3) AS A
ON
B.Field3 = A.Field3
AND B.Field2 = A.Field2
AND B.Field1 = A.Field1
WHERE
B.Field1 Is Null
AND B.Field2 Is Null
AND B.Field3 Is Null;
Cheers,
Jason Lepack
On Feb 14, 10:31 am, "The Frog" <andrew.hogend...@eu.effem.comwrote:
Hello everyone,
I am trying to find way of writing an SQL query that can produce
missing record combinations across a many to many type setup in
Access.
The three tables used are as follows:
Table1:
Field 1
Field 2 (PK is Field1 and Field2)
Table 2:
Field1
Field2
Field3 (PK is Field1, Field2, Field3)
Table 3:
Field3 (PK is Field3)
...
The table that " handles"the many to many is Table 2. What I am
looking for is to locate the missing possible combinations in Table 2
that would be possible in the many to many sense.
For example:
For each given Field1 / Field2 (Table1) combination that exists there
"should" be a matching record for each Field3 (Table3) in Table2. I
need to find the combinations that dont exist.....
I have tried a few different things but I am just going in circles
here and either get errors or just produce a list of what is in
table2. My logic must be flawed on this one. Any help with this would
be greatly appreciated.
Thanks in advance
The Frog
