Store calculated field in table (Access 2002)

Topic

New Reply

I would like to store a calculated value in a table. How is this possible?

tblDiscretionaryDetails stores the following information:
CountyID, EventID, DiscretionaryAmt

I want tblCounties.discretionaryexpenditures to be the stored calculated sum of all the CountyID’s and DiscretionaryAmt’s from tblDiscretionaryDetails.

tblDiscrtionaryDetails could contain a couple countyID’s for each eventID. Which means that over several eventid’s, there will be repeats of countyid’s so I’m looking to group on that county ID and keep a sum stored in the table as it will be used for other calculations. I know how to make a qry that works but how do you store back to the table And will it be continuously updated?

any comments on this is appreciated.

Thanks. Jenn

Reply | Quote

Replies

Hi Jenn

By definition you cannot have calculated fields in a table, but you can create calculations in a query. It is very difficult to follow your written example as you cannot sum a primary key as the answer would be nonsense , however you can count the primary key (I am assuming CountyID is the primary key of the table).

Can you add a cut down version of the database to your reply to this or at least a screen dump of the relationships to assist how you have created the relationships.

If you do not want to do this, reading your last comment in the last paragraph, you can create a make table query to do updates but I think this method is a sledgehammer to crack a nut so I feel we need to review the database schema

Reply | Quote

I apologize… County ID is not the primary key… I didn’t include the primary key in the post as it’s an autonumber and irrelevant to the point. I was looking primarily for a counter reason to my approach, which you provided! I thought I would try and store the calculated field in a table rather than setting up a qry and rpt to pull the data when I need it. But I suppose I will have to. I built a qry which sums the discretionaryamt field by grouping on countyID and yields an accurate result.

Anyway, You can view the relationships in the attachment… This is the first stage of a complete overhaul and redesign of a database that was used for years… We are adding new features and capabilities and ironing out some limitations that were built into previous versions. Any suggestions are welcome.

Thanks much for your response.

Reply | Quote

When I open the snapshot file in your attachment, it is blank except for the text in the upper left corner [Relationships for PAC07-08(2)]

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Best I could do for right now is a printscreen of the relationships…

Hold onto this Hans… I’m sure I’ll be posting other questions…

Thanks…

Reply | Quote

I have to apologize, I somehow missed the second and third page in your snapshot file. Now that I saw them in the PDF file, I went back and discovered them in the snapshot too.

Reply | Quote

But now that I see the relationships, I fail to see tblDiscretionaryDetails. Must be my old eyes…

Reply | Quote

Be glad It’s not your eyes Hans!

Here’s the accurate relationship view.

Again, I’ll take any suggestions…

Jenn

Reply | Quote

You can create a query based on tblDiscretionaryDetail.
Add the CntyID and Amount fields to the query grid.
Select View | Totals or click the Totals button on the toolbar.
Set the Total option for Amount to Sum (the one for CntyID remains Group By, the default setting).
This query will return the total amount for each county. Save it under an appropriate name, e.g. qryAmountPerCounty.

You can use it whenever you need this amount. For example, if you need it in another query, add qryAmountPerCounty and join it to another table on CntyID.
Using a query guarantees that the total amount will always be up-to-date; if you stored it in the tblCounties table, you’d run the risk that the amount would lag behind the actual situation.

Reply | Quote