A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Reverse crosstab" query: how to..`



 
 
Thread Tools Display Modes
  #1  
Old July 6th, 2004, 05:16 PM
Mike
external usenet poster
 
Posts: n/a
Default "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  
Old July 6th, 2004, 07:01 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default "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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 02:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.