By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,480 Members | 1,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,480 IT Pros & Developers. It's quick & easy.

Update fields based on calculation

P: 16
I am new to programming using VBA code. I am involved in developing a database. I created most of the tables and queries. Now I am at the complicated part of implementing some calculations, so I decided to use VBA code. This is what I have.


I have two tables Tbl1 and Tbl2.
Tbl 1 has the following fields
Account Balance Set Date

Tbl2 has the following fields,
Account sumofbalance Set Bal Overstated Balance


IThe overstated balance is to be deducted from the balance field having the same account numbers starting with the most recent date to attain the new balance. This should be done for all the balances in the same account starting from the most recent date until the overstated balance gets exhausted.

Eg
If the overstated amount is 200, the 100 is deducted from the most recent balance to make it zero. The remaining 100 of the overstated balance is deducted from the next recent balance and so on till the overstated balance gets exhausted.
Expand|Select|Wrap|Line Numbers
  1. Account    Balance    Set Date    New Bal(Overstated Balance - Balance)
  2. 12387654    100    28-Jul    0
  3. 12387654    600    27-Jul    500
  4. 12387654    300    26-Jul    300
  5. 12387654    100    25-Jul    100
I know that we can use the recordset command to move through records. Can I use the recordset method to update the new balance field in table 1 based on the logic. Can anyone help me on this?
Aug 20 '08 #1
Share this Question
Share on Google+
25 Replies


NeoPa
Expert Mod 15k+
P: 31,709
This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.
Table Name=[tblStudent]
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
You use field names with spaces in so this is unclear at the moment.

I believe Recordset processing can do what you need. Whether what you're trying to do is a good idea is unclear without a better understanding of exactly what it is you are trying to do.
Aug 20 '08 #2

P: 16
Thanks for your help, I appreciate that.

Table Name=[tblColl]
Expand|Select|Wrap|Line Numbers
  1. Field;  Type;  IndexInfo
  2. Account; Text; PK
  3. Balance; Numeric;
  4. [Set Date]; Date/Time
  5. [New Balance]; Numeric
  6. Valid; Boolean
Table Name=[TblCalc]
Expand|Select|Wrap|Line Numbers
  1. Field;  Type;  IndexInfo
  2. Account; Text; PK
  3. SumofBalance; Numeric
  4. [Set Bal]; Numeric
  5. [Overstated Balance]; Numeric
[TblColl]
Expand|Select|Wrap|Line Numbers
  1. [Account]  [Balance]  [Set Date]  [New Balance]  [Valid]
  2. 12387654     $100.00   28-Jul-08       Null       False
  3. 12387654     $600.00   27-Jul-08       Null       False
  4. 12387654     $300.00   26-Jul-08       Null       False
  5. 12387654     $100.00   25-Jul-08       Null       False
  6. 56789526   $2,300.00   28-Jul-08       Null       False
This is the master table. I have to add two more fields([New Balance];type=Numeric and [valid];type=Yes/No) to this table based on validations using this table and the other table.

[TblCalc]
Expand|Select|Wrap|Line Numbers
  1. [Account]  [SumOfBalance]  [Set Bal]  [Overstated Bal]
  2. 12387654        1100          900           200
  3. 56789526        2300         2300             0
This is a table I created using a make table query.

Validation required:

For each account number in the tblcoll, where the account field is equal to the account of tblcalc. If the sumofbalance<=[set bal], then all the items are considered valid and the [new balance] field(which has to be created) of the table tblcoll is the same as the balance field, or ([new balance]=[balance]).

But if the [sumofbalance]>[set bal], then for each of the [account] fields of the Tblcoll where Tblcoll.[account] = Tblcalc.[account], the [overstated balance] of the tblcalc field is subtracted from the [balance] field of the tblcoll, starting from the most recent date. This is done till the calculation brings the balance to zero. And this is done only for the accounts with the same account number.

For instance In the example given above for the Tblcoll, the sum of balance for the same account number(12387654) comes to 1100(100,600,300,100) which is shown in the Tblcalc.[sumofbalance] field. The [overstated bal] field in the Tblcalc is obtained by subtracting the [set bal] from the [sumofbalance] field(1100-900=200). Since the [sumofbalance]>[set bal], the [overstated bal] is deducted from each of the [balance] from the Tblcoll table with the same [account] number as the Tblcalc table starting with the nost recent date( here 28-Jul-08). Till the [overstated bal] gets exhausted.

