Expression and Query questions

D

dticks

I have query that takes the Date entered and adds a number of set number of
days from another control. Is there a way to store this calculation in the
main table.

Secondly how can I query this calculated field to only give me most current
dates of the records?

example

CAL_2 has several calibration dates. How can I query for the most recent?
 
S

Shawn Sumpter

Change your query to an update query to store the calculated number to a field.

At this point the "calcualted" number is real date in your table so you can
sort in decending order to see the most recent first.

Depends upon what you are using this database for. It sounds like you may
be setting up something similar to one of mine. If not ignore this.

I have a master table of sites that are inspected. It has a related table
that records the history of inspections or actions such as submission
deadlines. Each history record has a due date and a complete date. This way
if I need to send delinquency letters or inspection reminders for schedules I
can query for only those with uncompleted deadlines.

Shawn
 
F

fredg

I have query that takes the Date entered and adds a number of set number of
days from another control. Is there a way to store this calculation in the
main table.

Secondly how can I query this calculated field to only give me most current
dates of the records?

example

CAL_2 has several calibration dates. How can I query for the most recent?
Click to expand...


Do not store calculated data.

As long as you have stored the [Original Date] and the [NumberOfDays]
there is no need to store the calculated date. Whenever you need the
calculated date, simply re-calculate it,
NewDate = [OriginalDate] + [NumberOfDays]
or in a Query
NewDate:[OriginalDate] + [NumberOfDays]
 
S

Shawn Sumpter

Do not store calculated data.
Click to expand...

A sound practice I agree. There can, however, be exceptions.

If there is a need to keep history over a long period of time a user may
have to choose between storing the number of days or storing the calculated
date.

Why? What happens if your perameters change over time? If you haven't
stored one or the other your history becomes invalid.
 
Top