Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    May 2005
    Location
    The Lone Star State
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query based on a query

    Hello!

    I'm currently developing a web app. that will pull data from an MS Access database. The original queries were developed in Access, and one query is based on another. I'm trying to replicate this with VB.net. Here are the SQL statements:

    qryMonthbyLoad = SELECT tblMain.Date, tblSub.LoadCount, tblSub.AvgCYLoad, [LoadCount]*[AvgCYLoad] AS Total
    FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
    WHERE (((tblMain.Date) Between [1st Day] And [2nd Day]) AND ((tblSub.LoadCount)>0));

    query2 = SELECT Sum(qryMonthByLoad.Total) AS SumOfTotal
    FROM qryMonthByLoad;

    I'm not quite sure the best way to approach this with VB.net, as it doesn't seem to like selecting from a query.

    Any advice would be great!

    Thanks!

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    You could probably combine the two queries into one, and add another column for the sum, but right now I don't have time to look at it in dept, nor do I have access installed. But you could try just runing the same query but with the sum function.
    Code:
    query2 = SELECT Sum([LoadCount]*[AvgCYLoad]) AS SumOfTotal FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
    WHERE (((tblMain.Date) Between [1st Day] And [2nd Day]) AND ((tblSub.LoadCount)>0));
    Good luck

  • #3
    New Coder
    Join Date
    May 2005
    Location
    The Lone Star State
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Brandoe!

    That works! I needed to modify the SQL to recognize the dates (good 'ole #)

    Here's what it ended up being:

    Code:
    Dim strSQL2 as String = "SELECT Sum([LoadCount]*[AvgCYLoad]) AS SumOfTotal " & _
    							"FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID " & _
    							"WHERE (((tblMain.Date)>=#" & TextDay1.Text & "# And (tblMain.Date)<=#" & TextDay2.Text & "#) AND ((tblSub.LoadCount)>0));"
    You guys are great, always a big help!


  •  

    Posting Permissions

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