I track products that my company purchases (around 600 items). I track the information about the product (ID, Name, Supplier, etc) and the information of each order we place for more products. I do all of this in MS Access. Here is part of the table’s basic structure:
Products Table:
ProductID, Supplier, etc.
Orders Table:
OrderID, DateOfOrder (no other fields)
OrderDetails Table:
OrderID (lookup in Orders table), ProductID (lookup in Products table), Quantity, ItemPrice
We like to know the lowest price we’ve paid during the year for each product. That is simple enough with a query. I end up with a query that spits out ProductID with the corresponding minimum price beside it (two columns).
I need to know how to find the minimum price paid and what _date_ we paid that price. I’ve tried building the query in Access as follows:
1st Column–
Field: ProductID
Table: Products
Total: Group By
Sort: Ascending
2nd Column–
Field: ItemPrice
Table: OrderDetails
Total: Min
3rd Column–
Field: DateOfOrder
Table: Order
Total: Group By
With the above setup, it will include duplicate items. For example, if I paid $5 on June 1 for Product A and $4 on March 1 for the same product, I only want the query to show the second price (Product A, $4, March 1). However, it gives me both.
How do I get Access to spit out the associated date with ONLY the lowest price for each product?
Thanks for the help!