cross tab query help

[misschanda via AccessMonster.com]

Hello,
I am trying to make a crosstab query , in which the parameters are listed as
column headings and the results are beneath for each sample.
So for instance if there were three different parameters for sample one it
would be:

Sample 1 Height Weight Location
5,11 215 Alabama
5,4 105 Alaska
6,1 245 Florida
It would not repeat the sample number or list the results next to parameter
The following is the current sql for what I have tried.
Any help is appreciated

Thanks,

SELECT DISTINCT Duke_Samples_Transformer.Manufacturer,
Duke_Samples_Transformer.TXSerialNumber, OrderDetails.SampleNumber, Orders.
OrderID, OrderDetails.CustomerSampleNumber, OrderDetails.CollectDate, Results.
ApprovedDate, Duke_Samples_Transformer.Temperature, Duke_Samples_Transformer.
Port, Duke_Samples_Transformer.WorkOrder, Results.Test, Results.Param,
Results.Result, Results.Units, Results.ResultStatus
FROM (((Orders INNER JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID)
INNER JOIN SampleDetails ON (OrderDetails.SampleNumber=SampleDetails.
SampleNumber) AND (OrderDetails.OrderID=SampleDetails.OrderID)) INNER JOIN
Results ON (SampleDetails.Test=Results.Test) AND (SampleDetails.
SampleNumber=Results.SampleNumber) AND (SampleDetails.OrderID=Results.OrderID)
) INNER JOIN Duke_Samples_Transformer ON (Results.
SampleNumber=Duke_Samples_Transformer.SampleNumber) AND (Results.
OrderID=Duke_Samples_Transformer.OrderID)
WHERE (((OrderDetails.OrderDetails_User4) Is Null) AND ((OrderDetails.
SampleNumber) Not In (SELECT DISTINCT Results.SampleNumber FROM Results WHERE
Results.ResultStatus < 3))
ORDER BY OrderDetails.SampleNumber, Orders.OrderID;

 

[Kardan via AccessMonster.com]

Hi

What you are trying to do with a crosstab query is not possible. This is
because the query will build a data table which requires the returned data
identifier on each row.

To achieve what you are trying to do you have only one option;

Build a report with a crosstab query as the source. Then in the report use
the suppress duplicates option on the fields you do not want repeated. The
only problem with this is that if you later have more parameters (i.e. column
headings) these would need to be added to the report, likewise if any
disappeared the report would produce an unknown field error.

Whether you choose to do this is up to you depending on how stable your data
is and what you want the results for.

I also not that the SQL you posted in not for a crosstab query.

Regards

Richard

 

[misschanda via AccessMonster.com]

Hey thanks for the reply.
I have been playing around and made the following sql. taking what you said i
was able hide duplicate in report, but instead of having the numeric results
show underneath the paramater, I got X's. like so:

Sample id numeric results bromide chloride oxy etc

202 1.7 x
2003 1.3 x
2003 2.1 x
2003 5.1 x

It should be:
Sample id bromide chloride oxy etc
202 1.7
2003 5.1 1.3 2.1

How do i arrange to have the numeric values fall under the parameters instead
of the X.

thanks!


TRANSFORM First(IIf([orderdetails.samplenumber] Is Null,"","X")) AS [the
value]
SELECT OrderDetails.samplenumber, Duke_Samples_Transformer.Manufacturer,
Duke_Samples_Transformer.TXSerialNumber, Results.NumericResult
FROM (((Orders INNER JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID)
INNER JOIN SampleDetails ON (OrderDetails.SampleNumber=SampleDetails.
SampleNumber) AND (OrderDetails.OrderID=SampleDetails.OrderID)) INNER JOIN
Results ON (SampleDetails.Test=Results.Test) AND (SampleDetails.
SampleNumber=Results.SampleNumber) AND (SampleDetails.OrderID=Results.OrderID)
) INNER JOIN Duke_Samples_Transformer ON (Results.
SampleNumber=Duke_Samples_Transformer.SampleNumber) AND (Results.
OrderID=Duke_Samples_Transformer.OrderID)
WHERE (((OrderDetails.OrderDetails_User4) Is Null) AND ((OrderDetails.
SampleNumber) Not In (SELECT DISTINCT Results.SampleNumber FROM Results WHERE
Results.ResultStatus < 3))
GROUP BY OrderDetails.samplenumber, Results.param, Duke_Samples_Transformer.
Manufacturer, Duke_Samples_Transformer.TXSerialNumber, Results.NumericResult
PIVOT Results.param;

Hi

What you are trying to do with a crosstab query is not possible. This is
because the query will build a data table which requires the returned data
identifier on each row.

To achieve what you are trying to do you have only one option;

Build a report with a crosstab query as the source. Then in the report use
the suppress duplicates option on the fields you do not want repeated. The
only problem with this is that if you later have more parameters (i.e. column
headings) these would need to be added to the report, likewise if any
disappeared the report would produce an unknown field error.

Whether you choose to do this is up to you depending on how stable your data
is and what you want the results for.

I also not that the SQL you posted in not for a crosstab query.

Regards

Richard

Hello,
I am trying to make a crosstab query , in which the parameters are listed as

[quoted text clipped - 30 lines]

Results.ResultStatus < 3))
ORDER BY OrderDetails.SampleNumber, Orders.OrderID;

 

