If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Reverse crosstab" query: how to..`
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. |
#2
|
|||
|
|||
"Reverse crosstab" query: how to..`
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. -- Duane Hookom MS Access MVP "Mike" wrote in message news:_fAGc.109818$E84.40281@edtnps89... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab query oddity | Duane Hookom | Running & Setting Up Queries | 2 | July 4th, 2004 07:41 PM |
Crosstab query with same VALUE in multiple columns | ChrisJ | Running & Setting Up Queries | 1 | June 20th, 2004 10:41 PM |
Reference Main Report Crosstab Query from Subreport | blinton25 | Setting Up & Running Reports | 0 | June 7th, 2004 11:42 PM |
Showing all subrows in crosstab query | Ragnar Midtskogen | Running & Setting Up Queries | 3 | May 26th, 2004 08:16 PM |
Dates In a CrossTab query | Box 666 | New Users | 1 | May 21st, 2004 04:01 AM |