How To Select Record With The Latest Date?

[Altstatten]

Table1
Table1.Date of Last Sale

Table2
Table2.Firstname
Table2.Lastname


In english I have...

09/01/12 John Smith
04/11/14 John Smith
07/22/13 John Smith
11/30/11 John Smith
06/03/60 Fred Jones
09/01/12 Fred Jones
01/03/10 Fred Jones
10/25/12 Fred Jones

..and so on. Multiple people having placed multiple orders on multiple dates. The outcome I'm looking for is this:

04/11/14 John Smith
10/25/12 Fred Jones

I need the latest line item for all of John Smith's purchases, based on the "Date of Last Sale" column, same for Fred Jones, same for each person in the list.

I'm not a programmer but I do have some experience in Crystal Reports so I'm fairly confident I have the tables linked properly with the correct connectors, etc. What has me stumped is what a formula that returns, "only the line item with the latest "Date of Last Sale" for all orders with John Smith's name on them, then does the same for each person in the list looks like. Spent numerous hours on this already and all I can conclude is that "Maximum" may have something to do with it but that's as far as I've gotten.

Again, if I can build this in the Query Design window that would make things much smoother for me on my end. Anybody? Thanks in advance for your help.

[eremmel]

To be able to solve this in SQL you need to think in data sets. Unfortunate you did not give me your table structure so I've to describe it a little in pseudo code, but I hope you grasp the idea.
Make a query that returns the last date per person, save that query as qLastSale:

Code: Select all

SELECT MAX(a."Date of Last Sale") as "LastSaleDate", b.Firstname, b.Lastname
FROM Table1 a INNER JOIN Table2 b ON a."forignKeyToTable2" = b."primaryKeyTable2"
GROUP BY b.Firstname, b.Lastname

You can now use this query as a basis for your other selections queries.

To help any further please upload a base document with some sample data.

[hoettilie]

This is a really useful code that I would like to adopt for my own database project. However, I need to expand it a little and cannot figure out how. Maybe someone could help me with this.

I wish to add to my a query result a "comment" which is stored in the same table as the dates of the purchase.

Like This:

Table a: 09/01/12 "Ordered two apples" - Related Table b: John Smith
Table a: 04/11/14 "Ordered one lemon" - Related Table b: John Smith
Table a: 07/22/13 "Ordered three apples" - Related Table b: John Smith
Table a: 11/30/11 "Ordered one banana" - Related Table b: John Smith

So that the result of my query is

04/11/14 John Smith "Ordered one lemon"
10/25/12 Fred Jones "Ordered a car"
...

I would be grateful for any help!

Michael

[eremmel]

The example query is about finding the last date of table a. You have to join again on the same table relation to get your comment:
I assume that you saved your query as 'qLastSale' Introduce a query like 'qAllSale':

Code: Select all

SELECT a."Date of Sale" as "SaleDate", b.Firstname, b.Lastname
FROM Table1 a INNER JOIN Table2 b ON a."forignKeyToTable2" = b."primaryKeyTable2"

Now you can combine the two:

Code: Select all

SELECT a."LastSaleDate", b."Comment", a.Firstname, a.Lastname
FROM qLastSale a 
     INNER JOIN qAllSale b 
          ON a."LastSaleDate" = b."SaleDate" and a.Firstname = b.Firstname AND a.Lastname = b.Lastname

This will break when you have more that one sale at the same date of the same (b.Firstname, b.Lastname).

[hoettilie]

Great! The code works well and I learned something new.

Thank you very much. You just helped a small community-housing project to become better organized.

Best regards,
Michael