Convert columns into rows (1 Viewer)

[lwarren1968]

How do I convert columns into rows in a query. I have several [LEVELS] and hundreds of [SKU] with various prices. See sample below. Is there a simple SQL Query for doing this?

My orig. data looks like this:

SKU Level-1 Price Level-2 Price Level-3 Price

Need it to look like this:

SKU LEVEL PRICE
00029800008 1 7.99
00029800008 2 7.75
00029800008 3 7.19

 

[arnelgp]

use Crosstab query:

TRANSFORM First(urTableName.price) AS FirstOfprice
SELECT urTableName.sku
FROM urTableName
GROUP BY urTableName.sku
PIVOT urTableName.level;

 

[theDBguy]

Hi. I was going to say you could also use a UNION query...

 

[lwarren1968]

can you provide sample of the union query?

 

[arnelgp]

I did not see there is "Level" in the query:

RANSFORM First(urTableName.price) AS FirstOfprice
SELECT urTableName.sku
FROM urTableName
GROUP BY urTableName.sku
PIVOT "Level-" & urTableName.level & " Price";

 

[lwarren1968]

I'm self taught Arnegp. Mainly simple stuff in access. So, I'm not sure what you are having me do. Language is new to me. Can you give me more detail?

 

[arnelgp]

create a New query from your table.
double click these fields to include in the query:
sku, level, price.

on the ribbon, click Crosstab.

below window (table):

field: sku
total: group by
crosstab: row heading

field: Expr1: "Level-" & yourTableName.[Level] & " Price"
total: group by
crosstab: column heading

field: price
total: first
crosstab: value

 

[arnelgp]

did you get it yet.
open crosstab1 query in design view.

 

[isladogs]

Arnel
I think you may have misunderstood the question.
If I understand correctly, the data is already in spreadsheet (crosstab) structure and the OP wants to convert it to a normalised structure.

If that is the case, the OP could use a union query ...or export the table to Excel and use the approach explained in this thread https://www.access-programmers.co.uk/forums/showthread.php?t=306580
That will create a normalised table that can be reimported to Access in place of the existing data.

@lwarren -I hope what I've written makes sense to you

 

[lwarren1968]

You are correct Isladogs. Can you explain the union query. This is going to be an ongoing (daily) process and I was just hoping to link data and allow it convert with min. steps. I've unioned several spreadsheets into on in the past, but have never done what you are saying.

 

[isladogs]

It would be far better to convert the data to your normalised structure permanently. So I would recommend the other approach first.
If that's not possible for your purposes then I can explain the union query idea BUT using a spreadsheet structure in Access will cause you endless problems.

EDIT If you decide to keep the existing data after all (despite my strong advice to the contrary) your union query would be similar to this

SELECT TableName.sku, TableName.[Price-1 Level]
FROM TableName
UNION SELECT TableName.sku, TableName.[Price-2 Level]
FROM TableName
UNION SELECT TableName.sku, TableName.[Price-3 Level]
FROM TableName;

To create this query, open a new query in design view, change to SQL view then paste the code into the window replacing TableName with the actual name of your table

 

[lwarren1968]

the data is coming from an outside source, so I am unable to restructure. All the reason for doing in order to get detail uploaded into our database.

 

[isladogs]

I've just edited my reply adding the info needed for a union query.
However the fact that the data comes in that format from an outside source doesn't mean you need to keep using it in that format

 

[June7]

Looks like fields are:
SKU
[Level-x Price]

The first SELECT defines field names and data types. There is a limit of 50 SELECT lines. Only last line has semi-colon.

SELECT SKU, [Level-1 Price] AS Price, 1 AS LevelID FROM TableName
UNION SELECT SKU, [Level-2 Price], 2 FROM TableName
UNION SELECT SKU, [Level-3 Price], 3 FROM TableName;

Level is a reserved word hence the name LevelID. I once had a field named Level and encountered issue and had to change. Don't use reserved words as names for anything http://allenbrowne.com/AppIssueBadWord.html

A poster had field named [Code Description] and Access had an issue with passing field name to form OrderBy property in VBA because of space in name in spite of using [] because Description is a reserved word. Had to change field name. Don't use spaces nor punctuation/special characters (underscore only exception).

 

[isladogs]

June's version of the union query is better and more like the original request.

 

[arnelgp]

now I get it. I made a little utility for you.
check tblProduct, it has no record.
check also the other table.
this is the one you want to be imported to tblProducts.
you can copy the Form and Module1 to your db.