Retrieve Data From 2 x SQL Statements

**DMT Dave** · 06-17-2022, 02:16 AM

Hi Guys, so so close with this, there are 2 things, i cant appear to loop through rs3 recordset where I am trying to count the items delivered between the years (LYearStart And LYearEnd) based on combo

the strSQL2 source is copied from a query SQL view that does display the results, they appear in the immediate window with iDelQty being the same number due to not looping correctly, i cant remember how to do multiple loops !!!!

Also i am trying work the calculation our for Price per item ?

strSQL is getting data from expenses
strSQL2 is getting data from assign (delivered items)

Code:

 strSQL = "SELECT tblExpenses.ItemRequired, tblExpenses.Year, tblExpenses.MonthNo, Sum(tblExpenses.TotalAmount) AS SumOfTotalAmount, Sum(tblExpenses.Litres) AS SumOfLitres, " _        & "Avg(tblExpenses.PricePerLitre) AS AvgOfPricePerLitre " _
        & "From tblExpenses " _
        & "GROUP BY tblExpenses.ItemRequired, tblExpenses.Year, tblExpenses.MonthNo " _
        & "HAVING (((tblExpenses.ItemRequired) = '" & strItem & "') AND ((tblExpenses.Year) Between " & lYearStart & " And " & lYearEnd & ")) " _
        & "ORDER BY Avg(tblExpenses.PricePerLitre) DESC;"
        
    strSQL2 = "SELECT tblAssign.Year, tblAssign.Month, Count(tblAssign.ItemType) AS CountOfItemType, tblAssign.Source " _
        & "From tblAssign " _
        & "GROUP BY tblAssign.Year, tblAssign.Month, tblAssign.Source " _
        & "HAVING (((tblAssign.Year) Between " & lYearStart & " And " & lYearEnd & ") AND ((tblAssign.Source) = '" & strSrc & "')) " _
        & "ORDER BY tblAssign.Year DESC , tblAssign.Month DESC;"




Set rs3 = CurrentDb.OpenRecordset(strSQL2)
    Do While Not rs3.EOF
        iDelQty = rs3("CountOfItemType")
        rs3.MoveNext
    Loop
    
strHTML = "<HTML><Body><table border='3' width='70%'><tr><th>Item Required</th><th>Month-Year</th>" & _
        "<th>Total Fuel Cost</th><th>Total Litres</th><th>Avg Price Per Litre</th><th>Total Items Delivered</th><th>Fuel Cost Per Item</th></tr>"
         
         strMailBody = strHTML
         
Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF
    lMonthNo = rs.Fields("MonthNo")
        iDelQty = DCount("*", "tblAssign", "[Year] Between " & lYearStart & " And " & lYearEnd) ' & " And [MonthNo] = " & lMonthNo & " And [Source] = '" & strSrc & "'")
        
       strMailBody = strMailBody & "<tr><td>" & rs("ItemRequired") & "</td><td>" & _
        MonthName(rs("MonthNo")) & " - " & rs("Year") & "</td><td>" & Format(rs("SumOfTotalAmount"), "Currency") & "</td><td>" & _
        rs("SumOfLitres") & "</td><td>" & Format(rs("AvgOfPRicePerLitre"), "Currency") & "</td><td>" & _
        iDelQty & "</td><td>" & iDelQty / rs("SumOfTotalAmount") & "</td></tr>" & "|"
        rs.MoveNext
    Loop


    Debug.Print strSQL2

iDelQty is not looping

My 2nd mind boggle is iDelQty / rs("SumOfTotalAmount") I think i am not calculating correctly

How do i calculate How much fuel per single item and not the sum of all items

example: Ideally delivery qty is 600 then total fuel cost is 1200, fuel cost per item is what i am trying to return

Not pasted full code here as its large

Any help to retrieve the correct iDelQty would be very kind

**Minty** · 06-17-2022, 04:57 AM

rs3 is simply looping through all of it's values and setting iDelQty to whatever the last value is?

That doesn't seem to be doing what you expect?

Shouldn't it's loop be around the "outside" of the rs loop?

**DMT Dave** · 06-17-2022, 09:40 AM

Hi Minty, thank you if I'm reading correct, I've searched where i have done this before but keep loosing my self!!!

This should be the sequence before i test and crash the db ?

Code:

Set rs3 = CurrentDb.OpenRecordset(strSQL2)
    Do While Not rs3.EOF

Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF

        iDelQty = rs3("CountOfLiftType")
        rs3.MoveNext


    Loop
        strMailBody = strMailBody & "<tr><td>" & rs("ItemRequired") & "</td><td>" & _
        MonthName(rs("MonthNo")) & " - " & rs("Year") & "</td><td>" & Format(rs("SumOfTotalAmount"), "Currency") & "</td><td>" & _
        rs("SumOfLitres") & "</td><td>" & Format(rs("AvgOfPRicePerLitre"), "Currency") & "</td><td>" & _
        iDelQty & "</td><td>" & Format(iDelQty / rs("SumOfTotalAmount"), "Currency") & "</td></tr>" & "|"
        rs.MoveNext
    Loop


    Debug.Print strMailBody

**DMT Dave** · 06-17-2022, 09:41 AM

Can't remember if i ned x 2 loops guessing so because i have 2 x (do) commands

**Minty** · 06-17-2022, 10:26 AM

Your structure is still not correct this is effectively what you currently have

Code:

    
    Set rs3 = CurrentDb.OpenRecordset(strSQL2)
    Do While Not rs3.EOF

           Set rs = CurrentDb.OpenRecordset(strSQL)
           Do While Not rs.EOF

                 iDelQty = rs3("CountOfLiftType")
                 rs3.MoveNext
           Loop 
        Other stuff        
    Loop


    Debug.Print strMailBody

