Transposed Query Question

[everest]

Hi,

I have a question about querying environmental data so that it is transposed (sort of).

I have an Access table with three fields, Element/Chemical, Date, and Result.

For example, I have:
Element Date Result
------- -------- ---------
Iron 2/1/03 10
Iron 2/2/03 20
Zinc 2/1/03 10
Copper 2/1/03 10
Zinc 2/2/03 20

This is a simplified version of the table that I'm working with. In the real table, I have little more than 130 different elements/chemicals and about 5000 records. Our company has developed some kind of data analysis tool to run in Excel but the data needs to be formatted differently.

Basically, I have to put it in this format:

Date Zinc Iron Copper
2/1/03 10 10 10
2/2/03 20 20

In a sense, the data is sort of transposed, but the transpose function in Excel won't format it in this manner. Since there are only 130 different chemicals, I could probably do it manually, but just asking to see if there is an easier way. Any help would be appreciated. Thanks.

Everest

 

[JeremyNYC]

Check into the help files for crosstab queries.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[H82W8]

TRANSFORM Max(tblTest.Result) AS MaxOfResult
SELECT tblTest.Date
FROM tblTest
GROUP BY tblTest.Date
PIVOT tblTest.Chemical;

I used max here but use whichever aggregate Fn you need (perhaps Sum() or Last(), etc). If there is only one, Max works nicely.

 

[JMelvin]

With the data you have provided I am unsure what you are needing in the transposed table. Are those values supposed to be averages or sums?

 

[everest]

The data in the transposed table needs to be unique/individual data points. To get around this, I just checked (counted) to make sure that each date has only one instance of a chemical/element. Then I just use a max,min, or average. Should return only one value for that field.

I used the crosstab query as JeremyNYC mentioned and it somewhat gets the job done.

Thanks for all your help.