Retreiving info from specific dates

[pcourterelle]

Hi folks...a bit stumped and I hope you can help.

1. The db keeps stats on various items. It is necessary to compare data for the same day from the previous week, previous month and previous year. Not simply the same date, but the actual day is what's important, ie: Monday, third week of Jan must be compared to Monday of the previous week, to the third Monday of December and to the third Monday of Jan 2001.

To keep it simple, consider a db of just four fields to begin with:

Date

Salary - number field

Salary Last Week - calc field?

Salary Last Month - calc field?

Salary Last Year - calc field?

eg:

Salary $250 Date 1/25/02

Salary Last week: $350 Date 1/18/02

Salary Last Month: $275 Date 12/17/01

Salary Last Year: $500.00 Date 1/15/00

Is it possible to retrieve this information from the database using calculation fields only? I can calculate the dates I want reasonably enough but I have not found a way to retrieve the actual data.

any thoughts, hints would be appreciated.

pc

[Rumble Seat]

Spock would say "fascinating." The key is, for the date of every record, calculate the 3 other dates (Same Day: Prev_week, Prev_Month, Prev_Year) and then create relationships between those 3 fields and the main date field, allowing data to be displayed and compared.

Simple design, the trick is to come up with working definitions for those 3 dates.

The first is easy: Prev_Week = Date - 7

Prev_Month and Prev_Year are trickier, because the number of days is arbitrary next to the day of the week. You might use some combination of WeekOfYear, and DayOfWeek.... Let me think about that one...