Crosstab Column Order Problem

  • Thread starter Thread starter Colin
  • Start date Start date
C

Colin

Hi All,

I'm trying to create a crosstab query to show lab data by
site and date as rows and Analyte by column. EG

Site Date Copper Lead Zinc (etc)
BH1 1/1/04 50 20 75
BH1 5/1/04 20 10 60
BH2 1/1/04 100 150 140
BH2 5/1/04 120 200 75

My problem is that the ordering of the analyte column
headings is only alphabetical. I'm awear that I can order
them using the "IN" predicate after the "PIVOT" statement
however the list of analytes may vary so I cannot set up
static column names.

I was hoping to embed another SELECT Statement into the IN
section of the query to specify the order I want as shown:

TRANSFORM Max(qryAnalyticalResults.Results) AS MaxOfResults
SELECT qryAnalyticalResults.SamplingPointName,
qryAnalyticalResults.Date
FROM qryAnalyticalResults
GROUP BY qryAnalyticalResults.SamplingPointName,
qryAnalyticalResults.Date
ORDER BY qryAnalyticalResults.SamplingPointName,
qryAnalyticalResults.Date
PIVOT qryAnalyticalResults.Anlayte In (SELECT
tblAnalytes.Anlayte FROM tblAnalytes);

(The analytes are ordered corectly in the tblAnalytes)
To my mind it should work but the output has only three
columns - "SamplingPointName" "Date" and "SELECT
tblAnalytes.Anlayte FROM tblAnalytes". This third column
is blank.

If anyone has any experience with embeding the select
statement into the "In statement" I'd appreciate your
thoughts. I have done it before but not on a crosstab
query. Any other sugestions would also be greatly
appreciated.

Regards

Col
 
Create a new table with all analyte possibilities, and an ordinate
position (string type, not numerical) so for every occurrence you can
'calculate' its position (copper 03, zinc 05 etc). Then, in the source
for your crosstab query, prepend that ordinal indicator to the analyte.
(ordinalPos & " " & analyte) That will sort the columns in your order.
 
Back
Top