This is the validation that I am trying to do,
Expand|Select|Wrap|Line Numbers
  1. [Account]  [Balance]  [Set Date] [New Balance]
  2. 12387654      $100     28-Jul-08    0
  3. 12387654      $600     27-Jul-08  500
  4. 12387654      $300     26-Jul-08  300
  5. 12387654      $100     25-Jul-08  100
  6. 56789526    $2,300     28-Jul-08    0
In the first row for the most recent date(28-Jul-08) for the account 12387654 , the [New balance] field is found by deducting the [overstated bal] of Tblcalc table from the [Balance] field of the Tblcoll with the same account. So here the [Overstated bal] of 200 for the account 12387654, 100 is expended to bring the new balance to zero.

In the next row, for the next recent date for the same account, the remaining amount 100 left in the [Overstated bal] is deducted from the [balance] of 600 to bring it to 500. This leaves the [Overstated bal] completely expended and left with 0 value.

Hence for the next rows with the same account, the new balance is same as the balance.

This validation is actually done to bring the sum of the [new balance]=[set bal](900 here)

For the account, where the sumofbalance<=[set bal] like the other account 56789526, the [New balance] is same as the [balance] field.


For other field [Valid] to be added in the table Tblcoll, the validation is done as follows,

? Any calculation the brings the [new balance] field value to zero has to be marked invalid and the others are valid.

I hope this makes it clearer than before.
Aug 20 '08 #3

NeoPa
Expert Mod 15k+
P: 31,709
I will see if I can find some time to look at this later. I've taken up so much time reformatting your post such that it's clearer to read (very hard to even start on otherwise) that I've run short.

I was rather hoping you'd do this bit, but I appreciate that at least you've shown some effort to try to explain the situation in great detail anyway. If it's not simple, that's down to the problem rather than the explanation.
Aug 20 '08 #4

P: 16
I apologize for the messup with my first post. Thank you for taking your precious time to look into this.
Aug 21 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi. While NeoPa is considering your post can I ask why you are not using Excel for this particular application? The fact that your tables comprise little more than various forms of balance totals is a give-away that a database is not the best solution. Excel is an excellent tool for performing the kind of calculation you need to do - it is after all primarily a financial tool built for just such calculations - whereas relational databases are best at handling thousands of rows of complex interrelated data from multiple tables that implement relationships between different business objects.

It is the norm in databases to compute balances from source transaction data, not to store these balances as part of an overarching table. It suggests to me that the way you have designed your tables is more akin to what you would see in an Excel sheet than to the use of table normalisation methods for database design.

You would be able to implement the calculation rules you describe in Excel in under an hour's work; I'm not convinced that you can say the same for trying this in Access, nor that you will benefit from using Access at all, unless the underlying data is way more complex than the tables as you have listed them in post # 3.

-Stewart
Aug 21 '08 #6

P: 16
Hi Stewart,

Thanks for your help. This cannot be implemented using the excel because there are over thousands of rows of complex data. The master table I mentioned in my post#3 is infact made by running a make table query I made by combining two tables using union. Actually the data is imported from text files which in turn are obtained by screen scrapping from Mainframe. I dont think we can implement this functions using excel because of the large amount of data present in this table.

-Arthur
Aug 21 '08 #7

Expert Mod 2.5K+
P: 2,545
Hi. Understood, but in case it helps Excel can deal with 65000+ rows in any one worksheet (2^16 or thereby), and up to 255 columns. You can have as many worksheets per workbook as available memory allows as well.

-Stewart
Aug 21 '08 #8

NeoPa
Expert Mod 15k+
P: 31,709
Thanks for trying anyway Stewart.

Arthur. I'm not ignoring this but something has come up tonight, so I will have to defer work on this for a while. It's circa 23:00 for me at the moment, so I may not get back to this at all tonight. Depending on the outcome of the "issues".
Aug 21 '08 #9

P: 16
I know stewart, but when I say Thousands, I mean over 100,000 rows.
Aug 22 '08 #10

P: 16
No problem sir, you can take your time.
Aug 22 '08 #11

NeoPa
Expert Mod 15k+
P: 31,709
Right, I've had a chance to look through this again and although you've clearly gone to a lot of trouble to explain this as well as you can, there are still a number of possibilities spinning around in my head as to what you may mean.

This is mainly due to what starts where? And what is expected to finish up where? The basic fundamental arithmetic is clear enough.

You show some data in your first post which I'm guessing is some sort of result set, but it doesn't match any of the record layouts you show. [tblCol1] in post #3 seems to be start data, but I've little idea where abouts the data for [tblCalc] in post #3 comes in the process. Then (in post #3) more data is shown, but no indication is given as to which table it is, or even if it's a table rather than results shown in a query.

In short, the details are fairly clear, but without the reference points they're hard to make proper sense of (I did try for quite a long while as I was conscious I had kept you waiting a while).

