I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item.
Here is a diagram of the tables I'm working with:
My understanding of SQL is very basic, and I tried the following (incorrect) query, in the hopes that it would return only one record per item (because of the DISTINCT
operator) and that it would only return the most recent purchase (since I did ORDER BY [Invoice Date] DESC
)
SELECT DISTINCT ([Food items].Item),
[Food items].Item, [Food purchase data].[Price per unit], [Food purchase data].[Purchase unit], Invoices.[Invoice Date]
FROM Invoices
INNER JOIN ([Food items]
INNER JOIN [Food purchase data]
ON [Food items].ID = [Food purchase data].[Food item ID])
ON Invoices.ID = [Food purchase data].[Invoice ID]
ORDER BY Invoices.[Invoice Date] DESC;
However the query above simply returns all of the food purchases (i.e. multiple records for each record in [Food items]
), with the results sorted descending by date. Can someone explain to me what I am misunderstanding about the DISTINCT
operator? That is, why is it not returning only one record for each item in [Food items]
?
And more to the point - what is the simplest way for me to just pull the most recent food purchase data for each individual food item, given the table structure shown above? I don't really care about efficiency as much as simplicity (the database I'm working with is rather small - it will be years before it's even in the tens of thousands of records range). I care more about the query being understandable for someone with little knowledge of SQL.
UPDATE: So I tried, both of the answers suggested below, and neither of them work (they just throw up syntax errors).
Based on the suggestions below, and further reading online, I wrote the following new query, using the aggregate function max()
and a GROUP BY
clause:
SELECT [Food purchase data].[Food item ID], [Food purchase data].[Price per unit], max(Invoices.[Invoice Date]) AS MostRecentInvoiceDate
FROM [Food purchase data], Invoices
GROUP BY [Food purchase data].[Food item ID], [Food purchase data].[Price per unit];
But I am still having the same problem: that is, I'm still seeing more than one result for each food item. Can anyone explain why this query is not only returning the most recent purchase for each food item?
UPDATE 2 (SOLVED!):
None of the answers below quite worked out but based on some heavy modification of Vladimir's answer below, I was able to create the following queries, which appear to be giving the correct results.
First, I created this view and named it "LatestInvoices":
SELECT InvoicesMaxDate.ItemID, InvoicesMaxDate.MaxDate, InvoicesMaxDate.MaxID
FROM [Food purchase data], Invoices, (SELECT [Food purchase data].[Food item ID] AS ItemID, MAX(Invoices.[Invoice Date]) AS MaxDate, MAX(Invoices.[Invoice ID]) AS MaxID
FROM [Food purchase data], Invoices
WHERE Invoices.[Invoice ID] = [Food purchase data].[Invoice ID]
GROUP BY [Food purchase data].[Food item ID]
) AS InvoicesMaxDate
WHERE InvoicesMaxDate.MaxID = [Food purchase data].[Invoice ID] AND
InvoicesMaxDate.ItemID = [Food purchase data].[Food item ID] AND
InvoicesMaxDate.MaxDate = Invoices.[Invoice Date]
GROUP BY InvoicesMaxDate.ItemID, InvoicesMaxDate.MaxDate, InvoicesMaxDate.MaxID
Then I wrote another query to pull in the fields I needed:
SELECT [Food items].ID AS FoodItemID, [Food items].Item AS FoodItem, [Food purchase data].[Price], [Food purchase data].[Price per unit], [Food purchase data].[Purchase unit], LatestInvoices.MaxDate as InvoiceDate
FROM [Food items], [Food purchase data], LatestInvoices
WHERE LatestInvoices.[MaxID] = [Food purchase data].[Invoice ID] AND
LatestInvoices.ItemID = [Food purchase data].[Food item ID] AND
LatestInvoices.ItemID = [Food items].ID
ORDER BY [Food items].Item;
Thanks to all of you who took the time to help me with this!
DISTINCT
returns rows that are distinct across all columns in the row, not single columns.[
and]
ID
columns, soID
in theInvoices
table becomesInvoiceID
.DISTINCT
was by single columns. Is there an analogous operator that will select only based on uniqueness in a single column? Also, thanks for the tips on naming conventions - yes, it is very annoying to have to use[ ... ]
everywhere ... And I can see how including the table name in the ID column would increase readability.