[AccessD] Re: Using Dates

Gustav,

Since I am using SQL Server for all my BEs, I use Char(1) for the Flag 
columns.  Personal preference since I do not like to deal with 0 and 1 or 
-1.  In Access (JET) itself, I would store it as a boolean.  There are a 
number of other columns that I have in the actual one that I use.  And, I 
actually keep the Fiscal Period in a related table because of the 
possibility of have different departments/companies operating under 
different Fiscal Periods.  I was trying to keep the example simple.

A join is generally cheaper than doing the calculations.  Though that is 
not always the case.  Also, since I do use SQL Server, I do this join in a 
view on the server side and not on the client.

Thanks for adding to my post.  It is great when we can exchange ideas.  ;-)

Robert

At 07:04 AM 1/30/2004 -0600, you wrote:
>From: Gustav Brock <gustav at cactus.dk>
>Subject: Re: [AccessD] Using Dates
>To: Access Developers discussion and problem solving
>         <accessd at databaseadvisors.com>
>Message-ID: <15711059162.20040130115245 at cactus.dk>
>Content-Type: text/plain; charset=us-ascii
>
>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