Query based on a query

[reverendleo]

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!

[Brandoe85]

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.

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

[reverendleo]

Thanks Brandoe!

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

Here's what it ended up being:

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!