Looking up last price paid....

[Guest]

Greetings. This is a difficult Access problem that I really need some help
on. I have a table with a column for part numbers, a column of dates, and a
column of unit costs. I am trying to write an expression (?) that will scan
the first column (of part numbers), then the second column (dates) and tell
me for each part number in the column, based on the date, what was the last
price paid for it. Is this a V-Lookup, or a D-Lookup, or something else?
Thanks.

 

[Nikos Yannacopoulos]

Neither a VLookup(which is an Excel function, not an Access one), not a
DLookup... just a Totals query. start making a query in design view, and
add the table; get all three fields down to the grid, so they are
displayed. Then click on the Totals button (Greek Sigma) in the toolbar,
or go View > Totals from the menu; a new row will be added in the grid,
headed "Total" and defaulted to Group By in all fields; just change the
setting to Max under the date field, and the job is done!

HTH,
Nikos

 

[Jeff Boyce]

It sounds like you could use a Totals query to find the most recent date for
each part number (GroupBy part number, Maximum date). You could then use
the results of that first query, joined back to the table on part number and
date, to get the unit cost on that date for that part number.

The situation in which this approach breaks down is if you have more than
one unit cost for a given part number on a given date.

 

[Guest]

Thank you both for your help. Using the MAX feature worked great.

 

[Peter J. Veger]

give me, for each part, the price
on the date that was the latest among all dates --with a price-- for that
part:

SELECT tb.aPart, tb.aPrice FROM aTable AS tb
WHERE tb.aDate >= ALL (SELECT aDate FROM aTable WHERE aPart=tb.aPart);