MS Access: adding the results of SQL queries

[L1sta]

Hi
I'm trying to develop a calculator in Ms Access which will calculate the cost of calibrators, QCs and reagents (but not consumables) in storage for a specific study.
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"));

 

[PWise]

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
and(((tblStudyNo.StudyProtocolNumber)=[parameter1]) AND ((tblItem.Status)='in storage') AND ((tblDetails.Category)<>"Consumable"));

 

[L1sta]

Hi
Thanks for the quick reply but I cant get this to work... lots of syntax errors and 'Join type not supported' messages.
If it matters, I'm using Access 2003

Lista

 

[PWise]

try
[Code Sql]

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

[/code]

 

[SQLSister]

YOu might get a better answer in an Access forum. this forum is for the database Microsoft SQl Server which uses a differnt syntax than Access.

Questions about posting. See faq183-874