I'm sure when this is clarified we can make some better progress, which WILL include recordset processing as this is not something that can be done simply in SQL.
Aug 24 '08 #12

NeoPa
Expert Mod 15k+
P: 31,709
What we need is startpoint data and endpoint data, clearly indicating what comes from where and what ends up where.

If there are any fields anywhere that are included in this, which are not already explained, they must also be explained of course.

I'm confident that, with this, we can progress smoothly.
Aug 24 '08 #13

P: 16
What we need is startpoint data and endpoint data, clearly indicating what comes from where and what ends up where.

If there are any fields anywhere that are included in this, which are not already explained, they must also be explained of course.

I'm confident that, with this, we can progress smoothly.

Hi Sir,

To start with I have to import three textfiles, which are got from screen scraping from the mainframe sessions. I imported these three files to three tables COL1, FIRM1 and LGL1. I had to bring the COL1 and LGL1 files together, So I used a union query to bring theses two tables together(COL1 and LGL1 tables have the same fields). Then I made a table from the union of these using a make table Query into a table TblCOLL. So Now the rest of the validations are based on the two tables TblCOLL and FIRM1.
Table Name = [COL1]
Expand|Select|Wrap|Line Numbers
  1. [SET DATE]  [BALANCE] [ACCOUNT]
  2. 7/28/2008     100      12387654
  3. 7/27/2006     600      12387654
  4. 7/28/2007   2,300.00   56789526
Table Name = [LGL1]
Expand|Select|Wrap|Line Numbers
  1. [SET DATE]  [BALANCE] [ACCOUNT]
  2. 7/26/2008     300      12387654
  3. 7/25/2008     100      12387654
Table Name = [TblColl]
Expand|Select|Wrap|Line Numbers
  1. [SET DATE]  [BALANCE] [ACCOUNT]
  2. 7/28/2008     100      12387654
  3. 7/27/2006     600      12387654
  4. 7/26/2008     300      12387654
  5. 7/25/2008     100      12387654
  6. 7/28/2007   2,300.00   56789526
Table Name = [FIRM1]
Expand|Select|Wrap|Line Numbers
  1. [ACCOUNT]  [BALANCE]  [SET BAL CREDIT]  [SET DATE]
  2. 34567856     3259.98       CREDIT        8/5/2008
  3. 26737867     4080.50                     8/1/2008
  4. 68936789      106.75                     8/1/2008
  5. 76748987      813.75                     7/30/2008
I made the Tblcalc from the Tblcoll and FIRM1, using some queries. I used the account field to join both the tables.

Explanation of Fields in Table Tblcalc:

Some of the fields in the Tblcalc table are based on calculations.

=>I got the [sumof balance] field by finding the sum of the [balance] field of the TblCOLL.
=>Then I got the [Set bal] from the FIRM1 table by doing the following validation.

If the set bal credit is credit, then the [SET BAL] for that account is positive.
If the [set bal credit] field is blank then the [SET BAL] is negative.

=>Finally the [overstated bal] field is obtained by the following condition. If the [SET BAL]<0, then subtracting the [SET BAL] from the [Sumof balance] field.

Now going to the actual validation part,

I have to add two new fields to the table TblCOLL, they are [New Balance] and [Valid],

The following are the validations required for the [New Balance] field to be created.
For that I am going to use the Tblcalc Table,

If the [SET BAL] >=0, then [new balance]=balance

If the [SET BAL]<0, then the compare the sum of balance to the absolute value of the set balance.


Then the validations are done as I had mentioned in the Post #3.


Please let me know if you have any questions regarding this.

Thanks,
Arthur
Aug 24 '08 #14

NeoPa
Expert Mod 15k+
P: 31,709
This all goes along nicely until you get to describing the [Set Bal] field found in [tblCalc]. This is dependent on the FIRM1.[Set Bal Credit] field, but this only tells us what the sign is. There's no indication as to what the value should be.

When I check the data, it seems there are NO matching records between [tblColl] & [FIRM1]. This appears to leave us with nothing in [tblCalc].

In post #3 you have figures in [tblCalc], but I can't see any rationale behind them.

I will see if I can make any sense of the rest of it (ignoring how the figures got to be there), but it's hard to say what I may come up against at this point.
Aug 26 '08 #15

