Record grouping problem [Resolved]

[freewilly]

Hi team

I have a table that holds contract details for travel agents and customers. The table contains details of expired contract as well as current contract. I can tell which contract is current by looking at the ContractExpireDate.

I get the latest contract by grouping on the agent ID and customerID and get the Max(ContractExpireDate).

This works fine. The problem is that I also want to get the ContactID of the Max(ContractExpireDate) record. Max(ContactID) is not guaranteed to be from the same record as Max(ContractExpireDate).

So my question is, can I do the grouping so that the ContractID I get back is guaranteed to be from the same record as the ContractExpireDate?

Thanks

FW

[mendhak]

What SQL Query are you using?

[freewilly]

Hi mendhak

Heres a db with the data and the query. I need to get the ContractID that corresponds to the record with the highest ContractDate.

Thanks

FW

[mendhak]

Erm... Doesn't Access let you type the SQL Query anywhere?

[BruceG]

Try this:

SELECT X.CustomerID, T.ContractID, X.MaxDate
FROM (SELECT CustomerID, MAX(DateEnd) AS MaxDate FROM tblContract GROUP BY CustomerID) AS X
INNER JOIN tblContract AS T
ON  X.CustomerID = T.CustomerID
WHERE T.DateEnd = X.MaxDate

[freewilly]

Thanks Bruce