Listing all records for same field from multiple tables

[Guest]

Hi,

I want to list all the records of a particular field from all tables in my
database. The field has the same name in all the tables. Ideally I would like
one long list of the records. How can I do this?

Thanks,

Richard

 

[Brendan Reynolds]

SELECT YourFieldName FROM YourFirstTableName
UNION ALL SELECT YourFieldName FROM YourSecondTableName
UNION ALL SELECT YourFieldName FROM YourThirdTableName

Etc. for each table.

 

[Guest]

Thanks, that's great.

Now I have a list, how can I extract a list of duplicate values?

Richard

 

[Brendan Reynolds]

I'll have to use an unrelated table for this example, as I don't have a
suitable union query available, but the idea is that you would use this
against the union query from my previous post ...

SELECT Count(Table2.Name) AS CountOfName, Table2.Name
FROM Table2
GROUP BY Table2.Name
HAVING (((Count(Table2.Name))>1));

 

[Douglas J. Steele]

Assuming you saved that union query as, say, qryMyUnionQuery, try:

SELECT YourFieldName
FROM qryMyUnionQuery
GROUP BY YourFieldName
HAVING Count(*) > 1

 

[Guest]

Thanks, I tried this and it says my query is to complex (there are 2161
records that it is searching through). Any other ways that I could make it
work?

Richard

 

[Douglas J. Steele]

You sure you typed all the names correctly? That error message will
sometimes appear if Access can't figure out the query.