Update field with calculation based on fields from several records

[AndrewRossington]

Hello all

I hope the title wasn't too confusing. I wanted to summarise exactly what it is I need, but I find it difficult to put into words.

If I may explain:

I have 3 tables - Products, Orders and Deliveries.

They're set up in a one-to many relationship so that One Product has Many Orders, and each One Order has Many Deliveries (With me so far?)

On the sub-form based on the Deliveries table, I have 2 fields relevant to my question - QuantityReceived and QuantityRemaining.

These fields hold exactly what you'd imagine. What they don't do is automatically update the QuantityRemaining field based on the figure in the QuantityReceived field. (That is, every time you create a new record for detailing another delivery, the current QuantityReceived value should be subtracted from the previous QuantityRemaining value and the result placed in the current QuantityRemaining field.

Is this possible? And if so, how do I go about implementing this? (The database has been in use for a while and, as such, the fields already contain data. Is it possible to implement this for any new records created or would it have to be from scratch?)

For your info:

I use Access 2000
I've searched online but there doesn't appear to be any tutorials for this kind of thing.
I have what I consider very basic VBA know-how.

If anyone has any ideas, I'd appreciate it.

Thanks very much for your time.

 

[AndrewRossington]

I don't wish to seem a pest. Can anyone give me some advice on this? Or at least a starting point for my own research into a solution?

Thank you.

 

[alansidman]

Andrew,
There is no reason to maintain a running balance in your tables. This is a calculated field and goes against the principles of a relational database. You can always get this information by using queries and reports to obtain the information.

Data bases should be used for storing raw data, not calculated fields.

Alan

 

[AndrewRossington]

Andrew,
There is no reason to maintain a running balance in your tables. This is a calculated field and goes against the principles of a relational database. You can always get this information by using queries and reports to obtain the information.

Data bases should be used for storing raw data, not calculated fields.

Alan

Hi Alan, thanks for responding.

I honestly wasn't aware that this request was unconventional, however I've only ever worked with one highly customised Access Database and the one I'm creating, so my exposure to them is somewhat limited.

However, if I wanted to do this, despite it being a uncommon function for a database, would you know how I'd go about it? It would be a very useful feature to have on the sub-form.

 

[alansidman]

Andrew,
I've been giving this some thought. I think that you need to have a subform open based upon an underlying query. The underlying query would take its cue from a combobox on your main form that would identify the product in the criteria for the Product Field that you want to have the on hand quantity for. In your query you would need to have Receipts and Disbursement Fields as well as the Product Field. You would then need to have another Field that would be the calculation field. I would put a running sum formula in the expression for that field based upon the receipts and disbursements field quantities. (Do a search in this forum on Running Sums---theads).

I hope this gives you one idea on this could be accomplished. I am sure that there are other possibilities/solutions.

Good luck and post back if you need additional help.

Alan

 

[AndrewRossington]

Andrew,
I've been giving this some thought. I think that you need to have a subform open based upon an underlying query. The underlying query would take its cue from a combobox on your main form that would identify the product in the criteria for the Product Field that you want to have the on hand quantity for. In your query you would need to have Receipts and Disbursement Fields as well as the Product Field. You would then need to have another Field that would be the calculation field. I would put a running sum formula in the expression for that field based upon the receipts and disbursements field quantities. (Do a search in this forum on Running Sums---theads).

I hope this gives you one idea on this could be accomplished. I am sure that there are other possibilities/solutions.

Good luck and post back if you need additional help.

Alan

Thank you for the suggestion.

As I previously mentioned I'm no access wizard. I understand the basics of what you're saying. I got as far as adding the two necessary tables to the query, as well as the fields for Received and Remaining, and the record identifying field, but I'm not sure how I now add another which does the sum of (Current Remaining = Previous Remaining - Current Received), nor how to get the new Remaining figure to add itself to the correct Remaining field in the table.

Some guidance would not be scoffed at

 

[alansidman]

I'm in the midst of a fire drill. I will try to put some information together as I get a chance. For running sums, look at this first, I have another idea but will post it later.

http://support.microsoft.com/kb/101081/en-us

Alan

 

[alansidman]

Here is another solution for the running sums.

You have a list of sums and want to create a running sum inside a query of those sums. You need to have a unique field in the underlying table. Also you can create an autonumber in the same manner as an autonumber field in a table. Please note you must have a sortable unique key to be able to do this.

Here's how to create a Running Sum. Create a new query without any tables in it. Switch to the SQL view of the query and paste the following code in.

SELECT [tbl_Orig].[UniqueKey], (SELECT Sum([tbl_Orig].[FieldToSum]) AS Total
FROM [tbl_Orig]
WHERE ((([tbl_Orig].[UniqueKey])<=[tbl_Alias].[UniqueKey])) AS Total
FROM [tbl_Orig] AS [tbl_Alias];

Before going back to the normal view, change all occurences of 'tbl_Orig' with the name of your table, all occurences of 'UniqueKey' with the name of your unique key in the table and the one occurence of field to sum with the field to be totalled.

Go back to design mode and add any extra fields from your original table you desire. Then run the query

Points to note. Your original table must be sorted by the Unique Key ascending, if it is not or you cannot then create a new query with all the fields in and sort by the Unique Key and then base this query on the sorted query.

Alan

 

[Kavy]

Alan. I was trying to do the same thing. I just want to create a graph with the running account blance. Why is this so hard, it seems like something that lots of people would need?

Why can it be so easy to make a running sum in a sheet but not on a graph?

I used your code, and it works! But when I load it, it asks me for a value for the parameter of my key, do you know why thats happaning?

It doesnt change the numbers based on anything I enter there

Thank you!

 

[Kavy]

ahh I just realized its calculaing a running sum in the table default order (by key) not by date