VBA Arrays (1 Viewer)

[bjsteyn]

I am needing help with the following code. I am wanting to create an array of accounts using the account number as the identifier, but the account number is to big. I receive an overflow error. Is there any other way of setting up an array so that i can use the account number as the identifier.

Option Compare Database
Option Explicit

Type AccMonthTotals
    Deposits As Currency
    Payments As Currency
    Withdrawals As Currency
End Type
    
Type Account
    'AccNumber As String
    MonthTotals2010(3 To 12) As AccMonthTotals
    MonthTotals2011(1 To 5) As AccMonthTotals
End Type
    
   
Public Sub AdminFees2()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Dim Accounts() As Account
Dim AccNumber As String

Set db = CurrentDb()

strSQL = "SELECT sum([Tran Amount]) AS TranTotal, month([Time]) AS TransMonth, year([Time]) As TransYear, [Acc# No#],[Tran Type] FROM Transactions GROUP BY [Acc# No#],[Tran Type], year([Time]),month([Time])"
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast

    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do While Not rs.EOF
            counter = counter + 1
            AccNumber = rs![Acc# No#]
            If rs!TransYear = "2010" Then
                If rs![Tran Type] = "Deposit" Then
                Accounts(AccNumber).MonthTotals2010(rs![TransMonth]).Deposits = rs![TranTotal]
                ElseIf rs![Tran Type] = "Payment" Then
                Accounts(AccNumber).MonthTotals2010(rs![TransMonth]).Payments = rs![TranTotal]
                ElseIf rs![Tran Type] = "Withdrawal" Then
                Accounts(AccNumber).MonthTotals2010(rs![TransMonth]).Withdrawals = rs![TranTotal]
                End If
                
                
            ElseIf rs!TransYear = "2011" Then
                If rs![Tran Type] = "Deposit" Then
                Accounts(AccNumber).MonthTotals2011(rs![TransMonth]).Deposits = rs![TranTotal]
                ElseIf rs![Tran Type] = "Payment" Then
                Accounts(AccNumber).MonthTotals2011(rs![TransMonth]).Payments = rs![TranTotal]
                ElseIf rs![Tran Type] = "Withdrawal" Then
                Accounts(AccNumber).MonthTotals2011(rs![TransMonth]).Withdrawals = rs![TranTotal]
                End If
               
            End If
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "Complete"
End Sub

 

[stopher]

The problem with an array is that it is a discrete set of indexes. But your account numbers aren’t discrete at all. I image you could have acc no. 12345 but also 123456789. But what about all the indexes in between?

I would create a class for an Account. I would then store multiple accounts (Account objects) in a collection. In this respect an account can be call anything – it really doesn’t matter. So by doing this you can easily access a given account by its account number (and hence all the accounts object methods).

MonthTotals2010(3 to 12) is not a good idea. You will have to keep changing the code for each new month/year. It would be better to design this variable more generic. Maybe:

MonthTotals2010(3 to 12, 2010 to 2013)

But I’ve got to ask why you are wanting to do this? Access is a database and is therefore very good at managing data using queries. Why do you want to accumulate the data in a VBA array?
Chris

 

[bjsteyn]

The problem with an array is that it is a discrete set of indexes. But your account numbers aren’t discrete at all. I image you could have acc no. 12345 but also 123456789. But what about all the indexes in between?

I did note that the indexes in between problem , but this code will only be runned once. (maby again in a years time) and i didn't know of another way to do this.

MonthTotals2010(3 to 12) is not a good idea. You will have to keep changing the code for each new month/year. It would be better to design this variable more generic. Maybe:

MonthTotals2010(3 to 12, 2010 to 2013)

I did do it initially like that, but it is only once off code and thought it whould save memory space.

But I’ve got to ask why you are wanting to do this? Access is a database and is therefore very good at managing data using queries. Why do you want to accumulate the data in a VBA array?
Chris

This is my second attempt at writing this procedure. This first one was using many more queries and was looping threw every account ( about 40000 accounts ). I took about 4hours to loop threw 500 records and that meant it whould take 320hours ( almost 2 weeks ) to process. I presumed that it was because it was running so many queries that it was taking so long. ( 3 x 15 x 40000 = 1.8m queries) so i am trying to get the data structured and into memory with 1 query and am hoping that it will run much quicker when i loop threw the accounts.

I need this data to calculate admin fees for everymonth. If an account has a total of more than R1 at the end of a month , R1 will be deducted as an admin fee. I need the total admin fees for all accounts by month.

Here is my initial procedure:

Option Compare Database
Option Explicit

Public Sub AdminFees()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT DISTINCT [Acc# No#] FROM Transactions"

Set rs = db.OpenRecordset(strSQL)

rs.MoveLast
If rs.RecordCount > 0 Then

    Dim counter As Integer
    Dim x As Integer
    Dim rs2 As DAO.Recordset
    Dim strSQL2 As String
    Dim AdminFee("2010" To "2011", 1 To 12) As Currency
    Dim TempTotal As Currency
    rs.MoveFirst
    counter = 0
    Do While Not rs.EOF
    counter = counter + 1
        
        TempTotal = 0
        
        For x = 3 To 12
            strSQL2 = "SELECT sum([Tran Amount]) AS DepositTotal FROM Transactions WHERE ([Acc# No#]=" & rs![Acc# No#] & ") AND ([Tran Type]='Deposit') AND (month([Time])=" & x & ") AND (year([Time])='2010')"
            Set rs2 = db.OpenRecordset(strSQL2)
            TempTotal = TempTotal + IIf(IsNull(rs2![DepositTotal]), 0, rs2![DepositTotal])
            rs2.Close
            Set rs2 = Nothing
            strSQL2 = "SELECT sum([Tran Amount]) AS PaymentTotal FROM Transactions WHERE [Acc# No#]=" & rs![Acc# No#] & " AND [Tran Type]='Payment' AND month([Time])=" & x & " AND year([Time])='2010'"
            Set rs2 = db.OpenRecordset(strSQL2)
            TempTotal = TempTotal - IIf(IsNull(rs2![PaymentTotal]), 0, rs2![PaymentTotal])
             rs2.Close
            Set rs2 = Nothing
            strSQL2 = "SELECT sum([Tran Amount]) AS WithdrawalTotal FROM Transactions WHERE [Acc# No#]=" & rs![Acc# No#] & " AND [Tran Type]='Withdrawal' AND month([Time])=" & x & " AND year([Time])='2010'"
            Set rs2 = db.OpenRecordset(strSQL2)
            TempTotal = TempTotal - IIf(IsNull(rs2![WithdrawalTotal]), 0, rs2![WithdrawalTotal])
            rs2.Close
            Set rs2 = Nothing
            If TempTotal > 1 Then
            AdminFee("2010", x) = AdminFee("2010", x) + 1
            End If
        Next x
        
        For x = 1 To 5
            strSQL2 = "SELECT sum([Tran Amount]) AS DepositTotal FROM Transactions WHERE [Acc# No#]=" & rs![Acc# No#] & " AND [Tran Type]='Deposit' AND month([Time])=" & x & " AND year([Time])='2011'"
            Set rs2 = db.OpenRecordset(strSQL2)
            TempTotal = TempTotal + IIf(IsNull(rs2![DepositTotal]), 0, rs2![DepositTotal])
            rs2.Close
            Set rs2 = Nothing
            strSQL2 = "SELECT sum([Tran Amount]) AS PaymentTotal FROM Transactions WHERE [Acc# No#]=" & rs![Acc# No#] & " AND [Tran Type]='Payment' AND month([Time])=" & x & " AND year([Time])='2011'"
            Set rs2 = db.OpenRecordset(strSQL2)
            TempTotal = TempTotal - IIf(IsNull(rs2![PaymentTotal]), 0, rs2![PaymentTotal])
            rs2.Close
            Set rs2 = Nothing
            strSQL2 = "SELECT sum([Tran Amount]) AS WithdrawalTotal FROM Transactions WHERE [Acc# No#]=" & rs![Acc# No#] & " AND [Tran Type]='Withdrawal' AND month([Time])=" & x & " AND year([Time])='2011'"
            Set rs2 = db.OpenRecordset(strSQL2)
            TempTotal = TempTotal - IIf(IsNull(rs2![WithdrawalTotal]), 0, rs2![WithdrawalTotal])
            rs2.Close
            Set rs2 = Nothing
            If TempTotal > 1 Then
            AdminFee("2011", x) = AdminFee("2011", x) + 1
            End If
        Next x
        
        rs.MoveNext
    Loop


Else


End If

rs.Close
Set rs = Nothing

strSQL = "SELECT * FROM AdminFees"
Set rs = db.OpenRecordset(strSQL)

For x = 3 To 12
rs.AddNew
rs![MonthF] = x
rs![YearF] = 2010
rs![AdminFeesToDeduct] = AdminFee("2010", x)
rs.Update
Next x

For x = 1 To 5
rs.AddNew
rs![MonthF] = x
rs![YearF] = 2011
rs![AdminFeesToDeduct] = AdminFee("2011", x)
rs.Update
Next x

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "Complete"
End Sub

I would create a class for an Account. I would then store multiple accounts (Account objects) in a collection. In this respect an account can be call anything – it really doesn’t matter. So by doing this you can easily access a given account by its account number (and hence all the accounts object methods).

I have over 40000 accounts. Could you elaborate how i whould do this or maby have a look at my initial procedure and give advice how i can speed it up.

Thanx BJ

 

[spikepl]

This is not optimum code, to say the least. But what you do have can be accelerated substantially with little effort.

1. All fields in WHERE clauses should be indexed. This alone will yield a humongous decrease in processing time
2. For your Time condition you should rewrite the condition like this
SELECT Table.DateField FROM Table WHERE (((Table.DateField) Between Dateserial(MyYear),MyMonth,1) And Dateserial(MyYeay,MyMonth+1,0) ;
In this way, access does not have to find Month(Time) and Year(time) for each record.

If the above is not enough, then queries need to be redesigned to extract the needed info in one go.

 

[bjsteyn]

I have improved my initial procedure by only running 1 query per account and
it is running much quicker, but it is still 40 000 queries. It is processing around a 1000 accounts per 10 minutes which means it should take 400minutes ( 6h 40 min ) to process. Much better than 2 weeks , but feel an array whould speed it up even more. Below is my code:

 Option Compare Database
 Option Explicit
 
 Public Sub AdminFees3()
 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim strSQL As String
 
 Set db = CurrentDb()
 
 strSQL = "SELECT DISTINCT [Acc# No#] FROM Transactions"
 
 Set rs = db.OpenRecordset(strSQL)
 
 rs.MoveLast
 If rs.RecordCount > 0 Then
 
     Dim counter As Integer
     Dim x As Integer
     Dim rs2 As DAO.Recordset
     Dim strSQL2 As String
     Dim AdminFee("2010" To "2011", 1 To 12) As Currency
     Dim TempTotal As Currency
     Dim CurrentMonth As Integer
     
     rs.MoveFirst
     counter = 0
     Do While Not rs.EOF
     counter = counter + 1
         
         TempTotal = 0
         
         strSQL2 = "SELECT sum([Tran Amount]) AS TranTotal, month([Time])  AS TransMonth, year([Time]) As TransYear, [Tran Type] FROM Transactions  WHERE [Acc# No#] =" & rs![Acc# No#] & " GROUP BY  year([Time]),month([Time]),[Tran Type]"
         Set rs2 = db.OpenRecordset(strSQL2)
         rs2.MoveLast
         If rs2.RecordCount > 0 Then
             rs2.MoveFirst
             Do While Not rs2.EOF
                  
                  If IsNull(CurrentMonth) Then
                  CurrentMonth = rs2![TransMonth]
                  End If
                  
                  If Not (rs2![TransMonth] = CurrentMonth) Then
                     If TempTotal > 1 Then
                     AdminFee("2010", CurrentMonth) = AdminFee("2010", CurrentMonth) + 1
                     TempTotal = TempTotal - 1
                     End If
                     CurrentMonth = rs2![TransMonth]
                  End If
                  
                  If rs2![Tran Type] = "Deposit" Then
                  TempTotal = TempTotal + IIf(IsNull(rs2![TranTotal]), 0, rs2![TranTotal])
                  ElseIf rs2![Tran Type] = "Payment" Or rs2![Tran Type] = "Withdrawal" Then
                  TempTotal = TempTotal - IIf(IsNull(rs2![TranTotal]), 0, rs2![TranTotal])
                  End If
                                 
                  rs2.MoveNext
                 If rs2.EOF Then
                    If TempTotal > 1 Then
                    AdminFee("2010", CurrentMonth) = AdminFee("2010", CurrentMonth) + 1
                    TempTotal = TempTotal - 1
                    End If
                 End If
             Loop
         End If
         rs2.Close
         Set rs2 = Nothing
         
 
             
         rs.MoveNext
     Loop
 
 
 Else
 
 
 End If
 
 rs.Close
 Set rs = Nothing
 
 strSQL = "SELECT * FROM AdminFees"
 Set rs = db.OpenRecordset(strSQL)
 
 For x = 3 To 12
 rs.AddNew
 rs![MonthF] = x
 rs![YearF] = 2010
 rs![AdminFees] = AdminFee("2010", x)
 rs.Update
 Next x
 
 For x = 1 To 5
 rs.AddNew
 rs![MonthF] = x
 rs![YearF] = 2011
 rs![AdminFees] = AdminFee("2011", x)
 rs.Update
 Next x
 
 rs.Close
 Set rs = Nothing
 Set db = Nothing
 
 MsgBox "Complete"
 End Sub

This is not optimum code, to say the least. But what you do have can be accelerated substantially with little effort.

1. All fields in WHERE clauses should be indexed. This alone will yield a humongous decrease in processing time
.

Thanx for the advice. Didn't know that indexing had such a great effect.

2. For your Time condition you should rewrite the condition like this
SELECT Table.DateField FROM Table WHERE (((Table.DateField) Between Dateserial(MyYear),MyMonth,1) And Dateserial(MyYeay,MyMonth+1,0) ;
In this way, access does not have to find Month(Time) and Year(time) for each record.

.

Thanx will apply it next time as in my new query i am currently just grouping using the month and year functions.

BJ

 

[spikepl]

The lot should be rewritten anyway. Access has much better algorithms to find specifc records than you can do by stepping through records yourself in arrays.

Your current focus is still seemingly mired in Month and Year: You should operate on dates in one query that extracts desired sum or count in one go, for each month in a period from date1 to date2.

If you insist on doing all this in code, at least make strSql2 grab data only for the desired period and not all data.

 

[stopher]

This whole thing can be done using only a couple of queries I'm sure. You definately should not be running 40,000 queries. As spikepl says, you should not be looping through yourself. Just write queries to do the summing for you. There's no need to write any VBA code at all.

The problem is I'm trying to grasp exactly your requirements as it's buried in code at the moment.

Maybe you could just describe the process in words.

Chris

 

[bjsteyn]

Your current focus is still seemingly mired in Month and Year: You should operate on dates in one query that extracts desired sum or count in one go, for each month in a period from date1 to date2.

If you insist on doing all this in code, at least make strSql2 grab data only for the desired period and not all data.

If i had to this for every account then i whould be back to (15 x 40000) 600 000 queries. I presume you mean i must grab the data for a specific date range for all accounts. This whould take me to 15 queries (15 months). How whould i loop threw it though, as i have to loop threw every account and then to get the data from those queries i whould have to loop threw each of the 15 queries aswell (inside my account loop) until i reach the the depositstotal record, paymentstotal record and withdrawaltotal record for current account in first loop.

Just to make 120% clear on what i am trying to calculate, we have no transaction log of the admin fees that where deducted over the last 15 months, but have deposits, withdrawals and payments and are trying to calculate what was the total admin fees was for every month.

If an account had more than R1 in it at the end of a month, R1 whould be deducted. By looping threw every account and calculating what the total in the account was at the end of every month, we can calculate total deducted admin fees on all accounts for every month.

 

[bjsteyn]

This whole thing can be done using only a couple of queries I'm sure. You definately should not be running 40,000 queries. As spikepl says, you should not be looping through yourself. Just write queries to do the summing for you. There's no need to write any VBA code at all.

The problem is I'm trying to grasp exactly your requirements as it's buried in code at the moment.

Maybe you could just describe the process in words.

My above comment should explain what i am trying to do and I can not see how it can be done without code.

Thanx Stopher and Spikepl for your help.
I appreciate it.
BJ

 

[spikepl]

The issue here is you algorithm of doing stuff separately for each account and for each month and year. That is what causes you to do all these quereis and quiereis.

I do not quite see the necessity. What is R1? I could not see it in your code.

Can you give an example, just covering a few transactions for one account for one month? Using the fields in your table. I am no exactly clear on what you need, just like Stopher above. Remember we know NOTHING !

 

[stopher]

If I understand correctly, you want to see count how many accounts have a positive balance at the end of each month. And say 60 accounts have a positive balance for the end of a given month then you know your admin fee is 60 x admin fee?

Are we to assume the balance was zero at the beginning of March 2012? Or does it start from zero and the beginning of each month i.e. simply a sum of the transactions for that month?

 

[bjsteyn]

What is R1?

R1 is plain old 1 rand ( not a variable ) .

the recordset 1 loop is looping threw accounts

for every account that had a balance of more than 1 rand at the end of a month , 1 rand was deducted as an admin fee for that month. I need the total adminfees that was deducted for all accounts by month.

my second recordset is used for the following:
I am basically starting the account at R0 and by adding deposits and subtracting payments and withdrawals for the starting month March 2010 i can calculate what the total in the account was at the end of the month. If more than R1 then "Admin Fees For All Accounts For March 2010" = +1 , thus R1 must be deducted from the account total (up unitl March end), now i move on to the next month and add deposits and subtract payments and withdrawals to get the month end total in the account in April 2010. If account had more than R1 in it at the end of April 2010 then R1 was deducted, thus "Admin Fees For All Accounts For April 2010" = +1 . Now deduct Admin fee from Account Total ( up until April end) = -1. Now to next month. TempTotal is used in code store the total in the account as the loop is going threw the months.

hope it explains it a bit.

 

[bjsteyn]

If I understand correctly, you want to see count how many accounts have a positive balance at the end of each month. And say 60 accounts have a positive balance for the end of a given month then you know your admin fee is 60 x admin fee?

Yes They need R1 in it. That means R1 was deducted as an admin fee.

Are we to assume the balance was zero at the beginning of March 2012? Or does it start from zero and the beginning of each month i.e. simply a sum of the transactions for that month?

Some accounts were only created later on. Balance is 0 as soon as it is created and then all transactions from there on either a credit or a debit. What is left from the one month is carried over to the next month.

 

[spikepl]

so your objective is to perform the deduction and that's it? Or to calculate the amount of deductions that WERE made in total for the period in question (march to whenever)?

 

[bjsteyn]

so your objective is to perform the deduction and that's it? Or to calculate the amount of deductions that WERE made in total for the period in question (march to whenever)?

I need to know how many accounts had an admin fee deducted for every month or (same thing) what was the total admin fees for every month on all accounts.

To get this i need to know what was the balance in every account at every month end.

 

[spikepl]

Ok one more Q: The data in the tables already include balance after the actual withdrawal of the admin fee ?

 

[bjsteyn]

Ok one more Q: The data in the tables already include balance after the actual withdrawal of the admin fee ?

I am using two accounts to demostrate the data:

The Transactions table looks something like this:

Acc# No# Time Tran Type Tran Amount
30001312321 2010/03/09 09:00 Deposit 50.00
30009999999 2010/03/15 09:00 Deposit 100.00
30009999999 2010/03/16 09:00 Purchase 20.00
30001312321 2010/03/17 09:00 Withdrawal 50.00

The data in the query i am using for recordset 2 looks something like this. data pulled is for the current account in the loop. Lets say it is 30001312321. TranTotal is the total deposits, withdrawals or payments for the month.
TranYear TranMonth Tran Type TranTotal
2010 3 Deposit 50.00
2010 3 Withdrawal 50.00
2010 4 Deposit 50.00
2010 5 Deposit 200.00
2010 5 Payment 150.00
2010 5 Withdrawal 50.00

There is no records on admin fees and I need to calculate the admin fees other the past 15 months.

I believe i have found a way to use only 1 query and 1 recordset and then loop threw it. If i pull the data in this format, i will be able to loop threw the accounts and calculate the month end totals and i whould know when i am at a new month and new account in the loop by using variables to store the account and month in previous loop. I need to know when i am at a new account to reset TempTotal to 0 and I need to know when i am at the end of a month to calculate if a admin fee was deducted and -1 from TempTotal and increment Adminfees(year,month) with 1.
Acc# No# TranYear TranMonth Tran Type TranTotal
30001312321 2010 3 Deposit 50.00
30001312321 2010 3 Withdrawal 50.00
30001312321 2010 4 Deposit 50.00
30001312321 2010 5 Deposit 200.00
30001312321 2010 5 Payment 150.00
30001312321 2010 5 Withdrawal 50.00
30009999999 2010 3 Deposit 100.00
30009999999 2010 5 Payment 100.00

 

[bjsteyn]

The code looks like this:

Option Compare Database
Option Explicit
 
Public Sub AdminFees3()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim AdminFee("2010" To "2011", 1 To 12) As Currency
Dim AccountBalance As Currency
Dim MonthInLastLoop As Integer
Dim AccountInLastLoop As Double
Set db = CurrentDb()
 
strSQL = "SELECT [Acc# No#] , year([Time]) As TransYear, month([Time]) AS TransMonth, , [Tran Type], sum([Tran Amount]) AS TranTotal FROM Transactions GROUP BY [Acc# No#] ,year([Time]),month([Time]),[Tran Type]"
Set rs = db.OpenRecordset(strSQL2)
 
rs.MoveLast
If rs.RecordCount > 0 Then
[INDENT]rs.MoveFirst
Do While Not rs.EOF
[INDENT]If IsNull(AccountInLastLoop) then
[INDENT]AccountBalance = 0
AccountInLastLoop=rs![Acc# No#]
[/INDENT]End If 
If IsNull(MonthInLastLoop) Then
[INDENT]MonthInLastLoop = rs![TransMonth]
[/INDENT]End If
If Not (rs![Acc# No#] = AccountInLastLoop) Then
[INDENT]If AccountBalance > 1 Then
[INDENT]AdminFee("2010", MonthInLastLoop) = AdminFee("2010", MonthInLastLoop) + 1
[/INDENT]end if
AccountBalance = 0
AccountInLastLoop = rs![Acc# No#]
[/INDENT]else
[INDENT]If Not (rs![TransMonth] = MonthInLastLoop) Then
[INDENT]If AccountBalance > 1 Then
[INDENT]AdminFee("2010", MonthInLastLoop) = AdminFee ("2010",MonthInLastLoop) + 1
AccountBalance = AccountBalance - 1
[/INDENT]End If
[/INDENT]End If
[/INDENT]end if
If rs![Tran Type] = "Deposit" Then
[INDENT]AccountBalance = AccountBalance + IIf(IsNull(rs![TranTotal]), 0, rs! [TranTotal])
[/INDENT]ElseIf rs![Tran Type] = "Payment" Or rs![Tran Type] = "Withdrawal" Then
[INDENT]AccountBalance = AccountBalance - IIf(IsNull(rs![TranTotal]), 0, rs! [TranTotal])
[/INDENT]End If
If rs.EOF Then
[INDENT]If AccountBalance > 1 Then
[INDENT]AdminFee("2010", MonthInLastLoop) = 
AdminFee("2010", MonthInLastLoop) + 1
[/INDENT]end if
[/INDENT]End If
rs.MoveNext
[/INDENT]loop
[/INDENT]End If
rs.Close
Set rs = Nothing

Will test it on Monday, as i wrote this at home and the db is on my work pc.

 

[spikepl]

I notice you have a "Purchase" - that does not count in calculating the account balance?

I am stuck in some serious debugging of my own stuff, so if anyone wants to chime-in go ahead. I might have time over the week-end (depending on the bugs).

It seems to me a bunch of subqueries should do the trick. The painful part is to actually deduct the fee prior to continuing next month.

 

[stopher]

I've attached a example that uses just two queries (although you have to run the queries for each required month). You only need to run the second query (qryCountPosAccounts).

The first query (qryListBalances) creates a balance for a given month for all accounts.

The second query (qryCountPosAccounts) simple counts the number of accounts with a balance >1 that the first query generates and appends the count to the AdminFees table along with the Year/Month reference.

So running the second query is all that is needed to populate the AdminFees table with a record for a given year.

The code (Test) in module 1 is used to loop through the required months and run the update query.

Note that because the first query needs parameters passed to is, I have use query definitions in VBA to pass the parameters.

I stress tested the code and queries with
40,000 accounts with 10 records per account per month over 15 months i.e. 6 million records.

It took about 10 minutes to populate 15 months.

hth
Chris