quey help please

  • Thread starter misschanda via AccessMonster.com
  • Start date
M

misschanda via AccessMonster.com

Hello,
I ran into a problem in writing my sql.
In my crosstab query I am getting a count of the result instead of the
actual result. Is there a Value or etc to yield actual result instead of
using count.
For example instead of reporting the 2.31 grams for the Result of water, I am
getting a count indicating water was checked or measured.
Also the below query should only return 6 fields and 96 are being returned. I
added Select Distinct.

Any help appreciated


TRANSFORM count(results.numericresult) AS countOfnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, count(results.numericResult) AS [value Of numericResult],
results.Result
FROM (orderdetails INNER JOIN results ON (orderdetails.SampleNumber = results.
SampleNumber) AND (orderdetails.OrderID = results.OrderID)) INNER JOIN
Duke_Samples_Transformer ON (results.SampleNumber = Duke_Samples_Transformer.
SampleNumber) AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
DISTINCT Results.SampleNumber FROM Results WHERE Results.ResultStatus < 3;)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, results.Result
PIVOT [results.param];
 
J

John Spencer

Count does exactly that it COUNTS things. You need to use SUM, Max,
Min, First, or Last to get a value. Which one? I don't know from your
message.

As far as getting more columns than you want that is probably caused by
the PIVOT clause. What is in the field Results.Param? Also, the parens
on that should be [Results].[Param] not [Results.Param].

Also your subquery should not have the semi-colon at the end and you
should always eliminate the possibility for null values to be returned
when using NOT In. If the subquery returns NULL for any field you will
not get the results you expect - every record will be a match.

