Creating New Rows (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
All,
I'm trying to make Access work like XL, creating data on new rows based on prior rows.
Sort of like an accounting system

Best to give an example. See the attached spreadsheet

I will have Columns A - E in the Table
Column F values are what I need to create. See the formulas.

A few things to note.
1) The data will be sorted by Account ID, Security ID, Position_Date
2) The Combination of Position Date, Account_ID and Security ID is unique
3) Yellow notes the start of each new Account and Security ID

I'm sure this is a code thing... So I'm a little out of my element.

I really appreciate the help
 

Attachments

vba_php

Forum Troll
Local time
Today, 12:45
Joined
Oct 6, 2019
Messages
2,884
at first I thought you were asking about inventory control. but you are trading stocks it seems like. you should look here before you do anything else:


also, your "position opening quantity" field is just like an inventory tracking field. it doesn't matter whether the method would be LIFO or FIFO, or any other "in-between" method. if you want to take a look at a nice access template for inventory management as well, look at northwind here:


another good resource is here:


and yes, you are correct in saying that a large amount of code would be required to do this in access. however, the links provided above should give you easier solutions to the problem.
 

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
PHP - thanks for the note, but it is not quite what I need. Those inventory systems give you a net total for the activity for all days' of activity. I need to create an end of day value for each day like a running balance.
 

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
PHP - Wow - you are the best. I think these both will work ad I see one has the formula in the query and one has it in code.
They both sort of get me to the same results I think - so can you explain why one DB or method would be better then the other? I'll keep reviewing them.
The database may get big so speed will be an issue.
Thanks very much!
 

vba_php

Forum Troll
Local time
Today, 12:45
Joined
Oct 6, 2019
Messages
2,884
Well I believe that if you have to call code from a query it basically takes longer than a query running by itself. That basically makes sense since it's longer for the transactions to travel. But until your database gets very large it probably doesn't matter which method you use. But I will take a look tonight and I will run a test on 500000 records just to give you an example sound good?
 

vba_php

Forum Troll
Local time
Today, 12:45
Joined
Oct 6, 2019
Messages
2,884
What I meant by the transactions having longer to travel is the same concept that they use in New York city where the cables connecting the computers to the trading floors of the stock market have longer to travel if the buildings are far away from the trading floor. Does that make sense?
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 12:45
Joined
Feb 28, 2001
Messages
16,846
Usually, when you have a choice between VBA code or an SQL-based query, the query is faster for equal numbers of records. Just an observation. But as Adam is pointing out, for small lists you might not notice the difference unless you built-in some kind of time-stamp logger.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 12:45
Joined
Feb 28, 2001
Messages
16,846
What I meant by the transactions having longer to travel is the same concept that they use in New York city where the cables connecting the computers to the trading floors of the stock market have longer to travel if the buildings are far away from the trading floor. Does that make sense?
No. What makes a speed difference is (a) the speed of network connections from point A to point B to point C etc, and (b) the number of "hops" it has to make at any point that is a "store-and-forward" device such as a router, network address translator, or gateway device for the relevant protocol. Message speed is like the old statement about a chain being no stronger than its weakest link. A network is no faster than its slowest link. But distance is no more than a secondary concern. It is the network's configuration that matters.
 

vba_php

Forum Troll
Local time
Today, 12:45
Joined
Oct 6, 2019
Messages
2,884
No. What makes a speed difference is (a) the speed of network connections from point A to point B to point C etc, and (b) the number of "hops" it has to make at any point that is a "store-and-forward" device such as a router, network address translator, or gateway device for the relevant protocol. Message speed is like the old statement about a chain being no stronger than its weakest link. A network is no faster than its slowest link. But distance is no more than a secondary concern. It is the network's configuration that matters.
oh really Richard? Maybe you should ask the people in New York City that because my words comes from a direct article that was written years ago. Perhaps you should restate your words. Or perhaps we could have another public debate but let's not do that and hijack the thread like we always do okay?
 

vba_php

