I dont see why you need 3 tables at all... All this can be done with one table from what you have stated. All you are doing in reporting. You dont have to create different table to report the same data. All you have to do is find the set of records you want in a range and then report on that found set. IF you do not want to type in a date range when finding records such as 1/1/2009 ... 3/31/2009 ( 1st Qtr ), then you can also lookin into creating calc using the function WeekOfYearFiscal or WeekofYear along with the Year () function.
Thanks for the quick reply mr_vodka.
While I was in the shower tonight it dawned on me. I realized that since my reports are all based on weeks, I could just make each record contain that week's stats. No need for a DAILY or CALENDAR table at all. No relations whatsoever. Maybe I was over-worried about normalization. I will try this at work tomorrow. Though it sounds like an extremely complicated table. One record will have to contain 12 numbers for each of the 7 days of the week, plus totals and averages, plus a summary field and also all the FY-Q-W data.
If anybody thinks I am going overboard in consolidating ALL my data on one record, please warn me now.
You are being warned. By putting all of a week's data in a single record you are making it impossible to produce a report by month, quarter or year (none of these respect the weekly cycle) or by any other arbitrary period. You won't be able even to find records of January 2009 or records between Jan 15 and Feb 15, 2009, for example.
I don't see why you need to keep records of your reports - they are easily reproducible from the original data. As mr_vodka said, all of your reporting can be done from the same data table. All you need is to add a few simple calculation fields to serve as the breakfields for week, month, quarter, year, etc.
If you are actually 'printing' reports, consider the subsummary feature when editing the layout. You can use it to group your daily records into weekly, monthly, quarterly, etc, by creating appropriate calculation fields that determine the week, month, quarter, year, etc of the individual records. If you are just viewing the data, create static tables, one for weeks (and create 52 records, with ascending week number fields), one for months, one for quarters, etc. You could even get fancy and add a Year field, so you can view the aggregated numbers for previous years.