Using an update query to create a "totals" table

[mike_980]

Hi guys, not logged on in a while!



I am currently creating a database for my dads farm which will manage the amount of corn we process. The tables at present include one for individual loads of corn we lead in from fields (tbl_crop) and one for storing bulk loads which we buy in from other farms which will be added in bulk, for example 100tons from X supplier (tbl_bulk) along with some other info.

I have the forms created and working well for adding the info to the tables and all is well, each load has it's own respective weight which is stored separately in the appropriate tables. What I want to, and is the only way I can think of, is to create a separate table, which is updated with an update query and acts as a live view of how much grain we currently have. There will be forms added in the future which will remove corn from the store when we sell it So the database needs to know how much of each type of grain is in the store at anyone time. It obviously isn't just as simple as adding up all the loads as is is getting added and removed from the store all the time so we need a "current" value for the weights.

If I didn't explain it very well there then this may help,

The inputs in basic terms are the loads of corn we bring into the sheds, and the loads of corn we remove from the sheds.

The output will be a value on how many tonnes we have of each grain type we have in stock.


You may be thinking that excel would have been better but the system I am designing will give reports also on what each field has yielded, how much was sold to each customer etc etc so access was the choice I made.

Any help would be much appreciated!

Using access 2013

[mike_980]

Just been looking, could this be done with an SQL Update statement?

[June7]

Saving cumulative aggregate data is tricky and risky. It is disconnected from the raw data that produced the values so they are difficult to verify, there is little or no accountability for the data.

Conventional approach would be to have transactions of incoming and outgoing then calculate the net difference to determine balance whenever needed. Don't you have that with "The inputs in basic terms are the loads of corn we bring into the sheds, and the loads of corn we remove from the sheds."?