Create a query and have the feild title repeat in the rows for a union all

[bewsh1987]

Hi All



Need your help

I am trying to create a union All query which I can do no problem example below

What I need to do if possible is have the Title name as the row values i.e. [42337 Sales Volume] will show 42337 in the row values down for the first select statement then 42336 listed for the second select this will need a new column

Please I need you expertise on this

Thank you

SELECT "Week39" AS Week_No, [Week 39].[Retail Outlet Number], [Week 39].[Store Name], [Week 39].[Base Product Number], [Week 39].[Long Description], [Week 39].[42337 Sales Volume], [Week 39].[42337 Store Stock Volume], [Week 39].[42337 Store Stock Volume]
FROM [Week 39];
Union All
SELECT "Week39" AS Week_No, [Week 39].[Retail Outlet Number], [Week 39].[Store Name], [Week 39].[Base Product Number], [Week 39].[Long Description], [Week 39].[42336 Sales Volume], [Week 39].[42336 Store Stock Volume], [Week 39].[42336 Store Stock Volume]
FROM [Week 39];

[pbaldy]

Are you looking for this type of thing?

SELECT "Week39" AS Week_No, [42337 Store Stock Volume] AS 42337, 0 As 42336
FROM [Week 39]
UNION ALL
SELECT "Week39" AS Week_No, 0 AS 42337, [42336 Store Stock Volume] As 42336
FROM [Week 39]

[bewsh1987]

Thanks you for your reply

if the title is "42337 Sales Volume" then I would like 42337 listed in the rows

is this possible?

[pbaldy]

I guess I don't understand. Can you post some sample data and what you'd like the result to be?

[June7]

Why is [_____ Store Stock Volume] included twice?

I think yes.

SELECT "Week39" AS Week_No, [Retail Outlet Number], [Store Name], [Base Product Number], [Long Description], [42337 Sales Volume] AS SalesVolume, "42337" As SalesCode, [42337 Store Stock Volume] AS StockVol FROM [Week 39];
UNION ALL
SELECT "Week39", [Retail Outlet Number], [Store Name], [Base Product Number], [Long Description], [42336 Sales Volume], "42336", [42336 Store Stock Volume] FROM [Week 39];

If you normalized data structure, this UNION query would not be necessary.


Also, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[bewsh1987]

The data titles are from excel which is in pivot form the data is scraped as a pivot and I cannot get this in a listed format due to the amount of cells/data ( long story)

I am working with 8 weeks of data each day is 27000 lines one select line is one day and the 42337 is a date formatted to 29/11/2015

the union is well needed my purpose of this is to unpivot data with the tools I have without throwing a lot of formula in excel

SELECT "Week39" AS Week_No, [Week 39].[Retail Outlet Number], [Week 39].[Store Name], [Week 39].[Base Product Number], [Week 39].[Long Description], [Week 39].[42337 Sales Volume], [Week 39].[42337 Store Stock Volume], [Week 39].[42337 Store Stock Volume]


Pivot Data example

Retail Outlet Number	Store Name	Base Product Number	Long Description	42295 Sales Volume	42295 Store Stock Volume	42294 Sales Volume	42294 Store Stock Volume
5	Tesco BOLTON	60183198	MASC	1	3	1	4
5	Tesco BOLTON	61576665	VOLUME CLUBBING	1	3	1	4

New Data access query example ( I can do this without the date no problem)

Week_No	Retail Outlet Number	Store Name	Base Product Number	Long Description	Sales Volume	Store Stock Volume	Date
Week 33	5	Tesco BOLTON	60183198	MASC	1	3	42295
Week 33	5	Tesco BOLTON	61576665	VOLUME CLUBBING	1	3	42295
Week 33	5	Tesco BOLTON	60183198	MASC	1	4	42294
Week 33	5	Tesco BOLTON	61576665	VOLUME CLUBBING	1	4	42294

[June7]

This is resolved?

[bewsh1987]

In a long winded way yes I will be