[AccessD] Date Compare

Keith,

I do not think that was not what Gary was suggesting.

The date dimension table would have a column in it for
PayDate.   Then all of the dates that fall within that
pay period would have the save date in PayDate.  By
doing this there are no between statements required in
the query you are talking about.  Now to load the data
initially, is a different story.

Data warehouses use this technique to cut down on exactly
the type of queries you are doing with the between on
the two tables.  It give a significant increase in
performance.

Robert

At 12:00 PM 1/10/2007, you wrote:
>Message: 8
>Date: Wed, 10 Jan 2007 11:20:29 -0500
>From: "Keith Williamson" <Kwilliamson at RTKL.com>
>Subject: Re: [AccessD] Date Compare
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <B61870319AC0CC42A143BA69E5798D080C0F15 at fenwick.rtkl.rtkl.net>
>Content-Type: text/plain;       charset="us-ascii"
>
>Yeah.  That is basically what I just came up with, I guess.  I created
>another table with BeginDate and EndDate.  Then I am adding the
>"Between" operator as part of my join statement.  It appears to be
>working...but I need to run some more data through it, to be sure.
>
>Thanks!
>
>Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
>
>RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
>21231-3305
>
>410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
>Sent: Wednesday, January 10, 2007 11:08 AM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Date Compare
>
>I would make a table to fit in between that has one field with the
>individual dates and another field with the week ending date. Then you
>do a three table join. It's common to have a Date table in a data
>warehouse application where you have a record for each possible date
>and then have various values for that date set, the Fiscal period it
>is in, The fiscal year week it is in, things like that. Then you join
>in that date table whenever you need some of that stuff and you don't
>have to resort to calculations or formulas. Works pretty slick.
>
>GK