Query Building - Seems simple...

By garfonzo ·
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!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

nested query

by john.a.wills In reply to Query Building - Seems si ...

select ProductID, ItemPrice, DateOfOrder
FROM Products LEFT JOIN OrderDetails ON <what?> LEFT JOIN Order ON <what?>
WHERE ItemPrice = min(SELECT OD.ItemPrice FROM OrderDetails OD ... WHERE OD ... well, I think you get the idea, and I am too fuzzy at the moment to think it all out.

Related Discussions

Related Forums