NeoPa
Expert Mod 15k+
P: 31,709
My first problem discovered is that the [Overstated Bal] (from post #3) does NOT follow the logic you describe. The entry for account "12387654" has a positive value of 900 in [Set Bal]. According to your logic this is NOT negative so should not be subtracted from [SumOfBalance] to give the [Overstated Bal] figure.

The information is now too far unreliable to proceed at this stage.
...
I have to add two new fields to the table TblCOLL, they are [New Balance] and [Valid],
...
Fields should only be added to tables after the fact when there is some sort of oversight. If these fields are required in [tblColl], then they should be included in the layout of [tblColl]. I will go back and update your post #3 to reflect this, but you should consider including these fields in your table when you create the table.

I will await your clarifications of the points I've raised.
Aug 26 '08 #16

P: 16
This all goes along nicely until you get to describing the [Set Bal] field found in [tblCalc]. This is dependent on the FIRM1.[Set Bal Credit] field, but this only tells us what the sign is. There's no indication as to what the value should be.

When I check the data, it seems there are NO matching records between [tblColl] & [FIRM1]. This appears to leave us with nothing in [tblCalc].

In post #3 you have figures in [tblCalc], but I can't see any rationale behind them.

I will see if I can make any sense of the rest of it (ignoring how the figures got to be there), but it's hard to say what I may come up against at this point.
Sorry, I did a typing mistake with one of the the fields in the FIRM1, I apologize for the inconvenience. You are right, the [balance] field should be [SET BAL] , I made a mistake there. The following table should make it clear.

Again for the FIRM1, in my previous post I just gave you some raw data that goes into the fields. Now I have changed the data in FIRM1 to make more sense.

Table Name = [TblColl]
Expand|Select|Wrap|Line Numbers
  1. [SET DATE]  [BALANCE] [ACCOUNT]
  2. 7/28/2008     100     12387654
  3. 7/27/2006     600     12387654
  4. 7/26/2008     300     12387654
  5. 7/25/2008     100     12387654
  6. 7/28/2007   2,300.00  56789526
Table Name = [FIRM1]
Expand|Select|Wrap|Line Numbers
  1. [ACCOUNT]  [SET BAL]  [SET BAL CREDIT]  [SET DATE]
  2. 12387654     900.00        blank         8/5/2008
  3. 56789526    2300.50        blank         8/1/2008
I made the Tblcalc from the Tblcoll and FIRM1, using some queries. I used the account field to join both the tables.
This following are the queries that I used to make the Tblcalc table,

1)qry_Tblcoll_sumofbalance:
Expand|Select|Wrap|Line Numbers
  1. SELECT TblColl.ACCOUNT,
  2.        Sum(TblColl.BALANCE) AS SumOfBALANCE
  3.  
  4. FROM Tblcoll
  5.  
  6. GROUP BY Tblcoll.ACCOUNT
2)qry_tblcalc:
Expand|Select|Wrap|Line Numbers
  1. SELECT qry_Tblcoll_sumofbalance.ACCOUNT,
  2.        qry_Tblcoll_sumofbalance.SumOfBALANCE,
  3.        IIf([SET BAL CREDIT] Like "*CREDIT*",[SET BAL],-[SET BAL]) AS [SIGNED SET BAL],
  4.        IIf(([SIGNED SET BAL]<0),([SumOfBALANCE]-Abs([SIGNED SET BAL]))) AS [OVERSTATED BAL]
  5.  
  6. FROM qry_Tblcoll_sumofbalance INNER JOIN FIRM1
  7.   ON qry_Tblcoll_sumofbalance.ACCOUNT = FIRM1.ACCOUNT
  8.  
  9. WHERE (((qry_Tblcoll_lgl_sumofbalance.ACCOUNT) Is Not Null))
  10.  
  11. ORDER BY qry_Tblcoll_sumofbalance.ACCOUNT
The [signed set bal] field I mentioned here is actually the [set bal] field that I mentioned in the Tblcalc in post#3. I just used it in this query for the validation. I used it a [set bal] instead of [signed set bal] to not confuse you. The [signed set bal] is the field where I do the following validation.

If the set bal credit is credit, then the [SET BAL] for that account is positive.
If the [set bal credit] field is blank then the [SET BAL] is negative.
[TblCalc]
Expand|Select|Wrap|Line Numbers
  1. [Account]  [SumOfBalance]  [Set Bal]  [Overstated Bal]
  2. 12387654        1100          900           200
  3. 56789526        2300         2300             0
The Tblcoll and the Tblcalc are the ones that are needed for the validation. I just described all the queries that went on to make the Tblcalc to make it clear. Hope this makes it clearer.

Thanks for your help.

-Arthur
Aug 26 '08 #17

P: 16
The following is the function I created to do the validation part. I get an error when trying to loop through the records. I used the following to loop through the records. How do I find the count of records in the Tblcoll and loop through it? can i use the rscoll.recordcount? But I need to use the following criteria,

