Solved

SQL - Show hospitals Patient most recent visit

Posted on 2014-02-10
5
379 Views
Last Modified: 2014-02-11
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?
0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 167 total points
ID: 39847828
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
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 total points
ID: 39847834
You should group by the ID, except if you wish to sort on the patient name.

/gustav
0
 
LVL 7

Assisted Solution

by:Steve
Steve earned 166 total points
ID: 39847852
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
 

Author Closing Comment

by:Patrick O'Dea
ID: 39851223
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
 
LVL 7

Expert Comment

by:Steve
ID: 39851245
Yes we do. Were family somewhere down the tree.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question