Other Writing SQL query (Distinct) for Access 2007

I need some help from anyone good at writing Access database queries -

I'm doing a research project in something completely new for me. It involves a large number of DEXA scans. The data from this is all contained within a large (500mb) database. I've never seen MS Access before, so this was a very steep learning curve for me.

HOWEVER, I've very nearly done what I need to do. I've managed to get all the data I need - the trouble is that I need to filter it down slightly. Allow me to demonstrate:

I have a some patients (n~1400). Each patient in my data base has had one or more scan(s). Because some patients have had more than one scan, my database is ~1700 rows. I'm only interested in the most recent scan, so I want to discard the "old" data.

This is how my data currently looks:

Code:

Patient 1  Scandate 1  Data1
Patient 1  Scandate 2  Data 2
Patient 2  Scandate 1  Data 1
Patient 3  Scandate 1  Data 1
Patient 4  Scandate 1  Data 1 
Patient 4  Scandate 2  Data 2

Basically I want to sort it so if the patient identifier is the same, to take the most recent scan date, and discard the older one (In this example "scandate 2"). All the scans are dated as per Excel date naming E.g. 39867.

What I would like

I want to say something like:

if patient ID in row x = row y, then refer to next column and choose the one with the highest scandate.

But I don't know how to write that as a SQL query.

Can anyone help?

 

P.S. I should probably say that I've tried using the "Select distinct" function - but so far I've only been able to display unique patient ID and patient names - I can't get it to display the data. Presumably because Access doesn't know which data to choose?

 

Assume the table is called 'scans' and the field names are 'patient' 'scandate' and 'data'

Code:

SELECT patient, scandate, data 
FROM scans as s
INNER JOIN (SELECT patient, MAX(scandate) as latest_scandate 
                     FROM scans GROUP BY patient) as x 
ON s.patient = x.patient AND s.scandate = x.latest_scandate 

That query first of all selects the latest scans for each patient in a subquery then joins it to the main table so you can get the data field too.

I don't know the Access flavour of SQL but it should work.

 

Awesome, I'll have a try with that later. Thanks!

 

Hi Guys

I am in need of some serious help. I as my name suggests I KNO NO SQL and i need to find a solution.

I am building a Access DB for outbound calling where we select a branch and then do a survey call to a customer who visited the branch and ask his feedback on the emplopyee who served him

Now I have build a switchbaord where the agent can select the branch and this is where i am stuck.

I need now for a query where the next drop down box in the switchboard is filled with the staff names based on the branch selected.

Here is a breakdown of the Tables i used .

Staff