Union Query

[Simon]

I have the following query that does not work, if i remove of of the
sets of data from it if works fine, is there somthing i have to do
whne i have 3 or more sets of data


Thanks


SELECT qryP.[IDNumber] AS IDNumber,
qryHotTubsWS.[ProductName] AS ProductName,
qryHotTubsWS.[Price] AS Price,
qryHotTubsWS.[ProductCode] As ProductCode,
'qryHotTubsWS' AS TheType
FROM qryHotTubsWS


UNION ALL SELECT qryProductsWS.[IDNumber] AS IDNumber,
qryProductsWS.[ProductName] AS ProductName,
qryProductsWS.[Price] AS Price,
qryProductsWS.[ProductCode] AS ProductCode,
'qryProductWS' AS TheType
FROM qryProductsWS


UNION ALL SELECT qryExtrasWS.[IDNumber] AS IDNumber,
qryExtrasWS.[ProductName] AS ProductName,
qryExtrasWS.[Price] AS Price,
qryExtrasWS.[ProductCode] AS ProductCode,
'qryExtrasWS' AS TheType
FROM qryExtrasWS
ORDER BY IDNumber;

 

[ChrisM]

In message (e-mail address removed),

I have the following query that does not work, if i remove of of the
sets of data from it if works fine, is there somthing i have to do
whne i have 3 or more sets of data


Thanks


SELECT qryP.[IDNumber] AS IDNumber,
qryHotTubsWS.[ProductName] AS ProductName,
qryHotTubsWS.[Price] AS Price,
qryHotTubsWS.[ProductCode] As ProductCode,
'qryHotTubsWS' AS TheType
FROM qryHotTubsWS


UNION ALL SELECT qryProductsWS.[IDNumber] AS IDNumber,
qryProductsWS.[ProductName] AS ProductName,
qryProductsWS.[Price] AS Price,
qryProductsWS.[ProductCode] AS ProductCode,
'qryProductWS' AS TheType
FROM qryProductsWS


UNION ALL SELECT qryExtrasWS.[IDNumber] AS IDNumber,
qryExtrasWS.[ProductName] AS ProductName,
qryExtrasWS.[Price] AS Price,
qryExtrasWS.[ProductCode] AS ProductCode,
'qryExtrasWS' AS TheType
FROM qryExtrasWS
ORDER BY IDNumber;

in what way does it not work with all 3 sub-queries?

 

[Andy Hull]

Hi Simon

Union queries are fine with 3 or more sets of data.

Did you know the frst line of your SELECT FROM qryHotTubsWS selects IDNumber
from qryP and not qryHotTubsWS?

I expect this is just a typo when posting your question but just in case...

Regards

Andy Hull