1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Other Writing SQL query (Distinct) for Access 2007

Discussion in 'Software' started by Tomm, 31 Aug 2010.

  1. Tomm

    Tomm I also ride trials :¬)

    Joined:
    12 Apr 2004
    Posts:
    2,249
    Likes Received:
    0
    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? :sigh:
     
  2. Tomm

    Tomm I also ride trials :¬)

    Joined:
    12 Apr 2004
    Posts:
    2,249
    Likes Received:
    0
    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? :sigh:
     
  3. Daedelus

    Daedelus New Member

    Joined:
    7 May 2009
    Posts:
    253
    Likes Received:
    12
    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.
     
  4. Tomm

    Tomm I also ride trials :¬)

    Joined:
    12 Apr 2004
    Posts:
    2,249
    Likes Received:
    0
    Awesome, I'll have a try with that later. Thanks!
     
  5. IKNOWNOSQL

    IKNOWNOSQL New Member

    Joined:
    29 Jun 2011
    Posts:
    1
    Likes Received:
    0
    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
     

Share This Page