[AccessD] Query question ... Finding the proper rate/per date

I do something exactly like to find an employees current pay rate.  I only
store the effective date of the new pay.
I then use a TOP 1 to find it.

The only difference I would make in the query below is <= rather than just
less than because you want the new rate to show on whatever day it takes
effect.

Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Stuart McLachlan
> Sent: Monday, November 02, 2009 3:10 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query question ... Finding the proper 
> rate/per date
> 
> 
> Use TOP 1 for this.
> 
> Something like
> 
> SELECT TOP 1 Rate
> FROM tblMilageRates
> WHERE EffectiveDate < [Enter Date] ;
> ORDER BY EffectiveDate DESC
> 
> -- 
> Stuart
> 
> On 2 Nov 2009 at 14:14, John Clark wrote:
> 
> > So, what I figured on doing...and please correct me, if I 
> am going about
> > this the wrong way...is to have a separate table for 
> mileage rate. It
> > would have 3 fields, a record ID (probably unneeded), 
> effective date, and
> > the rate for that date. I then want the program to look at 
> the date of an
> > entry, and match it up w/the proper date from this table.
> > 
> > I know this doesn't work...already tried it...but here is 
> basically what
> > it would be:
> > 
> > Max( < [Enter Date]) 
> > 
> > The entered date does indeed bring up the last date & rate 
> entered, but it
> > brings up ALL previous entries. So, if I could get the max 
> date, it should
> > be the last one...correct? But, this is not proper code.
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>