Last Price Paid Query

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I hope someone can help me out. I have an access
purchasing database and I'm not having any luck pulling
out of my data the last price paid for the same item,
which may be less or more than a previous order, so I
can't get there by the last PO# or by the last date
ordered and if I use "max" I get the highest price paid.
Any helpful suggestions for an expression I should be
using?
 
That would work if I only wanted to query one item, but I
want to query all items for the last price paid so that
staff have a complete list of products to order from. I
do appreciate the quick reply though. This is only the
second time I've posted and the first time no one replied
to my question. So thank you!!!
 
Dear Christine:

Using a subquery returning the date of the "last order" (by which I
take you to mean the one with the greatest date) filtered to the item
in the outer query, you can limit the rows to those with the most
recent date.

If you don't know how to do this (and if you did, you probably
wouldn't be asking) please post a query that returns all the necessary
information but which return all rows, not just that for the last
price. Please point out which column(s) identify the item and which
column gives the date from which you want the most recent. I'll
modify that to give just what you want (hopefully!)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Then just do an aggregate query, grouping on item, max date for each item.
This can then be related to a table with the item, date (relate on these 2
fields) and price paid, yielding a list of items with the last price paid.
That sounds about right.
 
Back
Top