sql union all for same table error 2

[evr72]

Hello,

I have a table that has multiple common fields for example size, size2, size3, etc.

I am trying to get only 3 fields with a union table but get an error

here is the sql code I am using

SELECT DAVLOGIC_TAPOPS.SIZE, DAVLOGIC_TAPOPS.QTY, DAVLOGIC_TAPOPS.TOOLS_REQ
FROM DAVLOGIC_TAPOPS
UNION ALL
SELECT DAVLOGIC_TAPOPS_1.SIZE2, DAVLOGIC_TAPOPS_1.QTY2, DAVLOGIC_TAPOPS_1.TOOLSREQ2 
DAVLOGIC_TAPOPS AS DAVLOGIC_TAPOPS_1
UNION ALL
SELECT DAVLOGIC_TAPOPS_2.SIZE3, DAVLOGIC_TAPOPS_2.QTY3, DAVLOGIC_TAPOPS_2.TOOLS_REQ3
DAVLOGIC_TAPOPS AS DAVLOGIC_TAPOPS_2
UNION ALL
SELECT DAVLOGIC_TAPOPS_3.SIZE4, DAVLOGIC_TAPOPS_3.QTY4, DAVLOGIC_TAPOPS_3.TOOLS_REQ4
DAVLOGIC_TAPOPS AS DAVLOGIC_TAPOPS_3
UNION ALL
SELECT DAVLOGIC_TAPOPS_4.SIZE5, DAVLOGIC_TAPOPS_4.QTY5, DAVLOGIC_TAPOPS_4.TOOLS_REQ5
DAVLOGIC_TAPOPS AS DAVLOGIC_TAPOPS_4
UNION ALL
SELECT DAVLOGIC_TAPOPS_5.SIZE6, DAVLOGIC_TAPOPS_5.QTY6, DAVLOGIC_TAPOPS_5.TOOLS_REQ6
DAVLOGIC_TAPOPS AS DAVLOGIC_TAPOPS_5
UNION ALL
SELECT DAVLOGIC_TAPOPS_6.SIZE7, DAVLOGIC_TAPOPS_6.QTY7, DAVLOGIC_TAPOPS_6.TOOLS_REQ7
DAVLOGIC_TAPOPS AS DAVLOGIC_TAPOPS_6

 

[BikeToWork]

I think you need a "FROM" clause in your UNION ALL SQL statements. You don't tell Access from which table(s) to select the fields from. You need to put FROM DAVLOGIC_TAPOPS in each of the SQL statements.

 

[PHV]

Why not simply this ?
SELECT SIZE, QTY, TOOLS_REQ FROM DAVLOGIC_TAPOPS
UNION ALL SELECT SIZE2, QTY2, TOOLSREQ2 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT SIZE3, QTY3, TOOLSREQ3 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT SIZE4, QTY4, TOOLSREQ4 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT SIZE5, QTY5, TOOLSREQ5 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT SIZE6, QTY6, TOOLSREQ6 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT SIZE7, QTY7, TOOLSREQ7 FROM DAVLOGIC_TAPOPS

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I typically add an indicator of the source column into the SQL like (borrowing PH's statement):

SELECT 1 as Num, SIZE, QTY, TOOLS_REQ FROM DAVLOGIC_TAPOPS
UNION ALL SELECT 2, SIZE2, QTY2, TOOLSREQ2 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT 3, SIZE3, QTY3, TOOLSREQ3 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT 4, SIZE4, QTY4, TOOLSREQ4 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT 5, SIZE5, QTY5, TOOLSREQ5 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT 6, SIZE6, QTY6, TOOLSREQ6 FROM DAVLOGIC_TAPOPS
UNION ALL SELECT 7, SIZE7, QTY7, TOOLSREQ7 FROM DAVLOGIC_TAPOPS

Duane
Hook'D on Access
MS Access MVP