Handling Fiscal Years and Dates in Db

[ccepaulb]

Hello,

I am building a Db that will report against individual transactions. Each transaction will be on a specific day.
Now I will need to slice and dice the data in a number of different ways depending on what the end user wants to see.
Therefore I need to build a date table that will aggregate all the individual days by Week, Month, calendar year, fiscal year...etc.
Being a rookie, I was simply going to build a table that has all the days for the last three years and then the corresponding weeks, months etc in columns to the right.
Because of this design, I will have over 1,000 records. Could I do it differently to keep the size of the table down?
For example use ranges as a cell value (Our week 1 is named "Feb 1st Wk", it is 1/30/06-2/5/06 for fiscal Yr 2006, 1/31/04-2/6/05 for fiscal Yr 2005...etc)

Any advice on this?

Thanks, Paul

 

[ccepaulb]

Added Picture of Date Table

Here is what I built, not sure if I'm off base or if there is a better way I could do it.

See screen shot of part of table attached

Thanks, Paul

 

[MStCyr]

Hi

I just finished a project similar to that ... I used a date range for the date data ie. Apr 16-20

 

[ccepaulb]

Date Range

So how was that formatted?

Did you just have for example 11/1/04-12/5/04 in that field for each record?

-Paul

 

[MStCyr]

So how was that formatted?

Did you just have for example 11/1/04-12/5/04 in that field for each record?

-Paul

The data was formated exactly like mentioned before: Apr 16-20

Maurice

 

[MStCyr]

it's just a text field