A field from last record to auto enter into a field in a new record (1 Viewer)

[WarriorMCB]

I'm looking to do a simple inventory where what comes in (form 1) and what goes out (form 2) are taken away to leave a total. This total needs to be added to the next 'what comes in' form when new inventory is recorded. Is it possible to have a vba or something that can take the last field record sum from form 2 and put it into a field in form 1 (to a new record)
I have a query that does the math from the two tables (forms) that give the remaining total on that given date. Now I need that total to be added when more inventory comes in, giving me a bigger total than what came in. Can a new record, record a value from the previous record so that it can sum a new value in that new record.
I hope that's not confusing.

Thankyou

 

[theDBguy]

Hi. Not sure storing the previous total is necessary. All that calculations should be possible using a query.

 

[WarriorMCB]

Yes, however, I need to add the sum from the last record without having to type it. A query reads and can sum from the same row but I need it to sum from the previous record.

 

[theDBguy]

Yes, however, I need to add the sum from the last record without having to type it. A query reads and can sum from the same row but I need it to sum from the previous record.

Hi. A query can do that. Look up running sum examples.

Sent from phone...

 

[plog]

A query reads and can sum from the same row but I need it to sum from the previous record.

No, a query can add the value in the same field in different rows together:

SQL COUNT(), AVG() and SUM() Functions

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Your method is the incorrect way to do this, a "running sum query" (search the forum for this term, or go to the bottom to see recommended similar posts) is the correct way. Essentially, you don't store the current balance, you just have the computer calculate it up to the point you need whenever you need.

 

[WarriorMCB]

I have no idea what a running sum is, let alone know how to do it. I just need one little field from the previous record to be calculated in the current row and field. I'm totally lost. Without a multitude of code I guess I'm at a stand still.

thank you anyways

 

[theDBguy]

I have no idea what a running sum is, let alone know how to do it. I just need one little field from the previous record to be calculated in the current row and field. I'm totally lost. Without a multitude of code I guess I'm at a stand still.

thank you anyways

Hi. See if the SQL I posted in this other thread helps you understand what I mean by calculating previous balance without storing it.

Solved - Join queries-reference group by date

Sorry, I compared my Excel results to your query and i found the problem; i had entered an extra beginning balance on my sheet. So your numbers are absolutely correct!! I just need two minor adjustments: - I need to show the subtotal column as another column in the query (for report purposes)...

www.access-programmers.co.uk

The result looks like this.

 

[plog]

Did you search the forum? Did you check on the link at the bottom of this page titled "Running sum Query"? What specific issues do you have after reading those posts?

 

[WarriorMCB]

Hi. See if the SQL I posted in this other thread helps you understand what I mean by calculating previous balance without storing it.

Solved - Join queries-reference group by date

Sorry, I compared my Excel results to your query and i found the problem; i had entered an extra beginning balance on my sheet. So your numbers are absolutely correct!! I just need two minor adjustments: - I need to show the subtotal column as another column in the query (for report purposes)...

www.access-programmers.co.uk

The result looks like this.

Yes this looks like what I am wanting to achieve.

 

[WarriorMCB]

Did you search the forum? Did you check on the link at the bottom of this page titled "Running sum Query"? What specific issues do you have after reading those posts?

Issue is I have no idea what I'm seeing let alone know how to type the formula after seeing what I saw.

 

[Pat Hartman]

I just need one little field from the previous record to be calculated in the current row and field. I'm totally lost.

You are assuming that Access is a spreadsheet. It is not. It is a relational database. What you are requesting would violate the rules of normalization. Step away from the keyboard and do some reading on normalization. I'm sure that someone will break down and tell you how to do this but sorry, it won't be me. That's how wrong it is to do what you are trying to do.

There are also a number of threads on inventory control that you might find helpful so you can see how to do it. Generally, the accepted best method is to keep a transaction table showing additions and subtractions with a reason - buy, sell, damaged, shrinkage, etc. Then you sum the transactions to get the current balance or you use dates to control the ending date so you can calculate the balance on last Thursday if that is what you want.

 

[WarriorMCB]

Hi. See if the SQL I posted in this other thread helps you understand what I mean by calculating previous balance without storing it.

Solved - Join queries-reference group by date

Sorry, I compared my Excel results to your query and i found the problem; i had entered an extra beginning balance on my sheet. So your numbers are absolutely correct!! I just need two minor adjustments: - I need to show the subtotal column as another column in the query (for report purposes)...

www.access-programmers.co.uk

The result looks like this.

Can you help me achieve this please. Thankyou

 

[theDBguy]

Can you help me achieve this please. Thankyou

Can you post a sample db with test data?