Any way to automate set intersection discovery?

[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!

 

[Michel Walsh]

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

 

[Dale Fye]

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.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use a UNION query to gather all the data into one query/table,
like this:

SELECT "table_A" As SourceTable, col_a, col_b
FROM tableA

UNION

SELECT "table_B" As SourceTable, col_a, col_b
FROM tableB

UNION

SELECT "table_C" As SourceTable, col_a, col_b
FROM tableC

.... etc. ...

All the columns in each table must have the same data types as the
corresponding columns in the other tables.

Then use the above query as the source query for a make-table query that
creates/loads the final table. The column "SourceTable" will indicate
which table the data came from.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZsxH4echKqOuFEgEQLF0ACg6ihw9BV/Iji8A5J7UCdTyxW+aB0An01j
fnwbJgerC8ddzxM6ekWFlGVO
=Do8s
-----END PGP SIGNATURE-----