Query to compare subtables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ivan Debono - 28 Jun 2005 15:29 GMT

Hi all,

I have a master table and a details table.

Both tables have fields (id, name)

I need a query to list id & names from the master table where the details
records are equal. For example:

Master
id name
1 A
2 B
3 C

Details
id master_id name
1 1 A1
2 1 A2
3 2 B1
4 2 A2
5 2 A1
6 3 A1
7 3 A2

The query should return master records A & C (or at least just A or C)
because they have the same details.

Is it possible to achieve this?

Thanks,
Ivan

Reply to this Message

[MVP] S.Clark - 28 Jun 2005 23:21 GMT

You are trying to perform Set Theory functionality. You know, Venn Diagram
stuff.

i.e. Is Set A = Set B, etc.

So, you have to compare each Set against every other set to see if there is
a match. Unfortunately, Access is not very good at this. There may be some
code out there to help you, but I don' t know of any.

To perform this, you could query for everything in Set A. Then, through
process of elimination, you could determine which other sets do NOT match.
Eliminate any set that:
1. Does not contain the same COUNT of items. (In your example, B does not
match as it has 3 members, instead of 2.)
2. Does not contain each element of the compare set.
Suppose that another set had only members A1 and A3. It would not match Set
A as it does not contain A2. This would require stepping through each
member of Set A to determine which sets do not contain each of it's members.

HTH,

Signature

Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

> Hi all,
>
[quoted text clipped - 28 lines]
> Thanks,
> Ivan

Reply to this Message