Mission Impossible ? (1 Viewer)

[popen]

This topic is about a query I am running.
The query has three tables in it, one of which is a SQL view (Linked)
Within this view are the following fields:

Patient ID
Date Of Birth
GP Name
PCG Name
Start Date

Now, the problem that I am having is, to be brief, Duplicate Entries on the query, which show on the report that pulls off the query.

You see, a patient can have a patient ID, and be registered with a certain GP at a specific start date.
The patient can then be registered with ANOTHER GP at a different start date.
So, on my report I have a patient that is registered with 2 GP's.
I need to find a way to have only the Latest start date shown on the report, hence the patient will be shown with the correct GP.

I have tried using the MAX clause in SQL but it is getting too complex, I am sure this must be simple.

Any help would be appreciated.
Many thanks in advance,

-NEIL L POPE

 

[Chris RR]

There's probably a more elegant method, but here's what I would do.

Write a query based on the view, showing only the patient info with the latest start date (using the MAX here). Save this query, then use it in your final query, instead of the table view.

 

[popen]

Thanks for that, although my efforts are being utilised elsewhere at the moment I will give that a go very soon. Thanks again,

-NEIL L POPE