help needed with union query

C

CarolM

Hi

I have a query that is written as follows which someone kindly helped me
with and this works perfectly. When i run this query the heading is
productcode and all the codes are listed underneath that heading.

SELECT [Prod 1] AS productcode FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8] FROM [PICKSHEETUNI];

What i would like to do now is add in two further columns but ive tried
various different things and cant seem to get it right.

The two columns that i need to add in are location (loc 1, loc 2, loc 3, loc
4 etc etc) and (qty 1, qty 2, qty 3, qty 4 etc etc).

The end result should in theory look something like:-

productcode location quantity
714335469010 H69 1
903548103919 F69 2

Thanks in advance for your help, as always.

Carol
 
G

Gerald Stanley

Try something along the lines of

SELECT [Prod 1] AS productcode, [loc 1] AS location, [qty
1] as quantity FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [loc 2], [qty 2] FROM
[PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [loc 3], [qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [loc 4], [qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [loc 5], [qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [loc 6], [qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [loc 7], [qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [loc 8], [qty 8] FROM
[PICKSHEETUNI];

Hope This Helps
Gerald Stanley MCSD
 
C

CarolM

Hi Gerald

I have written the query as:-

SELECT [Prod 1] AS productcode, [Loc 1] AS location, [Qty 1], as quantity
FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [Loc 2], [Qty 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [Loc 3], [Qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [Loc 4], [Qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [Loc 5], [Qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [Loc 6], [Qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [Loc 7], [Qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [Loc 8], [Qty 8] FROM [PICKSHEETUNI];

When i try to run the query i get the following error message.

The SELECT statement includes a reserved word or an argument made that is
misspelled or missing, or the punctuation is incorrect.

Thanks for your help Gerald

Carol

Gerald Stanley said:
Try something along the lines of

SELECT [Prod 1] AS productcode, [loc 1] AS location, [qty
1] as quantity FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [loc 2], [qty 2] FROM
[PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [loc 3], [qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [loc 4], [qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [loc 5], [qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [loc 6], [qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [loc 7], [qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [loc 8], [qty 8] FROM
[PICKSHEETUNI];

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi

I have a query that is written as follows which someone kindly helped me
with and this works perfectly. When i run this query the heading is
productcode and all the codes are listed underneath that heading.

SELECT [Prod 1] AS productcode FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8] FROM [PICKSHEETUNI];

What i would like to do now is add in two further columns but ive tried
various different things and cant seem to get it right.

The two columns that i need to add in are location (loc 1, loc 2, loc 3, loc
4 etc etc) and (qty 1, qty 2, qty 3, qty 4 etc etc).

The end result should in theory look something like:-

productcode location quantity
714335469010 H69 1
903548103919 F69 2

Thanks in advance for your help, as always.

Carol
.
Click to expand...
Click to expand...
 
G

Gerald Stanley

Try removing the comma in [Qty 1], as quantity.

If the error still persists, try replacing location with
locn and quantity with qnty in case either of these are
reserved words.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald

I have written the query as:-

SELECT [Prod 1] AS productcode, [Loc 1] AS location, [Qty 1], as quantity
FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [Loc 2], [Qty 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [Loc 3], [Qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [Loc 4], [Qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [Loc 5], [Qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [Loc 6], [Qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [Loc 7], [Qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [Loc 8], [Qty 8] FROM [PICKSHEETUNI];

When i try to run the query i get the following error message.

The SELECT statement includes a reserved word or an argument made that is
misspelled or missing, or the punctuation is incorrect.

Thanks for your help Gerald

Carol

Gerald Stanley said:
Try something along the lines of

SELECT [Prod 1] AS productcode, [loc 1] AS location, [qty
1] as quantity FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [loc 2], [qty 2] FROM
[PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [loc 3], [qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [loc 4], [qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [loc 5], [qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [loc 6], [qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [loc 7], [qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [loc 8], [qty 8] FROM
[PICKSHEETUNI];

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi

I have a query that is written as follows which someone kindly helped me
with and this works perfectly. When i run this query the heading is
productcode and all the codes are listed underneath that heading.

SELECT [Prod 1] AS productcode FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8] FROM [PICKSHEETUNI];

What i would like to do now is add in two further columns but ive tried
various different things and cant seem to get it right.

The two columns that i need to add in are location (loc 1, loc 2, loc 3, loc
4 etc etc) and (qty 1, qty 2, qty 3, qty 4 etc etc).

The end result should in theory look something like:-

productcode location quantity
714335469010 H69 1
903548103919 F69 2

Thanks in advance for your help, as always.

Carol
.
Click to expand...
Click to expand...
.
Click to expand...
 
C

CarolM

perfect! - thank you so much.

Carol

Gerald Stanley said:
Try removing the comma in [Qty 1], as quantity.

If the error still persists, try replacing location with
locn and quantity with qnty in case either of these are
reserved words.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald

I have written the query as:-

SELECT [Prod 1] AS productcode, [Loc 1] AS location, [Qty 1], as quantity
FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [Loc 2], [Qty 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [Loc 3], [Qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [Loc 4], [Qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [Loc 5], [Qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [Loc 6], [Qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [Loc 7], [Qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [Loc 8], [Qty 8] FROM [PICKSHEETUNI];

When i try to run the query i get the following error message.

The SELECT statement includes a reserved word or an argument made that is
misspelled or missing, or the punctuation is incorrect.

Thanks for your help Gerald

Carol

Gerald Stanley said:
Try something along the lines of

SELECT [Prod 1] AS productcode, [loc 1] AS location, [qty
1] as quantity FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2], [loc 2], [qty 2] FROM
[PICKSHEETUNI]
UNION ALL SELECT [Prod 3], [loc 3], [qty 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4], [loc 4], [qty 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5], [loc 5], [qty 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6], [loc 6], [qty 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7], [loc 7], [qty 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8], [loc 8], [qty 8] FROM
[PICKSHEETUNI];

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi

I have a query that is written as follows which someone
kindly helped me
with and this works perfectly. When i run this query the
heading is
productcode and all the codes are listed underneath that
heading.

SELECT [Prod 1] AS productcode FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 2] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 3] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 4] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 5] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 6] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 7] FROM [PICKSHEETUNI]
UNION ALL SELECT [Prod 8] FROM [PICKSHEETUNI];

What i would like to do now is add in two further columns
but ive tried
various different things and cant seem to get it right.

The two columns that i need to add in are location (loc 1,
loc 2, loc 3, loc
4 etc etc) and (qty 1, qty 2, qty 3, qty 4 etc etc).

The end result should in theory look something like:-

productcode location quantity
714335469010 H69 1
903548103919 F69 2

Thanks in advance for your help, as always.

Carol
.
Click to expand...
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error in union query 2
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
re-post of query 13
Union Query multiple tables 4
How can I improve my query? 2
Union query 5
Excel 2007 Pivot Table conditional formatting 4
SUM in a UNION query 2

Top