471,320 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,320 software developers and data experts.

Missing record combinations across many-many relationship?

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
2 2089
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
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.

Similar topics

5 posts views Thread by Andrew | last post: by
36 posts views Thread by rbt | last post: by
1 post views Thread by Jordan Marton | last post: by
17 posts views Thread by Justin Emlay | last post: by
5 posts views Thread by le0 | last post: by
3 posts views Thread by Fred Chateau | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.