[AccessD] Using Dates

Yes, it's a very effective tool for doing date filtering in queries and
reports.  I've used it in data warehouse type applications and also
where users wanted to be able to get results for the last 6 months and
compare that to the same period last year or some similar kind of time
frame slicing and dicing.  The biggest problem with it is creating the
code to keep it updated.  I usually only updated it once a year to add
the new year's values.

Charlotte Foust

-----Original Message-----
From: Robert L. Stewart [mailto:rl_stewart at highstream.net] 
Sent: Thursday, January 29, 2004 11:39 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Using Dates


All,

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.  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