Forum Troll
Local time
Today, 12:45
Joined
Oct 6, 2019
Messages
2,884
To further that point if all Network speeds are the same anyway that basically boils down to the signals and the amount of time they have to transfer through the cable. Considering that all corporations compete minute-by-minute nowadays and have for Years anyway I would assume I'm right and you're wrong in this case. But again lest I say that let's not belabor this point anymore past this post
 

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
Well I believe that if you have to call code from a query it basically takes longer than a query running by itself. That basically makes sense since it's longer for the transactions to travel. But until your database gets very large it probably doesn't matter which method you use. But I will take a look tonight and I will run a test on 500000 records just to give you an example sound good?
Of course. Any help is appreciated. TY
 

vba_php

Forum Troll
Local time
Today, 12:45
Joined
Oct 6, 2019
Messages
2,884
so here are your RETURN tests. as you can see, running a query by itself on 1 million records takes no time whatsoever, using the pointer function to the previous record. see database called running balance - return. it can be found here (it is too big to upload to this forum):


but when the function call is used, running the query on anything more than 3,800 records causes access to freeze because the database engine can't handle the load requirement => see database called running total - return. So obviously using the pointer function is your best bet in a case like this. =) (test run in access 365 - version 2016)
 

Attachments

arnelgp

error reading drive A:
Local time
Tomorrow, 01:45
Joined
May 7, 2009
Messages
9,349
it will be much faster if instead of calling a function for the calculated column, Pos, use the expression:

Pos: CLng(DCount("1","orders","orderID<=" & [orderid]))
 

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
Hey PHP. Im really sorry, I'm struggling with this. Your solutions was so very helpful (thanks for all the links), but there are wrinkles I did not figure in. If this takes you too long to figure out I totally understand... I'm always amazed how many generous people are on this forum.
I'd expect this would take me days to get right so I'm hoping you can help.

Attached is a database with 2 tables
1) ActivityTable: Has all activity for 3 different accounts. In the real DB this is a query result, not a table, so there are no numbered rows. I'd rather keep the source in a query and not save the data to a table first
2) Business Dates

What I need is...
For Every combination of AcctKey & SecKey & AsOf I need a running balance of Quantity for EVERY Business day that the ending value <> 0.

The Spreadsheet attached has the expected output for 1000 & IBM000 for a few days in sequence. We are solving for Column E.

Of Course - each AcctKey & SecKey can start and end on different days

Thanks again for any help.
 

Attachments

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
it will be much faster if instead of calling a function for the calculated column, Pos, use the expression:

Pos: CLng(DCount("1","orders","orderID<=" & [orderid]))
Thanks for this. I'll try this.
My last post added a few wrinkles to my original ask.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:45
Joined
May 7, 2009
Messages
9,349
is this what you have in mind.
run SampleForm from the attached.
view code in Module1.

it deletes the records from TempTable everytime
the form is run. you need to frequently Compact & repair the db
to prevent bloat.
 

Attachments

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
is this what you have in mind.
run SampleForm from the attached.
view code in Module1.

it deletes the records from TempTable everytime
the form is run. you need to frequently Compact & repair the db
to prevent bloat.
Arnelgp - thanks SO MUCH for this... You just saved me days of my life trying to figure it out, and I probably would have done it poorly. I'll review the code and results in detail and ask any questions, but at first it looks great.
Hopefully I wont have to bother you.

Really appreciate your help
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:45
Joined
May 7, 2009
Messages
9,349
I hope somebody steps in and create an "all-query-only" solution so
you won't be needing a the TempTable.
 

Tskutnik

Registered User.
Local time
Today, 13:45
Joined
Sep 15, 2012
Messages
185
is this what you have in mind.
run SampleForm from the attached.
view code in Module1.

it deletes the records from TempTable everytime
the form is run. you need to frequently Compact & repair the db
to prevent bloat.

Arnelgp. Thanks again. You did a lot of work on this and it is appreciated.

A few things I have questions on:
1) Can the End (most recent) date be the one business date prior to today instead of the date of the last activity? This is an accounting process, so whatever the End of Day Quantity is on the date of last activity it would have to carry forward to today. See the IBM example - the last activity is 12/24/14 and that End of Day Quantity is 25, so that 25 should continue to carry forward to "Business Yesterday" (as of Friday date if run on Saturday, Sunday, or Monday)

2) I see End of Day Quantity = 0 for all the dates with no values. I can just filter these out of course, but I don't know if it is more efficient to do this in code.

Thanks again... and again.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom