Summing Field from one table into anther.

[Mr. Hero]

I have 3 tables (tblMain,tblEmpsBal,tblEmpsCurBal). Table tblMain is the main data table. Table tblEmpsBal contains individuals names/balance. Table tblEmpsCurBal contains the individuals updated balances.

Currently I am updating tblEmpsCurBal by manually udding the entries in tblMain. tblEmpsBal has their begining balance as of the 1 day of the year. This balance is updated into another table (tblEmpsCurBal) as entries are added into tblMain. I have created a qry that groups the entries by Names, and sums the entries based on the type of entry. The issue I am having is taking the sumed entry and subtracting it from the current balance table (tblEmpsCurBal).

Do I have to use a lookup function in my qry to properly add or subtract from the current balance and then have it update into a new table? I need some assitance understanding how I can accomplish this. Any guidance/assistance is greatly appreciated. Thanks.

 

[gemma-the-husky]

the obvious question is - why do you need the data in tblempscurbal to be held outside the tblempsbal - cannot it be just another field(s) in that table?

secondly - you dont normally need to store the result of calculations anyway. How often do you recacluate the running balance?

 

[Mr. Hero]

Dave,

Well my tblMain is used for the the Input form. In a sense tblmain is the foundation, I don't want any manipulation or changes to be done to this table. The tblEmpsBal is also the same way, because it contains the beginning balance from the first day of the year. This tbl is not allowed to change. So, I made another table tblEmpsCurBal. My qry groups the Emps by name and type of entry, then it sums the numbers entered. I then manually update the new tbl by looking at what was last updated and subtracting from this balance. This is the part I am having a hard time working with. I hope I have explained well enough. What can I do? I am hoping that I can make a append qry and in it calculate by taking the current balance and subtracting from it as new entries are made; then have it append to a Table that holds the final (New Current) balance.

Also, the calculation is done every time a new entry is made, sometimes this can be 10+ times a day. In which I need to keep track of all the changes.

 

[Mr. Hero]

I have attached a image that explains a little of the layout. I am having some difficulty explaining the issue.

 

[gemma-the-husky]

as I say - doing it this way doesnt really use the power of access correctly. you shouldnt bother if values are changed in any tables. you may need to audit the changes, but changes per se should not be an issue.

having said that, its your database, but you will tie yourself in knots trying to manage the data in the way you are.

 

[Mr. Hero]

Oh... Okay, then how can I ultilize access in a better? I have managed to get the information to store correctly. But, updating the other table as I make new entries is my problem. I am not sure what to do.. Do I need to make a qry, or do I write some code to select, calculate, then append?

 

[gemma-the-husky]

you wouldnt bother creating/storing a total.

when you need it, just run a query to compute the total.

that way, you dont have problems if you edit/delete records in ways you havent allowed for. eg directly in the taqble

 

[Mr. Hero]

I will try to change the way the this process is going. Just from your suggestions I see where I may have made this process very difficult. Althought I may understand I am not sure that restructuring will create a solution for updating the balance. But I will try and see where I end up. Thanks.

 

[Mr. Hero]

Now, I have a main entry table, a table with the start balance, and a table for a qry to append data into. I have used a single expression to calculate the data. I need some help with this part. I can get the balance to subtract and update into a table. But it is taking the original balance and subtracting every entry. i.e. If the begining Balance=20, and 3 entries, each entry equals 2. When I run my qry i get this: ID=1 Balance=18, ID=2 Balance=18, ID=3 Balance=18. How can I get my qry to update properly. I would like for this to function like this. ID=1 Balance=18, ID=2 Balance=16, ID=3 Balance = 14. Thanks for the help

 

[gemma-the-husky]

in general you dont need to bother with a running total

its more:

take all the movements between 1st sept and 30th sept

the opening balance is 20
the movements add to -6
so the balance is 14

in these terms, this can then get even easier - just treat the opening balance as another transaction of a different type

then the sum of all the transactions between 1st Sept and 30th Sept is 14

--------------
doing it THIS way means its easy to extract all sorts of things.

eg extract all balances as at a certain day
extract all items with 0 balance
extract all transactions with a quantity greater than 10

etc etc

--------------
if you need to generate a REPORT, then the report automatically has a property called "running sum", which it is easy to use.

in a form, this isnt the case, since you can re-order/filter the items in a form, so that the running total would become meaningless.

you could still calculate a running total if you really had to, but it needs a bit of work. its better to think around it, and come up with an alternative, if you can

 

[Mr. Hero]

I am lost. I have tried to understand and apply your suggestions. I am not sure how I have managed to digress from my original path but I have. In fact, I have deviated significantly. I see what your pointing towards, somewhat, but adding 20 to -6 to get my answer doesn't make sense. Since the entry is input as a positive 2. Ultimately, I was hoping to accomplish this without getting to confused on theories and practices.
Sorry Dave, I appreciate all your comments and suggestions, but I am even more confused about this then before. I will try to start from another point in my development of this confusing structure. I think that a re-structuring of my information will allow me to create some queries that will produce what I am after.

 

[gemma-the-husky]

sorry, if this is confusing you

dealing with movements in two directions needs a business decision.

lets say its inventory/stock

so stock coming in is a plus
stock moving out (sales) is a minus

so if you store purchases as positive, and sales as a negative value then you can just sum all the values easily to get a (net) value. However if you store every transaction as a positive. then you need to add some and subtract some at some point, so you need additional processing.

the first method is more logical, imo.

then you can just have a single transactions table, that stores the true movement sign (ie plus for stuff coming in, and minus for stuff coming out) and just have a transaction type lookup field that identifies what sort of transaction it is.

if you go the other way, you either end up with multiple transactions tables, or a table where you have to reprocess the entries to separate positives and negative movements - and then you can end up with union queries that are non-updateable etc etc - its just more complicated doing it that way.

-----------------
its probably best going back to whatever you are comfortable with - and we will try and help you with that.

 

[Mr. Hero]

Dave,

Thanks for the analogy. I am following a little better now. In some ways, you have hit the nail on the head in what I was trying to understand. With this being said, I believe that I need to break down my confusion into manageable parts and try not to do all of it at once. On a previous post, I stated that I had made a qry to do some calculations. After a little thinking I am sure now that I am going down the wrong path....

What is the best practice in this: A input form is attached to a table, There is another table which holds the balances. How can I get my Balance table to update as an entry is added to the input table?

 

[Mr. Hero]

Would this process have to do with Normalization?