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

-- 
Danny C. Sperry

Message posted via http://www.accessmonster.com

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




"Dsperry101 via AccessMonster.com" wrote:

> 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
> 
> -- 
> Danny C. Sperry
> 
> Message posted via http://www.accessmonster.com
> 
> 

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" <u24149@uwe> wrote in message 
news:791603421abce@uwe...
> 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
>
> -- 
> Danny C. Sperry
>
> Message posted via http://www.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
John Spencer wrote:
>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

-- 
Danny C. Sperry

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1

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

Dsperry101 wrote:
>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]
>>>                  Thanks in advance
>>>                  Dan S
>

-- 
Danny C. Sperry

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1