I have to find the record count for the Tblcoll, where the accounts of both the Tblcoll and the Tblcalc are the same.


For i = 0 To DCount(Tblcoll![account], "Tblcoll", [Tblcoll!account = Tblcalc!account])

I know that this is not the best way for doing the validation. I would be thankful to you if you could tell me if the code for doing the validation is ok with the logic or do I need to do any change. Could you please suggest me if I should work on a better programming approach?
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function Calc()
  4.  
  5. Dim db As DAO.Database
  6. Dim rscoll As DAO.Recordset
  7. Dim rscalc As DAO.Recordset
  8. Dim sql As String
  9. Dim sBalance As String
  10. Dim rsfields As String
  11. Dim rscolcount As Long
  12. Dim calcvalue As String
  13. Dim fieldcount As Integer
  14. Dim x As Long
  15. Dim i As Integer
  16.  
  17.  
  18.  
  19.  
  20.  
  21. Set db = CurrentDb()
  22. Set rscoll = db.OpenRecordset("TblColl", dbOpenDynaset)
  23. Set rscalc = db.OpenRecordset("Tblcalc", dbOpenDynaset)
  24.  
  25.  
  26.  
  27. If rscoll.EOF Then
  28.   FindRecordCount = 0
  29. Else
  30.   rscoll.MoveLast
  31.   rscolcount = CStr(rscoll.RecordCount)
  32. End If
  33.  
  34.  
  35. ‘Debug.Print rscolcount
  36.  
  37.  
  38. While Not rscoll.EOF
  39.   rscoll.MoveFirst
  40.   rscalc.MoveFirst
  41.  
  42.   While Not rscalc.EOF
  43.  
  44.     For x = 0 To rscolcount - 1
  45.       If UCase(Trim(rscalc![account])) <> UCase(Trim(rscoll![account])) Then
  46.         If Not IsNull(rscalc![account]) Then
  47.           If (rscalc![SET BAL] > 0) Then
  48.             rscoll.Edit
  49.             rscoll![New Balance] = rscoll!Balance
  50.             rscoll.Update
  51.             rscalc.MoveNext
  52.           Else
  53.           If (rscalc![ SET BAL] <= 0) Then
  54.             If rscalc![Overstated Bal] <= 0 Then
  55.  
  56.               rscoll.Edit
  57.               rscoll![New Balance] = rscoll!Balance
  58.               rscoll.Update
  59.               rscalc.MoveNext
  60.             Else
  61.             If rscalc![Overstated Bal] > 0 Then
  62.               For i = 0 To DCount(Tblcollections![account], "Tblcoll", [Tblcollections!account = Tblcalc_mk!account])
  63.                   If rscoll!Balance <= rscalc![Overstated Bal] Then
  64.  
  65.                     rscoll.Edit
  66.                     rscoll![New Balance] = 0
  67.                     rscoll.Update
  68.                     rscalc.MoveNext
  69.  
  70.                   Else
  71.                   If rscoll!Balance > rscalc![Overstated Bal] Then
  72.  
  73.                     rscoll.Edit
  74.                     rscoll![New Balance] = rscoll!Balance - rscalc![Overstated Bal]
  75.                     rscoll.Update
  76.                     rscalc.MoveNext
  77.                   End If
  78.                   End If
  79.               Next
  80.  
  81.             End If
  82.             End If
  83.           End If
  84.           End If
  85.         End If
  86.       End If
  87.       If rscalc.EOF Then
  88.         rscoll.MoveNext
  89.         rscalc.MoveFirst
  90.       Else
  91.       rscalc.MoveNext
  92.       End If
  93.     Next
  94.   Wend
  95. Wend
  96.  
  97. rscalc.Close
  98. Set rscalc = Nothing
  99. rscoll.Close
  100. Set rscalc = Nothing
  101.  
  102.  
  103. End Function
Aug 26 '08 #18

NeoPa
Expert Mod 15k+
P: 31,709
At some point I will try to catch up with this, but I wonder if you have any understanding whatsoever of how difficult it is to respond to your posts when you don't respond to mine.

Every time I make a point, instead of responding directly, you try to explain everything again from a different angle. That means I have to reread everything and try to make it make sense against everything else you've written.

You can probably see where this is going.

You've not got a good track record for getting down your information correctly. Trying to make it all make sense is extremely difficult, and you don't help as you NEVER respond simply and directly to my posts.

Don't get me wrong, I can see you are putting a lot of effort into this as time goes on, but as your effort is directed away from the simple and into the more complicated, the whole thread is almost impossible to makes sense of.

Each post you make seems to throw up more problems than it answers questions. How come [tblColl] is back to the simple three fields again? Swimming in quicksand would be simpler.

