Access Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top