Search results

I have attached a db with my code module. Thanx BJ

What i meant with the array is that I believed if I could load/stucture the data into an array and then loop threw it whould run faster than 400000 queries, as i could not get the data structured using just 1 query ( i thought). I have 1 query know and am looping threw it but my values are...

Purchases are "Payments". It is in my code: :) :) :) If rs![Tran Type] = "Deposit" Then AccountBalance = AccountBalance + rs![TranTotal] ElseIf rs![Tran Type] = "Payment" Or rs![Tran Type] = "Withdrawal" Then AccountBalance = AccountBalance - rs![TranTotal]...

Thanx Chris, i have tested it and it only takes about a minute to run threw the 270000 transactions. The only problem, although it might be small, but is needed for accuracy is that R1 needs to be deducted from the AccountBalance for a month if it was counted as an Admin Fee Deducted. Any idee...

Purchases are there, you probably just mist it. :eek:

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...

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...

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.

Yes :cool: They need R1 in it. That means R1 was deducted as an admin fee. 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.

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...

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

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...

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 :D, but...

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. I did do it initially like that, but it is only once off code and thought it whould save memory space. ;) This is my second...

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...

Hey Nigel, i sent you an email. Thanx BJ

I am wanting to sell the developer rights to an order processing system I have developed in ms access. It is a complete software solution for distributors doing rep and delivery based sales. The POS system handles the following: Quotes Orders Customers Products Price Lists Stock Management...

Hey DCrake Still awaiting your reply. Thanx BJ

Can you elaborate. By IPR i presume you mean Intellectual Property Rights. This is my first major program I have worked on. The program has some features dedicated to Nestle, but can be removed for other distributors. The whole PFD franchise idea was started by someone working for Nestle...

Hi DCrake I am wanting to sell the development rights. I started working on the program for a food distributor franchise group that opened in SA that are dedicated to Nestle products distributing. They would have baught the program with developer rights by me for around R30000 ZAR. ( $4000 )...