SubQuery Issues (Access 2K3)

Topic

New Reply

Hello Access wizards!

I’m having trouble figuring out how to write a two seperate SQL statements as one (with a subquery). I need to accomplish this so I can pull records from an asp site, which doesn’t seem to like referencing queries directly like is allowed in Access.

Here are the two queries:

This first query is called qryTotal_CY…

SELECT tblMain.JobNumber, tblMain.Date, tblSub.LoadCount, tblSub.AvgCYLoad, tblSub!LoadCount*tblSub!AvgCYLoad AS subCY
FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
WHERE (((tblMain.JobNumber)=[Job Number]));

Now, the 2nd query:

SELECT qryTotal_CY.JobNumber, Sum(qryTotal_CY.subCY) AS SumOfsubCY, Max(qryTotal_CY.Date) AS MaxOfDate
FROM qryTotal_CY
GROUP BY qryTotal_CY.JobNumber;

Any ideas are SINCERELY appreciated, and thanks much!

Reply | Quote

Replies

Anybody that was interested:

It turned out pretty simple if I drop some of the fields:

SELECT Sum([LoadCount]*[AvgCYLoad]) AS SumOfCY
FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
WHERE (((tblMain.JobNumber)=[Job Number]));

Thanks again!

Reply | Quote