Calculated field based on a query

[broodmdh]

We have several fields that should contain calculated dates, based on a value in that table and dates stored in another table. Is it possible to use a query to update these fields when the value is updated or dates are added? A single user will have one or more date ranges in the secondary table, and the sum of those ranges are applied against the value in the originating table to give the results for the calculated fields.

[June7]

I suspect Yes but why? Just build query and do the calcs in query. Not sure what you mean by 'sum' of date ranges. How is a date range summed? Really need better understanding of data structure to advise.

[broodmdh]

The date ranges indicate terms of service. Most people will only have one (with no end date if a current employee) but some could have several. The calculated dates indicate milestones (anniversary dates etc). Can I set a trigger that gets fired when a date range is added to the table? Ideally the dates will update without direct human interaction. Do I have to tie a query to a form? I'm not very familiar with the Access model, so please forgive any ignorant questions.

[June7]

Yes, entering data is an event and events run code. Do not have to tie query to form but probably best. Users should not work directly with tables and queries.

Conventional wisdom is to not save calculated data - calculate when needed.

[NTC]

if you need to store the prior dates - then you have Query 1 that does your calculation and append it to the table. But if you are over writing the prior data - then you should follow J7's advice and abandon fields in the table and instead rely on display fields (unbound) in forms/reports. For instance my bound field can be 1/1/2016 and the displaying fields can auto plus/minus days on that date and they will change immediately upon a change of the bound date field. Look up the DateAdd function for the syntax to put into the unbound field.