Pivot table or any other suggestions......

[mychalB]

I have a table for analytical results. the table structure consists of a location, date sampled, analyte, concentration, and reporting limit value. There are 0 to many recordsets for each location and date sampled for the various analytes.

Example:

B-1 6/23/97 Benzene 1300 ppm
B-1 6/23/97 Toluene 340 ppm
B-1 6/23/97 Xylenes 200 ppm

I need to generate an output that will follow this format. The rows will be location and date sampled. The columns will be analyte and units. The data(intersections)will be concentration.

From the above example the output should look like this...

Benzene Toluene Xylenes
B-1 6/23/97 1300 ppm 340 ppm 200 ppm
B-2 7/3/98 ND ND ND

I am trying to use the pivot table wizard but I can not control the data section to just list the concentration values instead of sum.

does anyone have any suggestions of what I should do to create an output like in my example above?

Thanks in advance

Mychal

 

[allanon]

Is it always the same number of analytes (is 3 the max) or will there be a possibility of more than 3?

 

[mychalB]

There are over 600 chemicals that could be identified for each sample. I just used a short set to show an example.

hope that helps.

Thanks in advance.

Mychal

 

[cheerio]

If there is only at most one sample for a given combination of analyte, location and date it does not matter that you are using sum as you will only be adding one figure. If that assumption is wrong we need more information as your sample data/sample answer does not address that.

If you are working in Excel then pivot tables are fine. In Access you could look at a cross tab query.

 

[zambezibill]

You will need to start with a crosstab query for your pivot table

If pivot tables are going to be an important part of future life, it might be worth the change to move to Access2002. The major part of the update was improving Access's facility with pivot tables. Pivot tables in the previous versions were probably the biggest reason unfaithful Access developers dallied with Excel.

Cheers, Bill