I take it you mean tables, not recordsets. I have had similar problems. I
see this as a limitation in the GROUP BY statement. Anyway, I'm not sure if
Access supports this but this works for me in SQL Server.

SELECT Con.Name, Con.Phone,
     (SELECT TOP 1 DateTime
     FROM Calls
     WHERE ContactID = Con.ContactID
     ORDER BY DateTime Desc) DateTime,
     (SELECT TOP 1 Duration
     FROM Calls
     WHERE ContactID = Con.ContactID
     ORDER BY DateTime Desc) Duration,
     (SELECT TOP 1 Result
     FROM Calls
     WHERE ContactID = Con.ContactID
     ORDER BY DateTime Desc) Result
FROM Contacts Con
     INNER JOIN Calls ON
     Calls.ContactID = Con.ContactID
WHERE Calls.DateTime > 12/12/01 00:00:00
    AND Calls.DateTime < 13/12/01 00:00:00

If this doesn't work you are stuck with doing the work in your program.
Probably the best way to go about it would be to replace the subqueries with
the fields from the calls table. In your program loop through contacts and
when they change get the previous datetime, duration and result (which you
read into variables). If you did this you would need to add in a ORDER BY
clause to the qry. Hope this helps.

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: Gary Clark [mailto:glc.home@v...]
Sent: Thursday, 19 July 2001 1:27 AM
To: sql language
Subject: [sql_language] SQL Syntax (Help!)



In Access I have a "Contacts" recordset, and a "Calls" recordset.  The 
calls recordset contains date/time, duration, and call result information 
that relates back to "Contacts" by ContactId.

There are several "Calls" for several "Contacts" in a day.

I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime, 
Calls.Duration, and Calls.Result for the very last call on every Contact 
that has a call for a given day.

Everything I have tried only returns the information for 1 contact that 
had the very last call.