This should be simple ...

[Intrinicity]

I'm sure this would be a common requirement

I have a table (imported from excel) that is a pricing grid for
blinds. The header row at the top is widths(mm) and the header row
down the left is heights(mm). The data is the price for each
combination. The import process makes the widths the column names.

var1 600 800 1200 ...
300 $60 $80 $100
600 $80 $100 $120
900 $120 $140 $160
..
..
all I want to do is to take that table and make it flattened, like
this:

height width price
300 600 $60
300 800 $80
300 1200 $100
600 600 $80
600 800 $100
600 1200 $120
900 600 $120
900 800 $140
900 1200 $160

I can't think how to do this in a query - I could take the flat
version and create a crosstab to go the other way, but not in this
direction. Any clues - I don't want to change too much about the way
the imported excel file looks because I have no control over that (I
already discard miscellaneous headings and junk before I import it)

Hope you can help

 

[John Spencer]

You could use a normalizing union query if there are not too many widths.

Something like the following
SELECT Height, 600 as Width, [600] as Price
FROM The Table
UNION ALL
SELECT Height, 800 as Width, [800] as Price
FROM The Table
UNION ALL
SELECT Height, 1200 as Width, [1200] as Price
FROM The Table


Then you could use the Union query as the source to populate a table or just
use the Union query as if it were the table. Using the query itself might
be slow since there won't be any indexes available, but it may be fast
enough for you.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

The direction you are headed is spot on.

If this is a one-off import, it's probably easiest to create the target
table with its 3 fields, and then use an Append query to append one column
at a time from the original spreadsheet. (Append on Query menu, in query
design.)

 

[David Cox]

i did a google groups search on swap rows and columns and *excel* and the
first response included:
'Mark the range of your data. Copy it all. Choose another sheet. Paste
Special>Transpose '