How to get MAX function in Access return more than 2 fields of a record

[jsduser]

Dear all,

I’m not an expert but doing my best. Have tried hard but are not stuck and hoping for some help.

I am trying to use MAX in order to get the record from a table with maximum value of a specific field..
I can manage to have MAX return two FIELDS from that record, but I need more fields.
Using a second query to get those fields is not an option (AFAIK) because non of the two fields returned can reliably be used as query search value to uniquely find back the record with the maximum value.

So the question is: How can I create a query that returns at least 3 fields from the record with the maximum value.

I have 2 tables.

Table “Licenses”
Field LicenseNumber, ProductCode, ProductName
All of type TEXT

Table “Transacti ons”
Field ID = autonumber
Field LicenseNumber = Text (linked to Licenses.Licens eNumber in the relations)
Field TransactionTime Stamp = DateTime
Field TransactionType = Number (an integer)

Contents of Table Licenses

LicenseNumber	ProductCode	ProductName
1	PRODA	PRODUCT A
2	PRODA	PRODUCT A
3	PRODB	PRODUCT B
4	PRODC	PRODUCT C
5	PRODD	PRODUCT D
6	PRODC	PRODUCT C

Contents of Table Transactions

ID	LicenseNumber	TransactionTimeStamp	TransactionType
2	2	10/06/2002 20:04	3
3	3	16/10/2013 08:04	2
4	1	01/06/2012 23:03	1
5	1	01/03/2006 23:12	4
6	3	01/01/2013 19:12	2
7	2	01/01/2005 08:15	3
8	2	08/06/2013 20:01	4
9	4	01/01/2000 00:00	1
10	1	01/06/2013 00:00	3
11	1	01/06/2013 17:10	2

What I need to get from a query is (from table Transactions),
for every specific LicenseNumber
What is the timestamp of the last transaction done, and what was the TransactionType of that transaction.

Now with one query I can get
For every specific LicenseNumber that had Transactions
What is the TransactionTime Stamp of the last transaction, and what is the LicenseNumber

Let’s name this query “Get MaxTransactionT imeStamp_per_li cense”

SELECT Max(Transactions.TransactionTimeStamp) AS LastTransactionTimeStamp, Transactions.LicenseNumber
FROM Transactions
GROUP BY Transactions.LicenseNumber;

Resulting in

LastTransactionTimeStamp	LicenseNumber
01/06/2013 17:10	1
08/06/2013 20:01	2
16/10/2013 08:04	3
01/01/2000 00:00	4

Which is correct but I need the TransactionType also.

So what I would like to get is

LastTransactionTimeStamp	LicenseNumber	TransactionType
01/06/2013 17:10	1	2
08/06/2013 20:01	2	4
16/10/2013 08:04	3	2
01/01/2000 00:00	4	1

You would say: Run another query using table “Transacti ons” and the query “GetMaxTransact ionTimeStamp_pe r_license” Linking the fields “LastTransactio nTimeStamp” of both.
But that will not work reliably. Why? Because in my database there may be multiple entries in table LastTransaction s with the same value of field LastTransAction TimeStamp.

You would then say: use field TransactionID to find the most recent transaction. That will not work either because in my database the LastTransAction TimeStamp of an entry in LastTransaction can change. So if the transaction with ID 5 today is the most recent one, tomorrow the one with ID 3 may be the most recent one (for one and the same LicenseNumber).

After heavy experimenting I am stuck with the feeling that the only solution is getting more than 2 values out of the SQL statement with MAX.
I have seen some examples of nested SQL statements where a MAX statement is nested inside another one with an INNER JOIN but I have not managed to get that to work.

Any help is greatly appreciated.

[Rabbit]

Please use code tags when posting code or formatted data.

Are you saying that your data could look like this?

ID    LicenseNumber    TransactionTimeStamp    TransactionType
2    2    10/06/2002 20:04    3
3    2    10/06/2002 20:04    2

If so, how do you know which TransactionType you want? If not, then there's no issue with you joining the query back to the table on LicenseNumber and LastTransAction TimeStamp.

[jsduser]

Rabbit thanks a lot for your reply.

I get what you mean.
Your example is not very likely although possible. In that case I would not care which transactiontype would be returned (preferably the one with the highest ID).

More likely is having multiple equal timestamps as you mention but each for a different license number.

[Rabbit]

If the case is the latter, there's no issue with joining your aggregate query back to the table on the grouping field and max result.