Need help doing calculations on fields in one table and storing the totals in another

[kellielewis2872]

I am working on a project working with valve recession readings. I have a table that I am inputting the valve readings in. I have another table that has the original valve readings which I need to use to calculate the total recession of the valves and store that value in another table.

1. I am having trouble connecting the tables.
2. I basically need these totals for a report only.
3. I have have tried doing VBA code and can get the totals, but the VBA code gets lost and is not retained after the database is closed even though I save it.

Any ideas of how I can make this work. There are too many fields to put in one table or query.

I have attached a zip file of the database I am working in.

 

[Mr. B]

After looking at the current structure of your database, I would suggest that you take a look at "normalization". In your case it would appear that you have Customers and those customers have "Units". Each of those units have initial data and then test data that is to be compared.

Try adding a "tblCustomers" table and only put information about each customer in this table.
CustomerID as Primary Key
CustomerName as Text
and other fields directly related to the customer as required

Create a "tblUnits" table and only have information about each Unit in this table, but provide the link to the Customer that owns the Unit,
UnitID as Primary Key
CustomerID as Foreign Key to link to the tblCustomers table
Other fields directly related to a Unit as required

Your table that would hold the actual data for the initial unit data would be in another table.
UnitInitialDataID as Primary Key
UnitId as Foreign Key to link to the "tblUnit" table
Other fields directly related to the actual initial data for the unit

Your other tables (test data and totals data) would be constructed in a similar manner.

It is not clear to me if you really need to store the totals. Normally you would simply calculate the totals when you want to display them, however, I have seen some situations where it was appropriate to actually store the calculated values. (If you are using Access 2010, I would suggest that you look at "Calculated" type fields.)

Hope this is helpful.