[Kardan via AccessMonster.com]

Hi

Try using the Crosstab wizard in Access to build the crosstab section by
going to the Queries View and selecting 'New Query Using Wizard' and choose
Crosstab.

As your query is based on several joined tables it may be easier to initially
save a query to obtain the records you want and then build the crosstab
against that.

You could always reduce this to one query later by nesting the SQL with
brackets as your source, such as;

SELECT SubA.[......... FROM (SELECT ............) AS SubA ..........

Regards

Richard

Hey thanks for the reply.
I have been playing around and made the following sql. taking what you said i
was able hide duplicate in report, but instead of having the numeric results
show underneath the paramater, I got X's. like so:

Sample id numeric results bromide chloride oxy etc

202 1.7 x
2003 1.3 x
2003 2.1 x
2003 5.1 x

It should be:
Sample id bromide chloride oxy etc
202 1.7
2003 5.1 1.3 2.1

How do i arrange to have the numeric values fall under the parameters instead
of the X.

thanks!

TRANSFORM First(IIf([orderdetails.samplenumber] Is Null,"","X")) AS [the
value]
SELECT OrderDetails.samplenumber, Duke_Samples_Transformer.Manufacturer,
Duke_Samples_Transformer.TXSerialNumber, Results.NumericResult
FROM (((Orders INNER JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID)
INNER JOIN SampleDetails ON (OrderDetails.SampleNumber=SampleDetails.
SampleNumber) AND (OrderDetails.OrderID=SampleDetails.OrderID)) INNER JOIN
Results ON (SampleDetails.Test=Results.Test) AND (SampleDetails.
SampleNumber=Results.SampleNumber) AND (SampleDetails.OrderID=Results.OrderID)
) INNER JOIN Duke_Samples_Transformer ON (Results.
SampleNumber=Duke_Samples_Transformer.SampleNumber) AND (Results.
OrderID=Duke_Samples_Transformer.OrderID)
WHERE (((OrderDetails.OrderDetails_User4) Is Null) AND ((OrderDetails.
SampleNumber) Not In (SELECT DISTINCT Results.SampleNumber FROM Results WHERE
Results.ResultStatus < 3))
GROUP BY OrderDetails.samplenumber, Results.param, Duke_Samples_Transformer.
Manufacturer, Duke_Samples_Transformer.TXSerialNumber, Results.NumericResult
PIVOT Results.param;

Hi

[quoted text clipped - 24 lines]