counting matches between multiple tables

[Guest]

Hi

I need to create a query which counts the number of matches between data in
a certain field within a 'master table' and corresponding fields in several
other tables, and display the results in a summary table. I've tried using a
union query to combine multiple select joins but I can't figure out how to
retrieve and display the different table names alongside the count for each
table.

I need to end up with something like this:

Tablename rowsmatching
table1 100
table2 250

Any ideas?
Thanks
Stefan

 

[MGFoster]

Hi

I need to create a query which counts the number of matches between data in
a certain field within a 'master table' and corresponding fields in several
other tables, and display the results in a summary table. I've tried using a
union query to combine multiple select joins but I can't figure out how to
retrieve and display the different table names alongside the count for each
table.

I need to end up with something like this:

Tablename rowsmatching
table1 100
table2 250

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

SELECT 'table1' As TableName, Count(*) As RowsMatching
FROM table1
WHERE colA IN (SELECT ColA FROM master_table WHERE <criteria>)

UNION

SELECT 'table2', Count(*)
FROM table2
WHERE colA IN (SELECT ColA FROM master_table WHERE <criteria>)

UNION ... etc. ...
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQmf8sIechKqOuFEgEQLrGwCfUsNYfiFu1bIKCngEiSb5gGsQxHoAoNHJ
OUfPI9vvPtysexjjuGHd0lBQ
=dV+O
-----END PGP SIGNATURE-----

 

[Guest]

thanks very much!

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

SELECT 'table1' As TableName, Count(*) As RowsMatching
FROM table1
WHERE colA IN (SELECT ColA FROM master_table WHERE <criteria>)

UNION

SELECT 'table2', Count(*)
FROM table2
WHERE colA IN (SELECT ColA FROM master_table WHERE <criteria>)

UNION ... etc. ...
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQmf8sIechKqOuFEgEQLrGwCfUsNYfiFu1bIKCngEiSb5gGsQxHoAoNHJ
OUfPI9vvPtysexjjuGHd0lBQ
=dV+O
-----END PGP SIGNATURE-----