SQL - Show hospitals Patient most recent visit

Two tables in Access - One-to-many

tblPatient
-------------
PatientID
PatientName


tblVisits
-----------
PatientID
DateOfVisit

I want a simple SQL that shows all patient names ONCE and also shows their latest tblVisits.DateOfVisit.

What is my SQL syntax?
Patrick O'DeaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this query

select P.PatientName, Max(V.DateOfVisit)
from tblPatient as P Inner Join tblVisits As V
On P.PatientID=V.PatientID
Group by P.PatientName
0
 
Gustav BrockConnect With a Mentor CIOCommented:
You should group by the ID, except if you wish to sort on the patient name.

/gustav
0
 
SteveConnect With a Mentor Commented:
Something along the lines of:
SELECT tblPatient.PatientName, Max(tblVisits.DateOfVisit) AS [Last Visit]
FROM [tblVisits] INNER JOIN tblPatient ON tblVisits.PatientId = tblPatient.PatientId
GROUP BY tblPatient.PatientId;

Open in new window

0
 
Patrick O'DeaAuthor Commented:
Thanks all, I got it working.

(On a separate note , a special thanks to sodea63 ... WHY? because I can't help but wonder if we share the same surname.... O'Dea ??)
0
 
SteveCommented:
Yes we do. Were family somewhere down the tree.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.