I know it's hard because I'm not there standing next to you telling you what is helpful and what is simply causing a lot more complication, but if this carries on like this it will simply be impossible for me to make any consistent sense of what's here.

I will have another go at this tomorrow, but I can't promise I can bring all the various strands together into a problem that makes sense to me. We'll have to see how it goes.
Aug 26 '08 #19

P: 16
Sorry for my late response and the confusion.I was caught up with some work so I could not reply you at once. I apologize for any inconvenience that I put you through. I know you have been putting a lot of effort on this and I really appreciate you for spending your precious time trying to help me.

Sorry I missed out the two fields from the table Tblcoll. As I had mentioned earlier in post#3 I had to do some validations and add the values to the two fields , that is [New Balance] and [Valid]. All the validations I mentioned in my posts and the code are to add values to the two fields in the Tblcoll table.

Again I am very thankful for all the help that you have offered.

Thanks,
Arthur
Aug 27 '08 #20

P: 16
Hi NeoPa,

I know that you are a very busy person, Could you please spare a few minutes to look at the code that I created for doing the validations that we discussed before. Right now the code takes more time to execute. Could you please advise me if I need to change anything to make it work faster?

I have made some changes to the code from the previous post. the newly changed code is as follows,
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function Calc()
  5.  
  6. Dim db As DAO.Database
  7. Dim rscollgl As DAO.Recordset
  8. Dim rscalc As DAO.Recordset
  9. Dim sql As String
  10. Dim rscolcount As Long
  11. Dim rscalcount As Long
  12. Dim calcvalue As String
  13. Dim fieldcount As Integer
  14. Dim i As Integer
  15. Dim count As Integer
  16. Dim NewOverstatedBal As Long
  17. Dim OverstatedBal As Long
  18. 'Dim fldnewbal As DAO.Field
  19. Dim fldbal As DAO.Field
  20.  
  21.  
  22.  
  23. 'Error handler
  24. On Error GoTo Calc_error
  25.  
  26.  
  27. Set db = CurrentDb()
  28. Set rscollgl = db.OpenRecordset("TblColl", dbOpenDynaset)
  29. Set rscalc = db.OpenRecordset("Tblcalc", dbOpenDynaset)
  30. Set fldbal = rscollgl("Balance")
  31. 'Set fldbal = rscalc("balance")
  32. 'Set fldoverstatedbal = rscalc("Overstated bal")
  33.  
  34. NewOverstatedBal = 0
  35. i = 0
  36. If rscollgl.EOF Then
  37.     rscolcount = 0
  38. Else
  39.     rscollgl.MoveLast
  40.     rscolcount = CStr(rscollgl.RecordCount)
  41. End If
  42.  
  43. If rscalc.EOF Then
  44.     rscalcount = 0
  45. Else
  46.     rscalc.MoveLast
  47.     rscalcount = CStr(rscalc.RecordCount)
  48. End If
  49.  
  50.  
  51.  
  52. rscalc.MoveFirst
  53.  
  54. 'Loop through each record in the Tblcalc Table
  55. Do Until rscalc.EOF
  56.  
  57. ' If matching record is found then update field in
  58. ' Tblcoll to the value determined
  59.     If rscollgl.RecordCount = 0 Then
  60.         Exit Function
  61.     End If
  62.     rscollgl.MoveFirst
  63.  
  64.  
  65.     Do Until rscollgl.EOF
  66.  
  67.     If UCase(Trim(rscalc![account])) = UCase(Trim(rscollgl![account])) Then
  68.         If Not IsNull(rscalc![account]) Then
  69.                 i = 0
  70.                 count = DCount("[ACCOUNT]", "Tblcollections", "[ACCOUNT] = '" & rscalc!account & "'")
  71.             If (rscalc![SIGNED SET BAL] >= 0) Then
  72.                     Do While i < count
  73.                         rscollgl.Edit
  74.                         rscollgl![New Balance] = 0
  75.                         rscollgl.Update
  76.                         rscollgl.MoveNext
  77.                         i = i + 1
  78.                     Loop
  79.  
  80.             Else
  81.             If (rscalc![SIGNED SET BAL] < 0) Then
  82.                 If rscalc![overstated bal] <= 0 Then
  83.                     Do While i < count
  84.                             rscollgl.Edit
  85.                             rscollgl![New Balance] = fldbal
  86.                             rscollgl.Update
  87.                             rscollgl.MoveNext
  88.                             i = i + 1
  89.                     Loop
  90.  
  91.                 Else
  92.  
  93.                 If rscalc![overstated bal] > 0 Then
  94.                            OverstatedBal = rscalc![overstated bal]
  95.                     Do While i < count
  96.  
  97.                         If rscollgl!balance <= OverstatedBal Then
  98.  
  99.                                         rscollgl.Edit
  100.                                         rscollgl![New Balance] = 0
  101.                                         rscollgl.Update
  102.  
  103.                                         OverstatedBal = OverstatedBal - fldbal
  104.                                         rscollgl.MoveNext
  105.                                         i = i + 1
  106.  
  107.                         Else
  108.                         If rscollgl!balance > OverstatedBal Then
  109.  
  110.                             rscollgl.Edit
  111.                             rscollgl![New Balance] = fldbal - OverstatedBal
  112.                             rscollgl.Update
  113.                             OverstatedBal = 0
  114.                             rscollgl.MoveNext
  115.                             i = i + 1
  116.  
  117.                         End If
  118.                         End If
  119.  
  120.                     Loop
  121.  
  122.                 End If
  123.             End If
  124.             Exit Do
  125.  
  126.                     End If
  127.             End If
  128.             Exit Do
  129.         End If
  130.  
  131.  
  132.     End If
  133.  
  134.  
  135.  
  136.  
  137.     rscollgl.MoveNext
  138.  
  139.  
  140.     Loop
  141.  
  142. rscalc.MoveNext
  143.  
  144.  
  145. Loop
  146.  
  147.  
  148. rscalc.Close
  149. rscollgl.Close
  150. Set rscalc = Nothing
  151. Set rscalc = Nothing
  152.  
  153. Set db = Nothing
  154.  
  155. Exit Function
  156.  
  157.  
  158. Calc_error:
  159.     MsgBox Err.Description, , "ERROR " & Err.Number & "  Calc"
  160.     Stop
  161.     Resume
  162.  
  163. End Function
