MS Access: adding the results of SQL queries 1

[L1sta]

Hi
I'm trying to develop a calculator in an Ms Access form which will calculate the cost of calibrators, QCs and reagents (but not consumables) in storage for a specific study and write to a list box.
My problem is that when I run the query below the results come back as 3 separate values whereas I need one.
I was considering running three separate queries and adding the results but I can't make this work either. Can anyone help??

Lista


Code:
SELECT DISTINCT Sum(tblDetails.Cost) AS SumOfCost, tblStudyNo.StudyProtocolNumber, tblItem.Status, tblDetails.Category
FROM tblStudyNo INNER JOIN (tblDetails INNER JOIN tblItem ON tblDetails.DetailNo = tblItem.DetailsNo) ON tblStudyNo.StudyProtocolNumber = tblDetails.StudyNo
GROUP BY tblStudyNo.StudyProtocolNumber, tblItem.Status, tblDetails.Category
HAVING (((tblStudyNo.StudyProtocolNumber)=[parameter1]) AND ((tblItem.Status)='in storage') AND ((tblDetails.Category)<>"Consumable"));



(i accidently posted this in a pure SQL forum earlier - I figured I may get syntactically accurate results if I reposted here... I'm not spamming ^_^ )

 

[PWise]

Try This

SELECT DISTINCT Sum(tblDetails.Cost) AS SumOfCost, 
FROM tblStudyNo INNER JOIN (tblDetails INNER JOIN tblItem ON tblDetails.DetailNo = tblItem.DetailsNo) ON tblStudyNo.StudyProtocolNumber = tblDetails.StudyNo

Where(((tblStudyNo.StudyProtocolNumber)=[parameter1]) AND ((tblItem.Status)='in storage') AND ((tblDetails.Category)<>"Consumable"));

 

[PHV]

Anyway, the DISTINCT predicate may be removed:
SELECT Sum(D.Cost) AS SumOfCost, S.StudyProtocolNumber
FROM (tblStudyNo AS S
INNER JOIN tblDetails AS D ON S.StudyProtocolNumber = D.StudyNo)
INNER JOIN tblItem AS I ON D.DetailNo = I.DetailsNo
WHERE S.StudyProtocolNumber=[parameter1] AND I.Status='in storage' AND D.Category<>'Consumable'
GROUP BY S.StudyProtocolNumber



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[L1sta]

Thanks for your help guys! It works now!!