[AccessD] Using Dates

Hi Robert

> I have been seeing a lot of people posting lately in many of the lists
> about how to do different tings with dates.  Here is what I do.  I have 
> what is called a Date Dimension table.  A dimension table is one used in 
> data warehouses.  In this case, it is great for relational data also.  The 
> date table has all of the things done to a date that can be done.

Yes, Charlotte has mentioned this before and we had a thread touching
this (Calculating days, July 2003).

The main reason for reluctancy to follow your method would be the
large amount of redundant data and the fact that Access is pretty fast
in date operations using the built in functions except Format() which
is slow. Thus, it may be faster to use Month() than to establish a
join to a another table to look up the month of a date. However, there
may be situations where that could be advantageous but I can see no
reason to store both the month number as well as the string value of
the month number.

The example Charlotte mentioned was (I think) storing the holiday
flag. Indeed, what should be stored in such a table should be values
that can not - or only with some difficulty - be calculated like
banking days which mostly follows days not being weekends/holidays but
not always.
Another example is working days. It could be useful to include field
like

  Workingday_of_Month
  First_Workingday_In_Month_Flag
  Last_Workingday_In_Month_Flag

Also, I would suggest flags stored as Boolean.

/gustav
  

> Here is 
> a sample structure:

> Date_ID                 Date/Time (loaded using the date() function)  PK
> Year_Nbr                Number, integer
> Year_Txt                Text(4)
> Month_Nbr               Number, byte
> Month_Nbr_Txt           Text(2)
> Month_Name              Text(15)
> Month_Name_Abbrv        Text(3)
> Day_of_Month_Nbr        Number, byte
> Day_of_Month_Nbr        Text(2)
> Day_Of_Year_Nbr         Number, integer
> Week_Of_Year_Nbr        Number, byte
> Quarter_Nbr             Number, byte
> Quarter_Nbr_Txt         Text(2)
> Fiscal_Period           Text(10)
> Weekday_Flag            Text(1)
> Holiday_Flag            Text(1)
> Last_Day_In_Month_Flag  Text(1)

> By using this table joined to the date, you do not have to do any functions 
> against a date to filter by month, your, quarter, or whatever.

> If anyone has questions about this, just send them to the list and I will 
> answer them as best I can.

> Robert