Update field when a date is the latest

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am using Access 2000. I have a table that contacts ITEMS purchased on a
DATE at a particular QUANTITY and COST.

I have an empty field UNIT_COST

I would like to update the UNIT_COST field by taking the COST field divided
by the QUANTITY field for each ITEM where the DATE field is the latest date.

So, I want to get the last paid cost per item but only want it for each
unique item where the date is the latest.

The table contains duplicate items because we buy the same items on many
dates.

Can anyone help me with the query to do this or the sql code to do this?

If I try to form an update query in access, the totals field that lets me
choose the MAX and GROUP BY is greyed out.
 
Steve,
I see you say you buy an item on several dates. Is it possible the same
item could be purchased more that once on the same date?
If not, then you may try this approach

1. Instead of an update query, use a select query to find the row you want
to update. It would Group By Item and Max by Date
2. Load the fields into variables
3. Do the math
4. Use an SQL statement to update from the variables
 
Back
Top