Access Query Question

[Guest]

This may seem like a dumb question, but I can't seem to figure it out.

I have a database setup in the following fields:
Date, Product, Price, Store Location, Store Name

I need to write a query to find the lowest price broken down by date, then
by product, then by location, then by store name.

So for each date, I want to know for each product in each location, which
store has the lowest price? I tried to use the min, but it gives me the min
for all the prices and will not group it. If anyone can help, please let me
know.

Thanks

 

[John Spencer]

This query might work for you

SELECT [Date], [Store Location], Product, Price
FROM YourTable
WHERE Price =
(SELECT Min(Price) as Lowest
FROM YourTable as Temp
WHERE Temp.Product = YourTable.Product
And Temp.Date = YourTable.Date)

You can also do this query by using two queries. Query one would get the
lowest price per product per date.
SELECT [Date], Product, Min(Price) as lowest
FROM YourTable
GROUP BY [Date], Product

With that as a saved query, you could then build the following
SELECT YourTable.*
FROM YourTable INNER JOIN [Name of saved query] as Q
ON YourTable.Date = Q.Date
And YourTable.Product = Q.Product
And YourTable.Price = Q.Lowest

By the way, Date is bad name for a field, since it is a reserved word. If
you can I would advise you to change the field name to PriceDate or some
other more descriptive name. Date is a function to returns the current
system date.