Last record of multiple people

[Guest]

I have a call log table with many entries for each client.

Is it possible to run a query that will show all the clients, but only their
last record?

thanks

 

[John Spencer]

Probably.
What determines the last record? Do you have a date time field that you use
for that?
What other information do you need?

If all you need is the client and the date time of the last contact/call.

Open a query
Add the table
Add Client information
Add the datetime field
Select View: Total
Change GROUP BY to MAX under the date time field
Run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Austin,

Without knowing your table structure, this is just a quess.

SELECT yourTable.*
FROM yourTable
INNER JOIN (SELECT ClientID, MAX(EntryDate) as MaxDate
FROM yourTable
GROUP BY ClientID) as qMaxDate
ON yourTable.ClientID = qMaxDate.ClientID
AND yourTable.EntryDate = qMaxDate.EntryDate

This will give you the latest record for each client, but would not be the
quickest way if you are just interested in returning the latest record for a
single client. If that is the case, the SQL would need to look something
like:

SELECT yourTable.*
FROM yourTable
WHERE yourTable.ClientID = [SomeClientID]
AND yourTable.EntryDate = (SELECT MAX(EntryDate)
FROM yourTable
WHERE yourTable.ClientID =
[SomeClientID])

HTH
Dale

 

[Guest]

The call log table has the following columns:

Client Id, Contact Date, Comments, and Follow Up Date

Basically, I'm looking for the query to diplay the last contact date for
each client.

thanks

 

[Guest]

I'm sorry there is also a record column with autonumber, which I don't know
if it matters.

 

[John Spencer]

Then using the query I described should work for you unless you want
comments and follow up date also returned.

The SQL would look like
SELECT [Client ID], Max([Contact Date]) as LastContact
FROM YourTable
GROUP BY [Client ID]

If you want other fields you will need to do this in two steps. Query one
would be the above query saved as qLastContact. Then you would make another
query with your table and qLastContact

SELECT YourTable.[Client ID]
, YourTable.[Contact Date]
, YourTable.Comments
, YourTable.[Follow Up Date]
FROM YourTable INNER JOIN qLastContact
ON YourTable.[Client ID] = qLastContact.[Client ID]
AND YourTable.[Contact Date] = qLastContact.LastContact

If you can't figure out how to build the queries in the SQL window, then
post back and I will try to find time to give you detailed instructions on
constructing the queries in the query view (query grid).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..