Keith Williamson
Kwilliamson at RTKL.com
Wed Jan 10 14:44:12 CST 2007
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