īut I am still having the same problem: that is, I'm still seeing more than one result for each food item. , max(Invoices.) AS MostRecentInvoiceDate So I tried, both of the answers suggested below, and neither of them work (they just throw up syntax errors).īased 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. I care more about the query being understandable for someone with little knowledge of SQL. 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 ?Īnd 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). multiple records for each record in ), with the results sorted descending by date. However the query above simply returns all of the food purchases (i.e. 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 DESC) SELECT DISTINCT (.Item), Here is a diagram of the tables I'm working with: 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |