Gary Kjos
garykjos at gmail.com
Wed Jan 10 15:13:09 CST 2007
Glad it worked for you. It's kind of an odd concept, at least it was for me whe I first started working with Data Warehouses. Different from trying to minimize data duplication and more joins than you might otherwise need. But it has huge speed benifits verses the other ways of going about it especially when you get more records involved. And there is lots of flexibility for dealing with exceptions and having lots of different groupings of things. We recently switched to a 4-5-4 week accounting period calender here and using the date table made that a snap. (We have 4 weeks in the first period of a quarter, 5 weeks in the second period and 4 weeks in the 3rd period. All weeks start on Sunday and end on Saturday.) It is a bit of a pain to set the calendar originally but once it's done - and you can set it years into the future - you are good to go for a long time. And if you need to group things in a different manner, just add another field to the date table, initialize it and you are good to go. GK On 1/10/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > You are right. I setup both ways. The "Between" method took about 3 > minutes to run the query. Your way took about 20 seconds. > > 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 2:22 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Date Compare > > Yes, that is correct. That's the way it would be done in a typical > data warehouse. > > GK. > > On 1/10/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > > So you are basically saying to setup a table with 365 records, per > year. > > Field1 = (every day of the year), and field2 = the associated paydate? > > > > 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 12:08 PM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Date Compare > > > > Whatever works but I think you are making it harder on yourself than > > you have to. You don't need that between. Make a table with one record > > for each date, keyed by that date and then have a second field for the > > beginning of the weekdate or the end of the week date or whatever you > > want to really track.....You have to set it up one time but you can > > then use it over and over. I can send you a table offline with a bunch > > of dates in it if you would like. > > > > GK > > > > On 1/10/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > > > 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 > > > > > > On 1/10/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > > > > Hi all, > > > > > > > > > > > > > > > > Anyone have a solution for how to link two tables with different > > > dates? > > > > > > > > > > > > > > > > I have a Payroll Paid table, with field: DatePaid > > > > > > > > > > > > > > > > I also have a Timesheet table, with field: TimesheetEndDate > > > > > > > > > > > > > > > > The DatePaid field is a bi-weekly date. The TimesheetEndDate > field > > is > > > > usually a weekly date, but occasionally there is a supplemental > > > > timesheet date. What I am trying to do is link the Timesheet > table > > > for > > > > all dates that fall within the 13 days prior to, and including, > the > > > > DatePaid. > > > > > > > > > > > > > > > > So, for DatePaid of 11/29/06.....I want to link all Timesheets > with > > a > > > > TimesheetEndDate falling 13 days prior to, and including 11/29/06. > > > > > > > > > > > > > > > > Anyone have a solution for that? > > > > > > > > > > > > > > > > Thanks kindly, for any help. > > > > > > > > > > > > > > > > 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 > > > > <http://www.rtkl.com/> > > > > > > > > > > > > > > > > -- > > > > AccessD mailing list > > > > AccessD at databaseadvisors.com > > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > Website: http://www.databaseadvisors.com > > > > > > > > > > > > > -- > > > Gary Kjos > > > garykjos at gmail.com > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > Gary Kjos > > garykjos at gmail.com > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > -- > Gary Kjos > garykjos at gmail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com