[AccessD] Using Dates

You misunderstood me, Gustav.  I indeed have used something like what
Robert described.  The reason is that the dimension table can then be
used directly as a rowsource for comboboxes, etc. and when joined to
another table on the date key, it makes it very simple to query out
something like the last 6 months without resorting to any kind of date
manipulation but just using the month numbers.  What might not have been
clear is that those month numbers aren't necessarily just 1 to 12.
There's usually also a month number from the start of the table, as
there is a running year number.  It may also include a quarter in year
column and a quarter number from start.  It looks a bit bizarre until
you learn how useful it can be

It is actually a fast technique since it is designed to filter data in
very large tables quickly.  You want the data for last year?  Look at
the date, find the year number and then look all the records where the
year number is 1 less.  You don't have to manipulate dates at all that
way and you can use integer math.  A dimension table does, indeed,
contain "redundant" data because that is the way that kind of table is
designed.  It is not a data entry table at all.  It is strictly a lookup
table and its only purpose is to provide a fast way to return a set of
specific records without doing a bunch of calculations.  I don't build
one for a  hundred year period, because my clients usually want a
specific start date, and I build from that.

Charlotte Foust

-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Friday, January 30, 2004 2: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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com