"Reverse crosstab" query: how to..`

[Mike]

To anyone who can help:

I have a table that looks like this (measuring the price of a product on a
certan date in various cities:

City 1 Jun 7 Jun 21 Jun .....etc........30 Dec (a
total of 50 dates)
Toronto $1.34 $1.45 $1.23 $1.42
Vancouver $1.23 $1.34 $1.42 $1.33
Montreal
Halifax
(a total of 50 cities)

I want to convert the table to look like this:

City Date Price
Toronto 1 Jun $1.34
Toronto 7 Jun $1.45
........
Vancouver 1 Jun $1.23
etc, etc.

This is, in effect, the reverse of a crosstab query. I suspect that a union
query is the answer, but I'm a bit baffled as to how to get a field in the
original table (1 June for example), to become data in the the query
results.

Based on the example, can anyone show me the sql code that would do the
trick?

Any help appreciated. Thanks.

 

[Duane Hookom]

SELECT City, #6/1/2004# as The Date, [1 Jun] as Amount
FROM tblSpreadSheet
UNION ALL
SELECT City, #6/7/2004#, [7 Jun]
FROM tblSpreadSheet
UNION ALL
etc....
It is very possible that your query may become too complex as you add more
dates.