Discussion Locked

Find Last Record in Microsoft ACCESS

0 helpful
12 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How do you obtain the last record in a history file for a particular client in Microsoft ACCESS? This is not the last record in file.
ie Joe Smith has 5 appointment records in the history file. I would like to obtain the 5th record to determine what date he was last in.

12 replies

by jrussell

Try the DMax function ...

Assuming your History table has fields named
"Client" and "ApptDate" you could do something like this ...

DMax("ApptDate","HistoryTable","[Client]='Mr Jone's')

Would return the last Date that Mr. Jones was in.You could also create a summary query and set the total parameter for the ApptDate field to Max (instead of the default GroupBy option)

Hope this helps ...

by dehartdm

I tried this but I need to create a report that lists the last appt for an entire client list. I also could not get DMAX to work in query. Thanks anyway.

by sl-campbell

I would set query to sort on the date field in a Descending order and the criteria to the clients ID and move then to the first record. That way, you have all appointments for this client available to you, can page through all appointments in the right date order and have the last date shown first.

by dehartdm

This is great for a display screen but I need a report of last appts for an entire client base. Thanks for your help.

by mark.thomson

For a single client

Select Clientname,Max(ApptDate) as LastApt From ApptTable inner join ClientTable on ApptTable.clientid = ClientTable.clientid WHERE ClientName = "John Smith"

or for all clients

Select ClientName,Max(ApptDate) as LastAptFrom ApptTable inner join ClientTable on ApptTable.clientid = ClientTable.clientid Group By ClientName

by dehartdm

I used query design view to put it together and then looked at the SQL. It was basically the same as your SQL statement for all clients but I was still receiving multiple records for one client with various dates on them. I am trying to createare report that contains all clients and their last appt dates. Is this a known problem with my version of Access. MAX and MIN never appeared to work.

by articulated mandible

You need to use a correlated subquery to get the results you want. The following query will list each Client and the date of their last appoinment only. I don't know how to build it in design view in Access, but you can simply copy and paste in the SQL view pane (changing the table and field names, of course).

SELECT ClientName, ApptDate as [LastVisit]
FROM tblClients, tblAppts as ApptA
WHERE tblClients.ClientID = ApptA.ClientID AND ApptDate =
(SELECT MAX(ApptDate)
FROM tblAppts as ApptB
WHERE ApptA.ClientID = ApptB.ClientID)

by c1980

I am trying to figure out just what you are asking for but it looks like a guessing to me! Based on the original question I see several correct answers!

You asked:
1.) obtain the last record - by appointment date.
2.)for a particular record(Client)

Your question has been answered.

If you are looking for something else than what you asked for then we need more specifics.

What is it that you are looking for?

1). The last record - by appointment date for a particular record(Client)?

2). A recordset based on the above for all clients, returning only the last record for each client?

3). A recordset based on the above for all clients, returning all records for all clients but displaying the last record for each client first?

by c1980

SELECT [ClientAppointments].[Client], MAX([ClientAppointments].[AppointmentDate]) AS LastAppt FROM [ClientAppointments] GROUP BY [ClientAppointments].[Client]

This discussion is closed to new replies.