Union Query to list duplicates

  • Thread starter Thread starter MissBanjo
  • Start date Start date
M

MissBanjo

I've created a union query in multiple tables (73 to be exact) to create a
list of duplicates of a certain field. I had to create 2 union queries then
union them together because I couldn't go over a certain number of tables, no
big deal. My problem is the query doesn't show the tables anymore. Didn't
it used to have a + to the left of the rows that you could click on to see
what tables were involved with that value? Is there a way to show this in a
union query?
 
the query doesn't show the tables anymore.
Explain a little more. What view are you not seeing the tables that you
think you should?
 
I've created a union query in multiple tables (73 to be exact) to create a
list of duplicates of a certain field. I had to create 2 union queries then
union them together because I couldn't go over a certain number of tables, no
big deal. My problem is the query doesn't show the tables anymore. Didn't
it used to have a + to the left of the rows that you could click on to see
what tables were involved with that value? Is there a way to show this in a
union query?
Click to expand...

The + had to do with subdatasheets, and never to my knowledge worked as you
describe. What I'll do in a massive UNION query such as you describe is
explicitly include the tablename in the UNION query:

SELECT Table1.Thisfield, Table1.Thatfield, "Table1" AS Tablename
FROM Table1
WHERE Thisfield IS NOT NULL
UNION
SELECT Table2.Thisfield, Table2.Thatfield, "Table2"
FROM Table2
WHERE thisfield IS NOT NULL

....

This gives you a new field Tablename in the UNION.
 
Back
Top