[AccessD] Using Dates

Gustav,

In my estimation, the simplicity and flexibility that the Date Dimension
table provides will outweigh its drawbacks for all but the most basic of
date query tasks.

For example, with a Date table, it would be possible to write a single
parameterized query that would provide answers for a wide range of date
related questions, like:
 * All January sales in the table
 * All sales for weeks with a holiday since 2000
 * All weekend sales for June, July and August of 2002
The results could easily be returned an any text or numeric date format
desired.

While you could do all of this with date calculations and the Format
function in one query, it would be very complex, and the computational
overhead would likely be prohibitive. You'd probably be forced to break it
into smaller queries, with the added maintainance that entails. Alternately,
you could build a custom WHERE clause for each question, but you've only
moved the complexity from the query to code.

It also shines when you need to calculate working day intervals, as
mentioned. I wish I had known about Date tables when I was developing a
Purchase Order Expediting module, which needed to know the number of working
days between the Order Date and the Required date. Using a properly setup
Date table would have simplified my code, and provided better results.

-Ken


-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk]
Sent: Friday, January 30, 2004 4:53 AM
To: Access Developers discussion and problem solving
Subject: Re: [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