3-Tier Crosstab Report

C

Cydney

I am needing to create a query (or queries) which when completed will
popluate a report that has a 3-tiered column layout. I have multiple Stations
(numbered text field like: PMW-8) where multiple Samples (numbered text like:
ATA-001) were taken on various Dates (Month/Year format). Those become my
tiers. I cannot depend on all Stations or Sample IDs being used, so I need to
make sure the headers are dynamic.

I need to identify the total amount of each Parameter (Ferrous Iron,
Methane, Sulfate, etc..) that were Sampled on the various random Dates at
each of the Stations.

Does anyone have a good example of this type of layout?

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 
S

Steve

How about a crosstab query using Sample Dates as columns and Samples as rows
grouped on Station.

Steve
(e-mail address removed)
 
C

Cydney

Thank you for your response.
That only gives me the one column heading. I also need to have Station IDs
and Sample IDs to be grouped at the top as well. There are about 65 stations
and any number of Sample IDs and Sample Dates.. so I can't make anything
absolute. The closest thing to absolute is the Station IDs. They probably
won't be adding any more.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 
K

KARL DEWEY

Can you layout what you would like to see?
Something like this --
Date1 Date2 Date3
Station1
Sample1
Sample2
Sample3
Station2
Sample1
Sample2
 
C

Cydney

Actually more like this:

Station ID: PMW-1 PMW-3 PMW-8 PMW-9 PMW-10
Sample ID: ATA-001 ATA-002 ATA-003 ATA-004 ATA-005
Parameters mm/yyyy: 3/09 3/09 3/09 3/09 3/09

Total BTEX 387.9 BDL BDL BDL BDL
Benzene 207.0 BDL 208.1 BDL BDL
Toluene 30.9 BDL BDL <1 BDL
Ethyl benzene 62.1 BDL BDL BDL BDL
Xylenes (total) 87.9 BDL BDL BDL BDL

There are several more parameters and about 65 different Station IDs each
with their own set of Sample IDs and dates.

I truly appreciate the help.
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 
S

Steve

Cydney,

My suggestion would give you the layout Karl shows here.

Steve
(e-mail address removed)
 
C

Cydney

My data is set up like this
StaID DateSampled SampleID Benzene Toluene EthylBenzene TotalXylenes NitrogenNitrate
PMW-01 4/1/1997 ATA001 230 64 72 33 --
PMW-01 4/1/1997 ATA001 230 64 72 33
PMW-01 5/1/1998 ATA001 202 18 75.5 6 --
PMW-01 5/1/1998 ATA001 202 18 75.5 6
PMW-01 184 19.8 71.7 12.9 --
PMW-01 11/1/1998 ATA001 184 19.8 71.7 12.9
PMW-01 231 174 12 45 BDL
PMW-01 5/1/1999 ATA001 174 12 45 BDL


--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 
C

Cydney

Karl, do you have any other suggestions?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top