[AccessD] Re: Using Dates

Hi Robert

OK. I had Jet in mind.

/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