Missing record combinations across many-many relationship?

[The Frog]

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

[Jason Lepack]

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" <[e-mail]:

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

[The Frog]

You are a LEGEND, thankyou for this. My SQL could use some more
practice I think. I am too used to doing things via recordsets and
VBA. Thankyou very much for this. When I read it it makes perfect
sense. Thankyou.

Cheers

The Frog