Partial Intersection on Multiple Query Results

[Guest]

I have 8 large, complex queries:

q1, q2, q3, q4, q5, q6, q7, q8

Each one queries its own respective table:

t1, t2, t3, t4, t5, t6, t7, t8

Suppose there are names as part of each table, composed of three fields ---
first_name, middle_name, last_name.

I want to know the name set(s) common to all the 8 query results.

I will alter the queries, incrementally (all 8 querries), until I get a full
name set collection totalling around 100 names.

In other words, what will emerge are 100 names common to all the query
results.

Any thoughts on how best to do this?

One approach would be to combine all the 8 queries into one single
super-large query. Yikes.

Thanks,
Nicholas

 

[Jeff Boyce]

Have you looked into creating a UNION query ... something like:

SELECT whatever FROM t1
UNION
SELECT whatever FROM t2
UNION
...

I believe there's a way you can get only unique values across the UNIONs
(check in Access HELP under UNION query).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Jeff Boyce]

Another thought...

It is an "unusual" design to have multiple tables with the same fields. You
may have a spreadsheet, not an Access database!

If you post more information about what kinds of data you are storing in 8
identical but separate tables, the 'group may be able to offer a design that
saves you all the work of UNION queries.

Regards

Jeff Boyce
<Office/Access MVP>