Query with most recent contract

[Sarah]

I could use some help. I have a table with customer info and a table with
contracts. They are linked by an ID field. I would like to create a query
that only links the most recent contract (it is determined by a date field)
for the customer. I know this sounds simple but I can't think of how to do
it. Thanks for your help.

Sarah

 

[Marshall Barton]

I could use some help. I have a table with customer info and a table with
contracts. They are linked by an ID field. I would like to create a query
that only links the most recent contract (it is determined by a date field)
for the customer. I know this sounds simple but I can't think of how to do
it.

I think this will do that:

SELECT Customers.*, Contracts.*
FROM Customers INNER JOIN Contracts
ON Customers.ID = Contracts.customerID
WHERE Contracts.contractdate = (
SELECT Max(X.contractdate)
FROM Contracts As X
WHERE X.customerID = Contracts.customerID
)

 

[Sarah]

Thanks Marshall - it worked perfectly!

I think this will do that:

SELECT Customers.*, Contracts.*
FROM Customers INNER JOIN Contracts
ON Customers.ID = Contracts.customerID
WHERE Contracts.contractdate = (
SELECT Max(X.contractdate)
FROM Contracts As X
WHERE X.customerID = Contracts.customerID
)