Query to move data from 1 column to multiple rows 1

[jdhamot]

Hello,

I currently have an Access db with a query consisting of the following data:
[pre]DTE STORE_ID FREIGHT TAX_DUE SVC_USED
6/2/14 1 0.00 284.72 -13.26
6/2/14 2 20.00 579.27 -40.00
6/2/14 3 26.90 11.74 0.00
[/pre]

I am trying to have a query display the information like below:
[pre]DTE STORE_ID TYPE AMOUNT
6/2/14 1 FREIGHT 0.00
6/2/14 1 TAX_DUE 284.72
6/2/14 1 SVC_USED -13.26
6/2/14 2 FREIGHT 20.00
6/2/14 2 TAX_DUE 579.27
6/2/14 2 SVC_USED -40.00
6/2/14 3 FREIGHT 26.90
6/2/14 3 TAX_DUE 11.74
6/2/14 3 SVC_USED 0.00
[/pre]

Any help or recommendations would be greatly appreciated. The reason I was hoping to do this in a query is because the data will be changing daily and I will then reference the query to create export/import files for another product. If using Access Modules (VBA Coding) is the only way to perform this task then I will move the thread to the appropriate forum.

Thank you for your time and help!
Josh

 

[dhookom]

You can use a normalizing union query:

SELECT DTE, STORE_ID, "Freight" as [Type], FREIGHT As Amount
FROM [YourQueryName]
UNION ALL
SELECT DTE, STORE_ID, "TAX_DUE", TAX_DUE
FROM [YourQueryName]
UNION ALL
SELECT DTE, STORE_ID, "SVC_USED", SVC_USED
FROM [YourQueryName]

Duane
Hook'D on Access
MS Access MVP

 

[jdhamot]

Thank you so much... I knew it was something I've done before, but just had a brain lapse. It was driving me crazy!

Josh

 

[Andrzejek]

Duane, does it have to be UNION ALL? Wouldn’t be enough to use just UNION? There should be no repeated records anyway.

Just curious...


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I typically use UNION ALL since I assume it is more efficient. Without the "ALL", the statement will attempt to find and weed out duplicates.

Duane
Hook'D on Access
MS Access MVP