query to join 3 tables

[tizwaz]

I have 3 tables all with similar data The fields I want to query on are Card ID and Card Name. I want to be able to run a query that returns all numbers with the Card Name from each of the tables. some numbers will have names in all 3 tables, some will have names in only one table. In other words I want the query to list every number (whichever table it is in) with the name for that number from either 1, 2 or 3 of the tables whichever applies. Can this be done?

 

[dhookom]

Sounds like you need a union query. If you had provided some sample records and how you expected them to display in your results, we might know for sure.

Try something like:

SELECT "Table1" as TableName, CardID, CardName
FROM Table1
UNION ALL
SELECT "Table2", CardID, CardName
FROM Table2
UNION ALL
SELECT "Table3", CardID, CardName
FROM Table3;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]