Any way to automate set intersection discovery?

  • Thread starter Thread starter T Magritte
  • Start date Start date
T

T Magritte

Hi,

I have several tables containing records, some of which are unique,
and some of which are duplicated in one or more of the other tables.

To illustrate check out the following venn diagram:
http://farm4.static.flickr.com/3417/3288467460_573af568c0.jpg

In this example, the circles l, m, n, and o represent 4 different
tables with partially overlapping records.
h, i, j, and k contain records that are unique to one of the 4
tables.
Finally, a - g represent records that present in 2 or more of the
tables.

I've been able to make tables for each of the sections above by
manually settings up a series of selection queries with reciprocal
joins or mismatch queries.

However, I was wondering if there's a better way to do this? Is there
a single command or query that would give me tables for each section,
or maybe a single table containing all of the records with 1 or more
extra columns that indicate which of the 4 original tables that record
can be found in?

Thanks!
 
You can merge the four tables into one with a UNION ALL query, and while it
will make your life easier to write the SQL statement, it will probably be
much slower of execution.


SELECT f1, f2, f3, "Table"1" AS source FROM table1
UNION ALL
SELECT g1, g2, g3, "Table2" FROM table2
UNION ALL
SELECT h1, h2, h3, "Table3" FROM table3



allows you to work with the fields (the names are those of the first SELECT)
and to use the field source (you can change the name if you want) to know
which table has supplied the data.



Vanderghast, Access MVP
 
T.

The question is, what data are you using to determine the overlap? Is it a
ClientID, PatientID, ???

And whay do you want to do with this information once you have it?

I think I would create a table (tbl_Venn) with fields (ID, VennCode), and I
would then generate a series of queries that append data to that table,
something like the following. This obviously does not contain the ( ) that
Access wraps around the individual joins (I always get these wrong), but
should give you an idea of where to start:

INSERT INTO tbl_Venn (ID, VennCode)
SELECT ID, "I"
FROM tbl_L
LEFT JOIN tbl_m on tbl_L.ID = tbl_m.ID
LEFT JOIN tbl_n on tbl_L.ID = tbl_n.ID
LEFT JOIN tbl_o on tbl_L.ID = tbl_o.ID
WHERE tbl_m.ID IS NULL
AND tbl_n.ID IS NULL
AND tbl_o.ID IS NULL

Then, all you would have to do is modify the criteria (change IS NULL to IS
NOT NULL) to identify all the records From tbl_L which fall in the sections
(a, e, d, g, and I).

Then change the query to use tbl_m as the base, and change the joins to get
the records that should fall in segments (f, b, j). Then change base table
to tbl_n to get the records that fall in segments (c, k). Lastly, use tbl_o
as the base to find the records that fall in h.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Back
Top