Query to expire old records

[blakebry]

Hello
I would like some suggestions on how to setup a query to add and expire records.

My table would be setup like the one below
Origin - Destination - Rate - Valid From - Valid To
USA Germany 400 4-25-09 12-31-9999

As i get new rates i want the query to add the new rates to the table and if the query finds a duplicates expire the old rate based on the new valid from date.
If it would be easier to add the records and then perform some expiraton function that would be fine as well.
Thanks for any suggestions.

 

[lameid]

You have a two step process... An update query to 'expire' the old record and an append query (insert into) to add the new record.

My inclination for this sort of thing is to use null for the valid to field but using a value of 12/31/9999 works to.

If you know the rate you are adding (i.e. entered on an unbound form), I would have a query update the valid to based on criteria for valid to of whatever your never expire value is (null or 12/31/9999) and the rate (however you uniquely identify it). Then Append.

 

[GingerR]

I agree with lameid. So your new records would look like this:

Origin - Destination - Rate - Valid From - Valid To
USA Germany 400 4-25-09 5-01-09
USA Germany 500 5-02-09

Then when reporting, you just select the item where "Valid To" is null. This way you retain history, and don't have to 'expire' anything to another table or anything like that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html