Gary Kjos
garykjos at gmail.com
Mon Nov 2 13:42:21 CST 2009
Can you put a beginning and ending date in your rate table? For the most recent one put a future date in it maybe? Then you could do a query on the rate table where your desired date is between the starting date and the ending date. I have a application where I do an update query on a transaction table to set "price point groups" and I do a cartesian join - putting two tables in the query without a join between fields on the two tables, and on the unit price of my transaction table I have a criteria of "between [PricePointLowPrice] and [PricePointHighPrice]" Those fields being in a price point ranges table. I thn update a "PricePointGroup" field in my transaction table with the record ID of that price point range table. It works fabulously. GK On Mon, Nov 2, 2009 at 1:14 PM, John Clark <John.Clark at niagaracounty.com> wrote: > Hello...been a long time since I've asked a question, but I am now trying to do something, that I've tried before, and I never could get it to work. I always find a work-around, but I want to actually do it right this time. > > What I am trying to do is this... > > I am creating a small program that creates witness vouchers for our District Attorney's dept. When a witness testifies in court, there is a flat fee of $15, and then mileage is added to that. The mileage rate can change, every 3 months, so I want the program to find the correct mileage rate, as per the date entered (the court date). Not only will this keep me from having to change the rate, every 3 months, but it would also allow the program to reprint any voucher, at any time, and keep its original rate. > > 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. > > This is a really small DB, but this one item is killing me. I'd like to use it for the flat fee table as well...it hasn't changed in a long time, but you never know. > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com