Select the top value for a given parent record in a child databas.

[Dwise]

I have a "parent database" of contacts and a "child" database of phone calls.
How do I create a query that shows parent records and only the latest
"calls" as contained in the corresponding linked record in the child database?

 

[John Spencer (MVP)]

It depends, do you have something in the child database that shows the datetime
of the last phone call? And what do you want returned? The date and time of
the last call, additional information?

SELECT C.FieldA, C.FieldB, Max(CallDate) as LastCall
FROM Contacts as C INNER JOIN PhoneCalls as P

 

[Dwise]

Thank you. Yes the child database has date and time fields and I want to
display one other text field from the Parent and child database. Based on
your suggestion, is the following correct?

SELECT Contacts.ContactID, Max(Calls.CallDate) AS MaxOfCallDate,
First(Calls.Subject) AS FirstOfSubject
FROM Contacts LEFT JOIN Calls ON Contacts.ContactID = Calls.ContactID
GROUP BY Contacts.ContactID;

 

[Van T. Dinh]

See The Access Wb article:

<http://www.mvps.org/access/queries/qry0020.htm>

on how to get the related Fields in a Group By Query.

HTH
Van T. Dinh
MVP (Access)

 

[John Spencer (MVP)]

No, it isn't. First will basically return a more or less random value from the
subject field that is one of the ones available for this contact.

In this case, you can use a subquery

SELECT Contacts.ContactID, Calls.CallDate,
Calls.Subject
FROM Contacts LEFT JOIN Calls
ON Contacts.ContactID = Calls.ContactID
WHERE Call.CallDate =
(SELECT Max(C.CallDate)
FROM Contacts C
WHERE C.ContactID = Contacts.ContactID)