Can you explain what you are trying to do with each record set in simple non database terms.
I can't work out if you want to run each RS3 for each RS or the other way around?

**DMT Dave** · 06-17-2022, 12:38 PM

Thanks Minty, trying to get each year and month rs3 from expenses in the same loop as rs from assign

the results from rs is fine, i am trying add iDelqty to the equation for the same time periods

so rs is looping as all results correct, in the html the iDelqty is all the same (last record in the recordest) so I belive i haven't looped it properly which frustratingly, done multiple loops before and can't remember or find where i done that before

**Welshgasman** · 06-17-2022, 01:15 PM

So rs3 should be within rs. ?
Not sure how you think you are tying the recordsets together?

**DMT Dave** · 06-17-2022, 01:39 PM

Hi WGM and Monty, hoping this will help where i have gone hay wire and lost myself thinking

The Total Items Delivered column is rs3 (iDelQty) (all same values) whereas the rest of the fields are correct, hope this helps you to help me

I have lost my way, tried to come away from and go back to it as a refresh and need fresh eyes to spot the problem

Click image for larger version.

Name: Capture.JPG
Views: 17
Size: 102.4 KB
ID: 48054

**Welshgasman** · 06-17-2022, 02:07 PM

What are you showing us there?
Perhaps put the sql into the sql window and see if the data is correct, before trying to use it.
As it stands as Minty said, you just walk through rs3 setting idelqty with each record until eof, so the logic is all shot to hell.

**Welshgasman** · 06-17-2022, 02:15 PM

Take a step back and think of what you are trying to do.
Think on how you would do it manually, the steps you would take.
That should tell you when you need to find idelqty.
Can it not be included in the other sql via a join, with another query at all?

**ssanfu** · 06-17-2022, 04:15 PM

You must know that "YEAR" and "MONTH" are reserved words AND they are built in functions in Access.....
OK, Off my soap box.

I will be referring to the code posted in Post #1.
The SQL "strSQL2" and the record set "rs3" are irrelevant. All the rs3 loop does is set iDelQty to the value of the last record in "rs3".

However, in the rs loop, the value of iDelQty is the result of a DCOUNT().

Code:

    strSQL = "SELECT tblExpenses.ItemRequired, tblExpenses.Year, tblExpenses.MonthNo, Sum(tblExpenses.TotalAmount) AS SumOfTotalAmount, Sum(tblExpenses.Litres) AS SumOfLitres, " _
             & "Avg(tblExpenses.PricePerLitre) AS AvgOfPricePerLitre " _
             & "From tblExpenses " _
             & "GROUP BY tblExpenses.ItemRequired, tblExpenses.Year, tblExpenses.MonthNo " _
             & "HAVING (((tblExpenses.ItemRequired) = '" & strItem & "') AND ((tblExpenses.Year) Between " & lYearStart & " And " & lYearEnd & ")) " _
             & "ORDER BY Avg(tblExpenses.PricePerLitre) DESC;"

    strSQL2 = "SELECT tblAssign.Year, tblAssign.Month, Count(tblAssign.ItemType) AS CountOfItemType, tblAssign.Source " _
              & "From tblAssign " _
              & "GROUP BY tblAssign.Year, tblAssign.Month, tblAssign.Source " _
              & "HAVING (((tblAssign.Year) Between " & lYearStart & " And " & lYearEnd & ") AND ((tblAssign.Source) = '" & strSrc & "')) " _
              & "ORDER BY tblAssign.Year DESC , tblAssign.Month DESC;"

   Set rs3 = CurrentDb.OpenRecordset(strSQL2)
    Do While Not rs3.EOF
        iDelQty = rs3("CountOfItemType")
        rs3.MoveNext
    Loop

    strHTML = "<HTML><Body><table border='3' width='70%'><tr><th>Item Required</th><th>Month-Year</th>" & _
              "<th>Total Fuel Cost</th><th>Total Litres</th><th>Avg Price Per Litre</th><th>Total Items Delivered</th><th>Fuel Cost Per Item</th></tr>"

    strMailBody = strHTML

    Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF
        lMonthNo = rs.Fields("MonthNo")
        iDelQty = DCount("*", "tblAssign", "[Year] Between " & lYearStart & " And " & lYearEnd)    ' & " And [MonthNo] = " & lMonthNo & " And [Source] = '" & strSrc & "'")

        strMailBody = strMailBody & "<tr><td>" & rs("ItemRequired") & "</td><td>" & _
                      MonthName(rs("MonthNo")) & " - " & rs("Year") & "</td><td>" & Format(rs("SumOfTotalAmount"), "Currency") & "</td><td>" & _
                      rs("SumOfLitres") & "</td><td>" & Format(rs("AvgOfPRicePerLitre"), "Currency") & "</td><td>" & _
                      iDelQty & "</td><td>" & iDelQty / rs("SumOfTotalAmount") & "</td></tr>" & "|"
        rs.MoveNext
    Loop

And, of course the value is the same for every (HTML) record because the criteria in the DCount() was/is commented out.

It would be nice to have an example dB to be able to single step through the code - I would have LOTS of "DEBUG.PRINT" statements in the code to see what is happening.

That's all for now.

**DMT Dave** · 06-18-2022, 12:41 AM

ok guys thank you, will follow up your suggestions, i think now starting point is to wrap the year with square brackets and step through

Retrieve Data From 2 x SQL Statements

Thread Tools

Retrieve Data From 2 x SQL Statements

Similar Threads

Retrieve Data from Query other than Recordsource

What is quicker way to retrieve data

Retrieve a data by using query

using SQL in VBA to retrieve data

Retrieve Data Too Slow

Posting Permissions