By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 814 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Missing record combinations across many-many relationship?

P: n/a
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

Feb 14 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Feb 14 '07 #2

P: n/a
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

Feb 15 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.