Help with Sub Query

[PNGBill]

Hi, I am trying to Sum a field with criteria and can't get one query to do this.

These two will do the job.

This selects the Records.

SELECT TBLTRANS.TRNACTDTE, TBLTRANS.TRNTYP, TBLTRANS.TRNDR
FROM TBLTRANS
WHERE (((TBLTRANS.TRNACTDTE)<=Date()) AND ((TBLTRANS.TRNTYP)="Interest")) OR (((TBLTRANS.TRNTYP)="Late fee")) OR (((TBLTRANS.TRNTYP)="Legal Fees"));

And this Sums the first.

SELECT Sum(QueryTesta.TRNDR) AS SumOfTRNDR
FROM QueryTesta;

Is there a way to combine these two (maybe subquery) so that it is just one string ?

I want to then use this sql string in a VBA Function to get the Sum result.

This will replace:

Public Function FunctionCurrentBalanceAllSum()

    Dim SumOverDue As Currency
    
        'Sum Late fee, Legal Fees, Interest from TBLTRANS
      SumOverDue = DSum("TRNDR", "TBLTRANS", "TRNACTDTE<=Date() AND TRNTYP In('Interest', 'Late fee', 'Legal Fees')")
      
        'Sum Principal from TBLTRANS & Add to SumOverDue
      SumOverDue = SumOverDue + DSum("TRNPR", "TBLTRANS", "TRNACTDTE<=Date() AND TRNTYP In('Interest')")
      
        'Sum PaymentsAmt from tblMemberRepayments and subtract to SumOverDue
      SumOverDue = SumOverDue - GetRecordSums("RepaymentsAll")
      
        'Give Result of SumOverDue to Function CurrentBalanceAllSum
      FunctionCurrentBalanceAllSum = SumOverDue
      

End Function

The sql will hopefully get me up to a value that I can then subtract "GetRecordSums("RepayMentsAll") to arrive at the final result.

Appreciate any advice.

 

[vbaInet]

Something like this:

SELECT Sum(QueryTesta.TRNDR) AS SumOfTRNDR
FROM (SELECT TBLTRANS.TRNACTDTE, TBLTRANS.TRNTYP, TBLTRANS.TRNDR
FROM TBLTRANS
WHERE (((TBLTRANS.TRNACTDTE)<=Date()) AND ((TBLTRANS.TRNTYP)="Interest")) OR (((TBLTRANS.TRNTYP)="Late fee")) OR (((TBLTRANS.TRNTYP)="Legal Fees"))) AS QueryTesta;

 

[PNGBill]

Perfect. Thanks vbaInet.

I was doing it back to front with the short sql as the subquery. Plus didn't really know what I was doing.

Have converted it to vba sqlString and will try and get it to replace the DSum() used in my older function.

 

[vbaInet]

No problemo.

 

[PNGBill]

Here is the finished and working Code.

Much longer the few lines of DSum() but now all sql.

Public Function FunctionCurrentBalanceAllSum() As Currency

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim SumOverDue As Currency
    
        SumOverDue = 0
    
        sqlString = "SELECT Sum(SubQryRecords.TRNDR) AS SumOfTRNDR " & vbCrLf & _
            "FROM (SELECT TBLTRANS.TRNACTDTE, TBLTRANS.TRNTYP, TBLTRANS.TRNDR " & vbCrLf & _
            "FROM TBLTRANS " & vbCrLf & _
            "WHERE (((TBLTRANS.TRNACTDTE)<=Date()) AND ((TBLTRANS.TRNTYP)=""Interest"")) OR (((TBLTRANS.TRNTYP)=""Late fee"")) OR (((TBLTRANS.TRNTYP)=""Legal Fees""))) AS SubQryRecords;"
    
             'Open Recordset
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
    
        SumOverDue = rst!SumOfTRNDR
        
         'Close database variables
    rst.Close
    dbs.Close
        
        sqlString = "SELECT Sum(SubQryRecords.TRNPR) AS SumOfTRNPR " & vbCrLf & _
            "FROM (SELECT TBLTRANS.TRNACTDTE, TBLTRANS.TRNTYP, TBLTRANS.TRNPR " & vbCrLf & _
            "FROM TBLTRANS " & vbCrLf & _
            "WHERE (((TBLTRANS.TRNACTDTE)<=Date()) AND ((TBLTRANS.TRNTYP)=""Interest""))) AS SubQryRecords;"
    
             'Open Recordset
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
        
        SumOverDue = SumOverDue + rst!SumOfTRNPR
        
        SumOverDue = SumOverDue - GetRecordSums("RepaymentsAll")
      
      
        'Give Result of SumOverDue to Function CurrentBalanceAllSum
        FunctionCurrentBalanceAllSum = SumOverDue
      
    
    
    'Close database variables
    rst.Close
    dbs.Close
      

