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. The import process makes the widths the column names.

height 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. Any clues - I don't want to change too much about the way the imported excel file looks becasue I have no control over that (I already discard miscellaneous heading and junk before I import it)

Hope you can help

If you import the Excel data to a predefined table you won't have the problem with the field names.

Create a series of queries, one for each width and them union them together to produce the table you want.

Thanks for you reply ..

Are we talking SQL to do the unions? Or do we just create the (many) select queries to pull out the individual widths and them pull them together with another select query over the top.

I haven't tried yet but if you could tell me if this is an SQL endeavour or one that can be done in the query window - that will send me on the right path.

Cheers

PS Thanks for the tip about the predefined table - trouble I have is that I never will know how many widths there are going to be, as each supplier is different and they change there products periodically

Thanks! the query-over-the-top of the little ones works nicely. I'll work on how to that this to the next step. Help much appreciated (funny, this topic if rife through the various Access boards and no-one has answered it as simply!)

Obviously the small queries can be done in the Design Grid. All union queries have to be written in the SQL window, but Access help is fairly OK on this topic.

I can't do complicated code, so I have to find simple solutions to complex questions! Plus I'm trained as a mathematician and we don't like writing lots of words.