query multiple tables with identical column names

[Dsperry101 via AccessMonster.com]

Hello,

I have a database that several tables (tbl113 , tbl114 , tbl115), each has
identical columns (DPGId , Description , serialnumber , columnlocation)
How can I do a query that will produce all the DPGId's in the same columns
as well as Description's , serialnumbers and columnlocations in their
respective columns ?
SELECT DPGid , Description , PostLocation , SerialNumber FROM tbl113 ,
tbl114 , tbl115 ; gives error

Thanks in advance
Dan S

 

[David Mueller]

It sounds as though you want to view the records from each table as if it
were one table. Use a union query to do that...

SELECT DPGid , Description , PostLocation , SerialNumber FROM tbl113
UNION
SELECT DPGid , Description , PostLocation , SerialNumber FROM tbl114
UNION
SELECT DPGid , Description , PostLocation , SerialNumber FROM tbl115

"UNION" will not list duplicate rows, whereas "UNION ALL" will return the
duplicates, I think.

If you want to rename the column headings, do so in the first SELECT
statement.

Hope this helps,
David

 

[John Spencer]

Use a UNION ALL query (or a UNION query).

SELECT DPGid , Description , PostLocation , SerialNumber
FROM tbl113
UNION ALL
SELECT DPGid , Description , PostLocation , SerialNumber
FROM tbl114
UNION ALL
SELECT DPGid , Description , PostLocation , SerialNumber
FROM tbl115

This query can only be built in SQL view. The query design view (the grid)
cannot build a union query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dsperry101 via AccessMonster.com]

David,

Is the query a single query ? I tried this :

SELECT DPGid , Description , PostLocation , SerialNumber FROM tbl113 ,
tbl114 , tbl115 UNION ALL;
got an error

Use a UNION ALL query (or a UNION query).

SELECT DPGid , Description , PostLocation , SerialNumber
FROM tbl113
UNION ALL
SELECT DPGid , Description , PostLocation , SerialNumber
FROM tbl114
UNION ALL
SELECT DPGid , Description , PostLocation , SerialNumber
FROM tbl115

This query can only be built in SQL view. The query design view (the grid)
cannot build a union query.

Hello,

[quoted text clipped - 10 lines]

Thanks in advance
Dan S

 

[Dsperry101 via AccessMonster.com]

David ,
I did a little experimenting and got it working, thanks for your time and
knowledge.
Dan S

David,

Is the query a single query ? I tried this :

SELECT DPGid , Description , PostLocation , SerialNumber FROM tbl113 ,
tbl114 , tbl115 UNION ALL;
got an error

Use a UNION ALL query (or a UNION query).

[quoted text clipped - 14 lines]