Union Query problem

[Simon]

Can any ne see what is wrong with this union Query

SELECT tblorderProduct.[Quantity] AS Quantity,
tblorderProduct.[ProductID] AS IDNumber,
tblorderProduct.[TotalExVAT] AS Total,
tblorderProduct.[Date] AS Date,
'tblorderProduct' AS TheType

FROM tblorderProduct


UNION ALL SELECT tblorderProductsWS.[Quantity] AS Quantity,
tblorderProductsWS.[IDNumber] AS IDNumber,
tblorderProductsWS.[Date] AS Date,
tblorderProductsWS.[Total] AS Total,

'tblorderProductsWS' AS TheType
FROM tblorderProductsWS



Error Message
The SELECT dtatment includes a reserved word or an argument name that
is misspelled or missing or the ounction is incorrect


Thanks

Simon

 

[Baz]

"Date" is a reserved word, you can't use it as an alias.

Once you've fixed that, the next problem you will encounter is that you've
transposed the date and total columns between the two queries.

 

[SteveM]

'Date' is a reserved word in Access, in fact, it is actually an Access
function that returns the current days date. It shouldn't be a column name in
your table, Use OrderDate or something as descriptive.

You don't need to use the 'AS' to create an alias for the column if the name
of the column is ok. For example, you don't need to alias Quantity AS
Quantity - it will already return with that name.

Also, the field order/names one each side of your Union must match - you
have Total and Date in different order...

These lines bothers me:

'tblorderProduct' AS TheType
'tblorderProductsWS' AS TheType

What are they supposed to represent?

Steve

Can any ne see what is wrong with this union Query

SELECT tblorderProduct.[Quantity] AS Quantity,
tblorderProduct.[ProductID] AS IDNumber,
tblorderProduct.[TotalExVAT] AS Total,
tblorderProduct.[Date] AS Date,
'tblorderProduct' AS TheType

FROM tblorderProduct


UNION ALL SELECT tblorderProductsWS.[Quantity] AS Quantity,
tblorderProductsWS.[IDNumber] AS IDNumber,
tblorderProductsWS.[Date] AS Date,
tblorderProductsWS.[Total] AS Total,

'tblorderProductsWS' AS TheType
FROM tblorderProductsWS



Error Message
The SELECT dtatment includes a reserved word or an argument name that
is misspelled or missing or the ounction is incorrect


Thanks

Simon

 

[Douglas J. Steele]

That's a pretty common approach to ensure you know from which table details
in the Union statement originated.

 

[Douglas J. Steele]

Others have already told you that Date is a reserved word, so shouldn't be
used as a field name.

For a good discussion of what names you should avoid, see what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[SteveM]

Gotcha but the identifier used may make sense to the developer but not users...
If there are a lot of records being returned, it would be more efficient to
use a simpler identifier (less characters) or even a digit...unless this
value is being used as a parameter somewhere else.

Steve