Table with date columns to records for all dates 1

[up4a]

I have a client that has a table that is a list of items and a column for each week (Mondays date) and then for each item they report "demand" for that week.
Item \ 4/28/14 \ 5/5/14 \ etc...
ABC \ 500 \ 50 CBA \ 25 \ 200
I need to convert it to
Item \ Date \ Qty
ABC \ 4/28/14\ 500
ABC \ 5/5/14 \ 50
CBA \ 4/28/14\ 25
CBA \ 5/5/14\ 200

For the life of me I can't seem to wrap my brain around how to do this.. I am assuming it is some kind of crosstab or pivot function but I am at a loss and can't even figure out how to search for the solution.

 

[SkipVought]

Hi,

Simple to do in Excel faq68-5287


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[up4a]

Thanks for that!

I will ask the client to do that and then I can just link to their document!

Love this site!

 

[dhookom]

I love Skip's Excel solution and have used it many times. You can also use a normalizing union query like:

SELECT Item, #4/28/2014# as TheDate, [4/28/14] as TheQuantity
FROM SpreadsheetLikeTable
UNION ALL
SELECT Item, #5/5/2014#, [5/5/14]
FROM SpreadsheetLikeTable
UNION ALL
SELECT Item, #5/12/2014#, [5/12/14]
FROM SpreadsheetLikeTable
--- etc ---

Duane
Hook'D on Access
MS Access MVP