Using a Query to Set a Field equal to a Sum

[farner]

I'm using Access 2007, and I want to calculate the sum of a field in one table and store that sum in another field in another table. I don't expect the original field to change much so I don't want to use a query just to calculate the sum and display it, I want to actually store it and display it whenever I want. Something similar to what you would do in Excel, except with sums from different tables. And if the original field does change, I want the sum to also update and the field in the other table to update as well.

So, to simplify, here is the above broken down:

I have Table1 and Table2 and Field1 and Field2.

Table1 --> Field1
Table2 --> Field2

I want the Field2 = Sum of Field1. I've read everywhere it's better to just calculate the number rather than store it in a table, but I don't need the number to be recalculated since it will most likely never change.

 

[plog]

I don't need the number to be recalculated since it will most likely never change... ...And if the original field does change, I want the sum to also update and the field in the other table to update as well.

If the calculation was to never ever never change I'd say its probably not a good idea; since the calculated value can change, its a bad idea.

What you gain by not storing it is accuracy--when you use a query to SUM it the value will always be correct without you having to go through the extra steps of recalulating and restoring it. What are you hoping to gain by storing it in a table as opposed to calculating it in a query?

 

[farner]

@plog

Thank you for your reply. I understand the recalculation and accuracy bit, but the reason I want it in a table is that my DB isn't very formal and at times I just add records right in the table rather than use the form. I want to be able to see the calculation in the table with the record. This is, however, the first time I've ever used Access, and I might be wrong by assuming it's as simple as Excel.

My database is for a school that involves Students, Faculty, Parents, and Tuition. What I have is the Students listed under their Parents (they register as families), and a Family's Total Tuition is the sum of the students' individual tuitions (minus any discounts given). Then there is a list of payments, which is summed up to give the Total Payments, and then the Balance = Total Tuition - Total Payments. I'm still working out the quirks as to getting the Parents to show up in the Tuition table when I register a new family (they show up normal in the Parent's Table, with a (+) to show the related students, but the Tuition Table doesn't show the parent unless I manually put in their ID). The Tuition table is supposed to be the exact same as the Parent's table, except it shows the list of payments and the dates rather than students. I'm a noob so I might be trying to do too much, but I appreciate the help if anyone is willing. Thank you!

 

[plog]

I want to be able to see the calculation in the table with the record

Access as a whole is perfect for this type of work, however, tables themselves are not. It sounds like you just need to learn more about Forms and the functions available in Access. In a very high level overview, to accompish what you want, you would put an unbound control on your form whose source is a DSum function that uses criteria to calculate just the records you want to calculate.

The good news is that from your table descriptions it sounds like you have a good grasp of what a properly laid out database structure is. They key now is to build your forms to take advantage of that structure. For example, the manual insertion of the parent's id into the tuition table can be overcome with a parent's form that has a tuition subform on it to track all the records in the tuition table that relate to a specific parent.

I know this sounds like a lot and theres a lot of jargon in my answer, but I think you can tackle each issue you have. Take it one problem at a time, one part at a time. Start with trying to display the total for a record on your form in the manner I described above (Using DSum). Post back here whenever you get stuck on a particular part.

 

[farner]

Alright, so I took care of the sum issue by using queries to calculate the sums and unbound controls in my report to perform the Total Tuition and Balance calculations. Those will show up each time I generate a report.

Now what I'm trying to do is have the list of payments show up in the Tuition table, under the Parents. So what I have is a Table for Parents with the students underneath, and a table for Parents (named Tuition) with list of payments underneath. The list of payments are in a separate table (Payments) and so are the students (Students). I have a form that allows for the input of the parent information, with a subform in it for inputting student info, linked to the main form through the parents (Guardian ID). That works fine. What I'm having an issue with is including a second subform, which allows for input of payments. I want the payments to have the same Guardian ID as the current Parent record, as well as for them to show up under Tuition, which also has the same Guardian ID as the parent record.

What I did at first was put a subform for payments (continuous) inside a subform for tuition, and then plugged that subform into the main form. I linked the Payments and Tuition forms with the Guardian ID, and the Tuition and Main form with Guardian ID. The first time I did it, it worked great, except it wouldn't allow me to create any new Parent records. As I was trying to fix that (I just needed to select the Cascade Update and Delete options in the relationships - but I didn't find that out till later), I tried to replicate what I had done the first time, but it didn't work. Now I can't do it at all anymore, and it's killing me. Everytime I input info for the Parents, Students, and Payments, the Parents and Students comes out fine, but the Tuition and Payments have no Guardian ID and are not linked. I don't know what to do. Help