Calculate between different records

Actually, it should not be faster UNLESS you had a DLOOKUP in the query, because VBA is interpreted but the query processor is compiled.

If you have a slot already in the extant table for that difference, then you don't need an extra table. Then you just do your recordset ops to include a .Edit and a .Update (doing, in essence, an update in place).

When the number of records is low enough that the query execution time isn't a problem why not use a query?

Click to expand...

If you will note, I did ask how many records were involved. My VBA answer is purely theoretical based on general principles. However, I have never claimed to be so much of a purist as to avoid the quick-but-dirty methods now and then.

The only issue I have with this kind of problem is that the syntax of the WHERE clause is usually SO convoluted as to be worse than writing the code. And please note also in the thread that our OP has indicated that the dates are non-uniformly spaced including cases that could occur on the same day. The SQL syntax for THAT one becomes a nightmare, but doing it via VBA can NEVER get confused. (If the recordset's base query is written incorrectly, the answer can be WRONG - but never confused.)

The_Doc_Man said:

If you have a slot already in the extant table for that difference, then you don't need an extra table. Then you just do your recordset ops to include a .Edit and a .Update (doing, in essence, an update in place).

Click to expand...

Thanks for making that a good point and it might be the case here.

@Tskutnik. Could you add a field to the table for this difference? I think a VBA solution would be faster and less complicated.

All, Thanks again for all the help. It is really appreciated. I had to put this down for a week so sorry for the delay. Attached is a new database with a more data, but for upload reasons I could not send the whole thing.

In the production version of this database there will be millions of records, but this calc will only be needed on the new records that have not been calculated yet, as identified by a query. My best guess it this is 100k records per calculation process.

The attachment has a Source data table and query where the sorting happens.

Since the thread is long here is a summary of where this all is:
· The question is how to reference another record in the source table when doing, e.g. divide an Account’s Current_Value (current period) by the same Account’s prior period Current_Value.
· The combination of Account and AsOf fields make each record unique. There is only one record in the database for this combination of fields.
· There are multiple Accounts in the same dataset, so you can’t always just default to the prior record (one above the current in the data) since each Account has a first period. For these earliest/first periods there would be no opening value and therefore no calculation.
· The data CAN be sorted in a query by Account / Month, in the query. This was mentioned in the “adjacent records” part of the thread.

Seems we are not 100% sure what the best method is, but to follow the thread…
· Re: Notes about THE DOC MAN’s “slot already in the extant table for that reference” – I may not be clear since I only have one table and one query. Technically there is no dedicated “extant” table. I assume you do NOT mean creating a new field in the source data table. If a new table is needed that’s not a problem, and I set one up. You will see I added a field for Prior_Value (Current_Value of the Prior period used in the calc). It’s helpful but not required if the calculated results are created and saved.

It would really help to get as specific as possible with a SQL or VBA code. I’m still hacking my way through this.

It seems we are getting close, so thanks again for your help guys. I really appreciate it.

I've run into problems trying to write the code for this. I posted the problem at http://www.access-programmers.co.uk/forums/showthread.php?p=1494004#post1494004 if you're interested.

This has nothing to do with the problem I'm having but I have a question about the Source table in the database you uploaded. The previous table we were working with had a security code field that was need along with account number and date to uniquely identify the records. Why isn't that security code in the Source table in this database?

The attached database has code that updates a field named PreviousValue that was added to the Source table. The code is in a module and can be run from the form frmRunUpdate. This code took about one second to update the 15592 records in the Source table. I tested it with about one million records and it took about 52 seconds.

I'm really sorry for the different fields in the sources. The right combination to make a records unique is Account, Security and Date, as you noted. I'm working on a few different projects and may have attached the wrong one. The security code should be in there.
If needed I can create one field with these 3 values concatenated, to make an easier and single reference point form record to record. The only catch is the real comparison is one Account&Security vs. the prior date's Account&Security, so the easier concatenation may not include the data field.

BTW - I just tried the code and it looks incredible - but will it work with the security field also? I assume so if the Account&Security code is unique.

I reattached the correct DB just in case (same as I sent earlier in the thread). I added a new field in Table 2 called Position_Ref, which is the Account&Security concatenated. I know ... you never do a formula in the table... Please assume in the final version this will be loaded and not calculated.

If your code changes because the security has be re-added please let me know. I'm so new at this I really need things spelled out for me sometimes.... but looking through your code I think I am starting to get it.

I cant tell you how much I appreciate the help - you have no idea how long I've been trying to figure this out. You are the best. Thanks for all the time.

Tskutnik said:

If needed I can create one field with these 3 values concatenated, to make an easier and single reference point form record to record. .

Click to expand...

That's not need. Actually that would just get in the way. Because there's a date in the mix a concatenation wouldn't sort correctly unless you reformatted the date to something like yyyy-mm-dd

The attached database has your code and the following changes.
I learned a lot from looking through your code and running a few tests. Thanks again for your help with all of this.

“FYI” notes
1) The SOURCE table now has the correct information, and in the sequence that it will appear in real life, which is day by day…. Very different than the last DB we were looking at. The query will sort this all the same of course.
2) I added a new field in the SOURCE called HOLDING _REF, which is the concatenated unique Account&Security. I found/replaced ACCOUNT to HOLDING _REF in your code to pick this field up instead of just ACCOUNT.The query was also changed from ACCOUNT to HOLDING_Ref.

