Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubQuery Issues (Access 2K3)

    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!

  2. #2
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubQuery Issues (Access 2K3)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •