udate Query

[roley]

I am trying to write a update query for our rental equipment business. In my rental Table I have a primary field[rental number] then [stock number], [date in], [date out] [date2], and [hours]. Every month i want to update my Inventory tables fields[new hours] and [date of new hours] with [hours] and [date2]. It wouldn't be a problem to do it but alot of times a piece of equipment goes out 2 to? a month. When I try to run a update query it does not take the oldest record and store the information. Is it a sorting thing?

 

[dcx693]

You could run a totals query first by [stock number] if that's how you identify each piece of equipment. Use that totals query to find the oldest record, save it, then use that to feed into your append query.

 

[roley]

Some months I will have 200 recods that will need to updated. I really don't want to go thru the process of picking out the oldest one. I haven't tried a total query unless it's in with a duplicate query. I will try it. But you said append? I don't want to add to the inventory but replace the current data in the fields.

 

[dcx693]

Oops, I meant update query, not append. If you first create the query that finds the oldest records, you don't need to run it each time you run the update query. You just need that totals query to find the oldest record to feed into the update query.

The problem is that you can potentially have multiple entries per piece of equipment per month. You have to somehow find the oldest one. That requires some type of grouping based on age. You need a totals query for grouping, and you can probably use the Max or Min function to find the oldest record.

Once you've got the oldest records, then you can design an update query and base it off the totals query.