Limiting calculations using dates

[Ben Feingold]

I have several databases:

Behavior - Which collects behavior problems.

Attendance - Which collects attendance information.

Level Data Sheet - Which complies them to determine a "level rating" (1-3)

Currently when the Level Data Sheet does all it's calculations, it is looking at every record ever entered. I would like for it to only look at the last two weeks of data (10 school days).

Thoughts?

[transpower]

The calculations must be restricted to the last two weeks or 10 school days. You could define additional fields which are equal to the behavior or attendance fields but are null when the records are older than two weeks. Then calculate based on the new fields.

[QuinTech]

Hi, Ben. Assuming you have a date field, create a calculated field:

( Status ( CurrentDate ) - theDateField ) <= 14

This field will be equal to 1 whenever the date is less than or equal to 14 days old. Now create another calc field which equals 1. If you make a relationship between this field and the first field i described (in the same file), only dates from the past 14 days will be returned by that relationship.

If you then change your calculation from using thisField to using theNewRelationship::thisField you will be able to restrict your set as you require.

HTH,

Jerry