TRANSFORM Sum(results.numericresult) AS countOfnumericResult
SELECT orderdetails.orderid
, orderdetails.SampleNumber
, orderdetails.Matrix
, Duke_Samples_Transformer.Manufacturer
, results.Qualifier
, results.Units
, results.RepLimit
, results.Result
, Sum(results.numericResult) AS [value Of numericResult]
FROM (orderdetails INNER JOIN results
ON (orderdetails.SampleNumber = results.SampleNumber) AND
(orderdetails.OrderID = results.OrderID))
INNER JOIN Duke_Samples_Transformer
ON (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*")
AND ((orderdetails.OrderDetails_User4) Is Null) AND
((orderdetails.SampleNumber) Not In
(SELECT Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber,
orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, results.Result
PIVOT [results.param];

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,
I ran into a problem in writing my sql.
In my crosstab query I am getting a count of the result instead of the
actual result. Is there a Value or etc to yield actual result instead of
using count.
For example instead of reporting the 2.31 grams for the Result of water, I am
getting a count indicating water was checked or measured.
Also the below query should only return 6 fields and 96 are being returned. I
added Select Distinct.

Any help appreciated


TRANSFORM count(results.numericresult) AS countOfnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, count(results.numericResult) AS [value Of numericResult],
results.Result
FROM (orderdetails INNER JOIN results ON (orderdetails.SampleNumber = results.
SampleNumber) AND (orderdetails.OrderID = results.OrderID)) INNER JOIN
Duke_Samples_Transformer ON (results.SampleNumber = Duke_Samples_Transformer.
SampleNumber) AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
DISTINCT Results.SampleNumber FROM Results WHERE Results.ResultStatus < 3;)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, results.Result
PIVOT [results.param];
Click to expand...
 
K

karl dewey

Sum instead of count. I think your pivot is wrong.
Post sample data for the tables and example of what you want the crosstab
output to look like.
 
M

misschanda via AccessMonster.com

Thanks for the reply.

I am not trying to sum up any of the results but instead present the value
as entered.
It appears as though that once I run the query I am multiplying my results.
so the intended 6 results are yielding 96.
While messing around I removed the units field and the results field and low
and behold got the expected 6 records.
Small victory, but I need those fields.

What do you suggest.?

TRANSFORM Sum(results.numericresult) AS sumofnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
FROM (orderdetails INNER JOIN results ON (orderdetails.OrderID = results.
OrderID) AND (orderdetails.SampleNumber = results.SampleNumber)) INNER JOIN
Duke_Samples_Transformer ON (results.OrderID = Duke_Samples_Transformer.
OrderID) AND (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
PIVOT [results].[param];


John said:
Count does exactly that it COUNTS things. You need to use SUM, Max,
Min, First, or Last to get a value. Which one? I don't know from your
message.

As far as getting more columns than you want that is probably caused by
the PIVOT clause. What is in the field Results.Param? Also, the parens
on that should be [Results].[Param] not [Results.Param].

Also your subquery should not have the semi-colon at the end and you
should always eliminate the possibility for null values to be returned
when using NOT In. If the subquery returns NULL for any field you will
not get the results you expect - every record will be a match.

TRANSFORM Sum(results.numericresult) AS countOfnumericResult
SELECT orderdetails.orderid
, orderdetails.SampleNumber
, orderdetails.Matrix
, Duke_Samples_Transformer.Manufacturer
, results.Qualifier
, results.Units
, results.RepLimit
, results.Result
, Sum(results.numericResult) AS [value Of numericResult]
FROM (orderdetails INNER JOIN results
ON (orderdetails.SampleNumber = results.SampleNumber) AND
(orderdetails.OrderID = results.OrderID))
INNER JOIN Duke_Samples_Transformer
ON (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*")
AND ((orderdetails.OrderDetails_User4) Is Null) AND
((orderdetails.SampleNumber) Not In
(SELECT Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber,
orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, results.Result
PIVOT [results.param];

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Hello,
I ran into a problem in writing my sql.
Click to expand...
[quoted text clipped - 24 lines]
results.RepLimit, results.Result
PIVOT [results.param];
Click to expand...
Click to expand...
 
M

misschanda via AccessMonster.com

Hello the ultimate goal is to have something like this:
Ordered Sampleid manufacturer co2 oxyg nitrogen water
2002 19 some1 .19
2002 12 some2 .55
2002 15 some3 300 315
2009 77 some3 450
2010 21 some3 275

i have revised the sql but am having troubles bc it seems to multiplying my
result. upon inclusion of a unit field and results field. both are presented
below

TRANSFORM Sum(results.numericresult) AS sumofnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
FROM (orderdetails INNER JOIN results ON (orderdetails.OrderID = results.
OrderID) AND (orderdetails.SampleNumber = results.SampleNumber)) INNER JOIN
Duke_Samples_Transformer ON (results.OrderID = Duke_Samples_Transformer.
OrderID) AND (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
PIVOT [results].[param];


ADDING TO EXTRA FIELDS

TRANSFORM Sum(results.numericresult) AS sumofnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit,
results.result, results.Units
FROM (orderdetails INNER JOIN results ON (orderdetails.SampleNumber = results.
SampleNumber) AND (orderdetails.OrderID = results.OrderID)) INNER JOIN
Duke_Samples_Transformer ON (results.SampleNumber = Duke_Samples_Transformer.
SampleNumber) AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit,
results.result, results.Units
PIVOT results.param;


karl said:
Sum instead of count. I think your pivot is wrong.
Post sample data for the tables and example of what you want the crosstab
output to look like.
Hello,
I ran into a problem in writing my sql.
Click to expand...
[quoted text clipped - 24 lines]
results.RepLimit, results.Result
PIVOT [results.param];
Click to expand...
Click to expand...
 
K

karl dewey

You did not post the sample data of your input tables as I asked.
--
KARL DEWEY
Build a little - Test a little


misschanda via AccessMonster.com said:
Hello the ultimate goal is to have something like this:
Ordered Sampleid manufacturer co2 oxyg nitrogen water
2002 19 some1 .19
2002 12 some2 .55
2002 15 some3 300 315
2009 77 some3 450
2010 21 some3 275

i have revised the sql but am having troubles bc it seems to multiplying my
result. upon inclusion of a unit field and results field. both are presented
below

TRANSFORM Sum(results.numericresult) AS sumofnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
FROM (orderdetails INNER JOIN results ON (orderdetails.OrderID = results.
OrderID) AND (orderdetails.SampleNumber = results.SampleNumber)) INNER JOIN
Duke_Samples_Transformer ON (results.OrderID = Duke_Samples_Transformer.
OrderID) AND (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
PIVOT [results].[param];


ADDING TO EXTRA FIELDS

TRANSFORM Sum(results.numericresult) AS sumofnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit,
results.result, results.Units
FROM (orderdetails INNER JOIN results ON (orderdetails.SampleNumber = results.
SampleNumber) AND (orderdetails.OrderID = results.OrderID)) INNER JOIN
Duke_Samples_Transformer ON (results.SampleNumber = Duke_Samples_Transformer.
SampleNumber) AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit,
results.result, results.Units
PIVOT results.param;


karl said:
Sum instead of count. I think your pivot is wrong.
Post sample data for the tables and example of what you want the crosstab
output to look like.
Hello,
I ran into a problem in writing my sql.
Click to expand...
[quoted text clipped - 24 lines]
results.RepLimit, results.Result
PIVOT [results.param];
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Then Use FIRST instead of SUM.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for the reply.

I am not trying to sum up any of the results but instead present the value
as entered.
It appears as though that once I run the query I am multiplying my results.
so the intended 6 results are yielding 96.
While messing around I removed the units field and the results field and low
and behold got the expected 6 records.
Small victory, but I need those fields.

What do you suggest.?

TRANSFORM Sum(results.numericresult) AS sumofnumericResult
SELECT orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
FROM (orderdetails INNER JOIN results ON (orderdetails.OrderID = results.
OrderID) AND (orderdetails.SampleNumber = results.SampleNumber)) INNER JOIN
Duke_Samples_Transformer ON (results.OrderID = Duke_Samples_Transformer.
OrderID) AND (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
WHERE (((orderdetails.Matrix) Like "TRans*") AND ((orderdetails.
OrderDetails_User4) Is Null) AND ((orderdetails.SampleNumber) Not In (SELECT
Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber, orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.RepLimit
PIVOT [results].[param];


John said:
Count does exactly that it COUNTS things. You need to use SUM, Max,
Min, First, or Last to get a value. Which one? I don't know from your
message.

As far as getting more columns than you want that is probably caused by
the PIVOT clause. What is in the field Results.Param? Also, the parens
on that should be [Results].[Param] not [Results.Param].

Also your subquery should not have the semi-colon at the end and you
should always eliminate the possibility for null values to be returned
when using NOT In. If the subquery returns NULL for any field you will
not get the results you expect - every record will be a match.

TRANSFORM Sum(results.numericresult) AS countOfnumericResult
SELECT orderdetails.orderid
, orderdetails.SampleNumber
, orderdetails.Matrix
, Duke_Samples_Transformer.Manufacturer
, results.Qualifier
, results.Units
, results.RepLimit
, results.Result
, Sum(results.numericResult) AS [value Of numericResult]
FROM (orderdetails INNER JOIN results
ON (orderdetails.SampleNumber = results.SampleNumber) AND
(orderdetails.OrderID = results.OrderID))
INNER JOIN Duke_Samples_Transformer
ON (results.SampleNumber = Duke_Samples_Transformer.SampleNumber)
AND (results.OrderID = Duke_Samples_Transformer.OrderID)
WHERE (((orderdetails.Matrix) Like "TRans*")
AND ((orderdetails.OrderDetails_User4) Is Null) AND
((orderdetails.SampleNumber) Not In
(SELECT Results.SampleNumber FROM Results
WHERE Results.ResultStatus < 3 AND SampleNumber is Not Null)))
GROUP BY orderdetails.orderid, orderdetails.SampleNumber,
orderdetails.Matrix,
Duke_Samples_Transformer.Manufacturer, results.Qualifier, results.Units,
results.RepLimit, results.Result
PIVOT [results.param];

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Hello,
I ran into a problem in writing my sql.
Click to expand...
[quoted text clipped - 24 lines]
results.RepLimit, results.Result
PIVOT [results.param];
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

cross tab query help 3
query/transform/crosstab?? 0
Query Format Help 0
Query Speed/Indexes 6
DoCmd Transfer from ACCESS to Excel 1

Top