[AccessD] Re: DatePart Question

Gustav,

Your example would not return the same data Charlotte's would.
You would return all months.  Hers would only return May of
the years specified.

A date and/or date/time dimension is essential in data
warehousing, period.  As for the use in a transactional
system, personal preference.  As for the use in reporting
systems, it is a really good idea.  That is what data
warehouses and data marts are for and where the date
dimension shines.

Robert

At 04:29 PM 2/4/2004 -0600, you wrote:
>Date: Wed, 4 Feb 2004 20:44:04 +0100
>From: Gustav Brock <gustav at cactus.dk>
>Subject: Re: [AccessD] Re: DatePart Question
>To: Access Developers discussion and problem solving
>         <accessd at databaseadvisors.com>
>Message-ID: <3945400171.20040204204404 at cactus.dk>
>Content-Type: text/plain; charset=us-ascii
>
>Hi Charlotte
>
> > You're way off, Drew. <G>  The reason for those divisions is because
> > data warehouses are used to query data, period.  So the user wants to
> > see all fact records within a particular month for the past 5 years.
> > The point of a dimension table is that you don't need to do any date
> > calculations in the query.  All you need do is specify the month number
> > (no function required), and the year number.  The join filters out the
> > appropriate records.  You need do no date math at all to filter the
> > records because any date math was done when the dimension table was
> > created.  Since SQL is much faster than code, why would you expect this
>
> > Month([MyDateField]) = 5 And Year([MyDateField]) BetweenYear(Now())-5
> > And  Year(Now())
>
> > to be faster than this
>
> > [MonthNo]=5 And YearNo Between 5 And 10
>
>Because of the missing inner join, of course. Also, for this example,
>you could state:
>
>   [MyDateField] Between #5/1/1999# And #5/31/1999 or
>   ...
>   [MyDateField] Between #5/1/2004# And #5/31/2004
>
>But I'm guessing. I neither have a huge set of data to test with nor -
>as Drew - the time at the moment to play with it. So I'll have to rely
>on the opinion from people having experience in this area.
>
>/gustav