morph from horizontal to vertical

[JR]

I have to work with two data bases both of which have are
inherited and can not be changed because they both feed
different models.

Database one is arranged horizontally:

(key)
Customer ID | Location | Item 1 QTY | Item 2 QTY | Item 3
QTY

Database is arranged Vertically:

(key) (key) (key)
Customer ID | Location | Item | QTY

I must take database one and morph it into data base two.

I haven't the foggiest idea how to do so - can anyone lend
a hand?

Thanks!

 

[Douglas J. Steele]

SELECT [Customer ID], Location, 1 AS Item, [Item 1 Qty]
FROM MyTable
WHERE [Item 1 Qty] <> 0
UNION ALL
SELECT [Customer ID], Location, 2 AS Item, [Item 2 Qty]
FROM MyTable
WHERE [Item 2 Qty] <> 0
UNION ALL
SELECT [Customer ID], Location, 3 AS Item, [Item 3 Qty]
FROM MyTable
WHERE [Item 3 Qty] <> 0

etc.

 

[John Nurick]

Hi JR,

Work with a copy of your data in case of accidents.

Start off with a Select query on the first table (I'll call it tblWide)
with the following fields:

Customer ID
Location
Item: 1
QTY: [Item 1 Qty]

When it's working right, convert it into an Append query and run it to
append all the Item 1 data to the second table.

Then modify it
Item: 2
QTY: [Item 2 Qty]
and append the Item 2 data, and so on.