group by having max()

[pramoc]

Hi!

I have a MS Access database that contains a table and I need a formatted query. I have attached an excel sample file that contains a look n feel of the table data (sheet1) and required output (sheet2).

What I want is last transaction of each servicer_no and purchaser_no. for instance

Lets say Serial number ?1? and Purchaser number ?1? has 3 transactions in 3 days. So What I need is last record (transaction) entered for every specific serial number and purchaser number.

I have mentioned output in output excel sheet.

Criteria would be the latest date and time.

[Ebrow]

SQL for query1

SELECT Table1.Serial_Num, Last(Table1.Purchaser_Num) AS LastOfPurchaser_Num
FROM Table1
GROUP BY Table1.Serial_Num;


sql for Query2. This will give your answer.

SELECT Table1.Serial_Num, Table1.Purchaser_Num, Table1.[Allotment Date], Table1.Transdate, Table1.Comments1, Table1.Comments2
FROM Query1 INNER JOIN Table1 ON (Query1.Serial_Num = Table1.Serial_Num) AND (Query1.LastOfPurchaser_Num = Table1.Purchaser_Num);

[pramoc]

Can u pls explain ?

[Ebrow]

Create a query called query1 in the SQL view copy and paste the first sql code.

Create a second query aclled query2 in the SQL view copy and paster the second sql code.

To change to SQL view, click on the pull down on the design button within a query, there is a button with the letters SQL. Select this.

[matthewspatrick]

In one query:

SELECT Serial_Num, Purchase_Num, Allotment_date, Max(trans_date) AS LatestDate, status, Comments1, Comments2
FROM YourTable
GROUP BY Serial_Num, Purchase_Num, Allotment_date, status, Comments1, Comments2
HAVING Max(trans_date) = trans_date
ORDER BY Serial_Num, Purchase_Num