End Function

 

[vbaInet]

Looking good.

 

[LPurvis]

Just to mention - there should't be any need for a subquery.
The overhead added by it won't be substantial - but if it's not needed you might as well dispense with it.
Your query can simply be:

SELECT Sum(TRNDR) AS SumOfTRNDR
FROM TBLTRANS
WHERE (TRNACTDTE<=Date() AND TRNTYP="Interest") OR TRNTYP In ("Late fee", "Legal Fees")

Just to be clear though - you want the date less than today only if TRNTYP is "Interest", as well as all records where TRNTYP is "Late fee" or "Legal Fees"?
(That's what the original queries were requesting).

It's worth mentioning that, in your code, there's no need to close the database object (dbs.Close) and therefore no need to reassign it. Using the same dbs object throughout your procedure is fine and preferable.

Cheers.

 

[PNGBill]

Thanks LPurvis,
I assumed date criteria covered all three TRNTYP options but only Interest would have dates greater then today so result should still be as expected.

Will edit code tomorrow.

 

[LPurvis]

Well, if you're making assumptions about the entered data - that could change unless you have rules in place to prevent it.
Ultimately - request what you absolutely know you want.
If the date criteria should apply to all three, then it's simpler still to do.

SELECT Sum(TRNDR) AS SumOfTRNDR
FROM TBLTRANS
WHERE TRNACTDTE<=Date() AND TRNTYP In ("Interest", "Late fee", "Legal Fees")

You could always have a separate query to look for records violating what you believe should be the case. i.e.

SELECT Sum(TRNDR) AS SumOfTRNDR
FROM TBLTRANS
WHERE TRNACTDTE>Date() AND TRNTYP In ("Late fee", "Legal Fees")

to look for problem records.

Cheers.

 

[PNGBill]

I assumed all along the date criteria covered all of the Or Criteria.
Sql not my strong point. Too used to design view.

As it happens, Late fee and Legal Fees are just charged on the day so can only have a date of today or less then today.
Interest is Forward Booked so appears in TBLTRANS for all occurrences of the loan option (number of fortnights) and can be expected to have future records which we want excluded from the result.

Sql just getting simpler as we go.

I will try and build in the error sql

Thanks

 

[PNGBill]

Replaced sqlString with vba version using LPurvis suggestion and same result appears - Great.

Remarked out the rst.Close & dbs.Close after first SumOverDue = rst!SumOfTRNDR

All works fine.

When I remarked out the subsequent Set dbs and Set rst following the 2nd sql, it all turned to custard

I can remark the Set dbs but the Set rst = dbs must be there or all I get is an error message.

 

[PNGBill]

Here is the Procedure with shortened sql but still Set rst repeated.
Procedure works fine.

Public Function FunctionCurrentBalanceAllSum() As Currency

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim SumOverDue As Currency
    
        SumOverDue = 0      ' set Variable to Zero
    
            'sql query with subquery to Sum TBLTRANS.TRNDR for Interest, Late fee & Legal Fees
       sqlString = "SELECT Sum(TRNDR) AS SumOfTRNDR " & vbCrLf & _
            "FROM TBLTRANS " & vbCrLf & _
            "WHERE TRNACTDTE<=Date() AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"");"
        
             'Open Recordset
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
    
        SumOverDue = rst!SumOfTRNDR 'Put Result of sql as Variable SumOverDue
        
         'Close database variables
        'rst.Close
        'dbs.Close
        
            'sql query with subquery to Sum TBLTRANS.TRNPR for Principal - Includes Application and Processing Fees
       sqlString = "SELECT Sum(TRNPR) AS SumOfTRNPR " & vbCrLf & _
            "FROM TBLTRANS " & vbCrLf & _
            "WHERE TRNACTDTE<=Date() AND TRNTYP In (""Interest"");"
    
             'Open Recordset
        'Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset(sqlString)
        
        SumOverDue = SumOverDue + rst!SumOfTRNPR    'Add result of 2nd sql to Variable
        
        SumOverDue = SumOverDue - GetRecordSums("RepaymentsAll")        'Deduct Repayments Made with Function
      
      
        'Give Result of SumOverDue to Function CurrentBalanceAllSum
        FunctionCurrentBalanceAllSum = SumOverDue
      
    
    
    'Close database variables
    rst.Close
    dbs.Close
      

End Function

 

[LPurvis]

>> I can remark the Set dbs but the Set rst = dbs must be there or all I get is an error message.

Absolutely. Removing the second recordset opening line wasn't the intention.
You need to open that recordset, as it's different from the first.
However the database object is the same.
You'll not notice performance differences unless you have fairly intense, repetative, processes - but it makes for more appropriate code using objects frugally.

Presumably TRNACTDTE and TRNTYP are indexed in your table?
That would appear to be a good choice, given their fundamental use in criteria.

Cheers.