“Wow - this is perfect” notes….
3) Since the SOURCE data will not always be in Account / Date order If I added sorting to the QrySourceSorted.
4) As the database is used new data will be added each day, and the calculation will only need to apply to any unpopulated PREVIOUSVALUE fields.
Both were tested with new data and worked great.

Questions/Issues
5) Re #2: Since this is a concatenation of 2 existing fields it obviously creates some redundant data in the SOURCE table.Is it better to change the code somehow to reference the Account & Security as separate fields (accomplishing the same unique function) or is the concatenation redundancy OK?The considerations are DB size vs. performance I guess….
6) In production new data will be loaded each day, so the ongoing calculations will only be needed where PREVIOUSVALUE = Null. Problem is, just filtering to NULL in the query won’t work since that result will exclude the prior period’s CURRENT_VALUE record. The other winkle is that the daily loads will not just have current day data because of prior day record replacements.Explaining why will take 1000 words and it does not matter for this exercise.Instead, I edited the table with what the data will look like in production.You will see the 2 conditions in question:A) to calculate the new data (could be multiple days) loaded in with missing PREVIOUSVALUE (see AsOf = 2/13/17 and 2/14/17).B) calculate any prior day dates with missing PREVIOUSVALUE (see HOLDNG_REF = Acc5_Sec2 for 12/1/16 – 2/10/17).

Again, thanks so much for the help and being so generous with your time.
And have a great 4th.

5) Re #2: Since this is a concatenation of 2 existing fields it obviously creates some redundant data in the SOURCE table.Is it better to change the code somehow to reference the Account & Security as separate fields (accomplishing the same unique function) or is the concatenation redundancy OK?

Click to expand...

It's probably OK, but it's not necessary and I think it introduces possible confusion. The attached database has the code rewritten without this concatenation. The HOLDING_Ref field has been deleted from the Source table.

The other winkle is that the daily loads will not just have current day data because of prior day record replacements.Explaining why will take 1000 words and it does not matter for this exercise.Instead, I edited the table with what the data will look like in production.You will see the 2 conditions in question:A) to calculate the new data (could be multiple days) loaded in with missing PREVIOUSVALUE (see AsOf = 2/13/17 and 2/14/17).B) calculate any prior day dates with missing PREVIOUSVALUE (see HOLDNG_REF = Acc5_Sec2 for 12/1/16 – 2/10/17).

Click to expand...

For discussion purposes let's call the table with all of the records the Source table and the table with the daily input the Input table.

Will the Input table have contain records that already exist in the Source table, i.e., records with the same Account, Security_ID and AsOf in which case the Source records would need to be updated? If that the case we will need to update some records and append others. This is not hard to do. Just let me know and I'll write some queries and code to do it.

As far as updating the previous values the easiest way is just to update the entire Source table after the new data has been added. Let's say that this will take 2 minutes for each million records in the Source. Is this acceptable? If it's not then the update will have to operate on the data in the Source table after a certain date somehow to be determine by the content of the Input table and Source table. I need to think about this.

6) In production new data will be loaded each day, so the ongoing calculations will only be needed where PREVIOUSVALUE = Null. Problem is, just filtering to NULL in the query won’t work since that result will exclude the prior period’s CURRENT_VALUE record.

Click to expand...

So it's a matter of finding the AsOf date such that all records on or later than that date are the ones needed for the update. I think I have the answer to this that considers that the records in the Input Table table may not be contiguous with the records in the Source Table but please look for any holes in this logic and run tests that you think might break this.

In the attached database I have a table named Input Table which represents data that would be added to the Source table. For testing I took the last 873 records in the Source Table and moved them to the Input Table. The first thing I do to find this date is query the Input Table with the query named qryEarliestOfInputTable. This query returns all the Accounts, Security_IDs, and AsOf dates for the earliest AsOf date in the Input Table. For each of these it get the previous date from the Source table with the expression:


The data we want is the earliest date in these previous dates. More about that later.

I added an optional parameter to the UpdatePreviousValues code name StartDate. If the subroutine is called with a date then the recordset is limit to records on or later than that date by this line:

in the code. More on that later.

I created an append query name qryAppendInput which appends the Input Table to the Source table, but it does a little more than that. If you look at this query in design view you will see that there is an outer join between the Input Table table and the Source table on the Account, Security_ID and AsOf fields. Also that the criteria for the Source ID is Is Null. What this does is ensure that nothing is appended to the Source table that's already there, i.e., doesn't allow duplicate Account, Security_ID and AsOf to be append. For example if you run this query twice the second time no records will be appended. So now for the code thats' run by the Run Update button


The StartDate is retrieved as described earlier. Then the records in the Input Table table are append to the Source table. Finally the code is run for the records on or after the StartDate.

There is a Copy Tables button on the form which reset the tables (copies the copies) in there initial state if you want to run the test multiple times.

The attached database has an additional button on the form to check the results of a test.