Sep 3 '08 #21

NeoPa
Expert Mod 15k+
P: 31,709
Arthur,

It's not that I have no time at all. It's simply that you make the job so complicated that I need to set aside a large portion of time just to look at this.

Most questions, as they should be, are simple questions asking about a simple problem (I'm not saying easy, but the question is clear). That is really what the site is about. Most of the effort is finding and explaining the (an) answer. It shouldn't be about spending hours just to work out the question.

Your job is to explain your problem in as general terms as possible. Not to get into too much detail of all your various confusions. The deeper you get into that, the more information I require, just to know what you're asking. Hence I find my hands tied before I even start.

Not only does this leave you with less help than you could get, it also leaves me with far less time available to help others. Not an ideal state of affairs.

I will have another go tonight (I have made some time available), but I already know that I don't have the details I need to understand the question properly. I will have to have a look through your approx 160 lines of code to see if I can dig up some clues from there.

I suspect, unfortunately (as I have from early on), that a SQL solution would have been much more efficient and possibly even more straightforward. For this I needed the clear details that have not been forthcoming (certainly not in clear enough form).
Sep 4 '08 #22

NeoPa
Expert Mod 15k+
P: 31,709
Having looked at your code one performance thing springs immediately to mind (no make that two).
  1. You open both tables as Dynasets with no sorting specified. I expect this code, as it stands, would execute more efficiently if both were opened sorted by [Account] (PK). Your code need then only process through the sub-recordset until the records no longer match. Of course .FindFirst should be used to go straight to the first matching record too.
  2. I would actually consider designing an updatable query (if possible) where both tables are INNER JOINed and using that as a Dynaset recordset in your code (sorted by [Account] still of course). This would involve recoding fairly fundamentally, but assuming you are able to get an updatable dynaset this could more quickly provide your results I expect.
An extra benefit of point #2 is, even though the recoding would be extra work, the resultant code should be shorter and cleaner (easier to maintain).
Sep 4 '08 #23

P: 16
Having looked at your code one performance thing springs immediately to mind (no make that two).
  1. You open both tables as Dynasets with no sorting specified. I expect this code, as it stands, would execute more efficiently if both were opened sorted by [Account] (PK). Your code need then only process through the sub-recordset until the records no longer match. Of course .FindFirst should be used to go straight to the first matching record too.
  2. I would actually consider designing an updatable query (if possible) where both tables are INNER JOINed and using that as a Dynaset recordset in your code (sorted by [Account] still of course). This would involve recoding fairly fundamentally, but assuming you are able to get an updatable dynaset this could more quickly provide your results I expect.
An extra benefit of point #2 is, even though the recoding would be extra work, the resultant code should be shorter and cleaner (easier to maintain).
Hi Neopa,

Thanks for all your help, as per your advise I sorted both the tables by account, used indexes for the table, and used the find first method to search for the accounts which are same. These changes helped me bring down the time to two minutes. I am running this in my local machine, so when I try running this in the network this should take more longer I guess. Is there anything else I can do to bring down the time to seconds. I am attaching the code below.

Thanks,

Arthur

Expand|Select|Wrap|Line Numbers
  1. Public Function Newcalc()
  2. Dim db As DAO.Database
  3. Dim rscoll As DAO.Recordset
  4. Dim rscalc As DAO.Recordset
  5. Dim strSQL As String
  6. Dim intIndex As Integer
  7. Dim i As Integer
  8. Dim count As Integer
  9. Dim OverstatedBal As Currency
  10. Dim aryAccount() As String
  11. DAO.DBEngine.SetOption dbMaxBufferSize, 5120
  12. 'Error handler
  13. On Error GoTo ErrorHandler
  14.  
  15.  
  16. Set db = CurrentDb()
  17. 'Open recordsets on the Tblcollections and Tblcalc_mk tables. 
  18. 'If there are 'no records in either table, exit the function.
  19.  
  20. strSQL = "select * from Tblcalc order by account"
  21. Set rscalc = db.OpenRecordset(strSQL, dbOpenSnapshot)
  22.  
  23. If rscalc.EOF Then Exit Function
  24.  
  25. strSQL = "SELECT * FROM Tblcoll ORDER BY account"
  26. Set rscoll = db.OpenRecordset(strSQL, dbOpenDynaset)
  27.  
  28.  
  29.  
  30.  
  31.  
  32. 'For the first record in Tblcalc table, find the first matching record
  33.    'in Tblcoll table find the next record that satisfies the criteria. 
  34.  
  35.  
  36. Do Until rscalc.EOF
  37.     rscoll.FindFirst "account = '" & rscalc![account] & "'"
  38.     i = 0
  39.     count = DCount("[ACCOUNT]", "Tblcoll", "[ACCOUNT] = '" & rscalc!account & "'")
  40.  
  41.     If (rscalc![SIGNED SET BAL] >= 0) Then
  42.         Do While i < count
  43.                         rscoll.Edit
  44.                         rscoll![New Balance] = 0
  45.                         rscoll.Update
  46.                         rscoll.MoveNext
  47.                         i = i + 1
  48.         Loop
  49.  
  50.     Else
  51.     If (rscalc![SIGNED SET BAL] < 0) Then
  52.         If rscalc![overstated bal] <= 0 Then
  53.             Do While i < count
  54.                             rscoll.Edit
  55.                             rscoll![New Balance] = rscoll!balance
  56.                             rscoll.Update
  57.                             rscoll.MoveNext
  58.                             i = i + 1
  59.             Loop
  60.  
  61.         Else
  62.  
  63.         If rscalc![overstated bal] > 0 Then
  64.         OverstatedBal = rscalc![overstated bal]
  65.         Do While i < count
  66.  
  67.             If rscoll!balance <= OverstatedBal Then
  68.  
  69.                                         rscoll.Edit
  70.                                         rscoll![New Balance] = 0
  71.                                         rscoll.Update
  72.  
  73.                                         OverstatedBal = OverstatedBal - rscoll!balance
  74.                                         rscoll.MoveNext
  75.                                         i = i + 1
  76.  
  77.             Else
  78.             If rscoll!balance > OverstatedBal Then
  79.  
  80.                             rscoll.Edit
  81.                             rscoll![New Balance] = rscoll!balance - OverstatedBal
  82.                             rscoll.Update
  83.                             OverstatedBal = 0
  84.                             rscoll.MoveNext
  85.                             i = i + 1
  86.  
  87.             End If
  88.             End If
  89.  
  90.         Loop
  91.         End If
  92.         End If
  93.     End If
  94.     End If
  95.     rscalc.MoveNext
  96. Loop
  97.  
  98.  
  99.  
  100.    rscalc.Close
  101.    rscoll.Close
  102.    db.Close
  103.  
  104. Set rscalc = Nothing
  105. Set rscoll = Nothing
  106. Set db = Nothing
  107.  
  108. Exit Function
  109.  
  110. ErrorHandler:
  111.    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
  112. End Function
  113.  
Sep 5 '08 #24

NeoPa
Expert Mod 15k+
P: 31,709
I would simply suggest looking at point #2 from my earlier post (#23).

Take a backup before you proceed further though. It would appear that you have made some fair progress here. It would be a shame to lose that for any reason.

Good luck with your project :)
Sep 5 '08 #25

P: 16
Thank you Sir!, I will try doing that.
Sep 5 '08 #26

Post your reply

Sign in to post your reply or Sign up for a free account.