Query to return entire rows with Min Date only (1 Viewer)

[83dons]

I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.

To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.

EventsTable
```````````
EpisodeID    |    EventID    |    EventDate
-------------+---------------+-------------
1            |    001        |    01/02/2010
1            |    023        |    05/10/2009
1            |    103        |    12/02/2010
2            |    004        |    02/03/2013
2            |    102        |    12/10/2014
3            |    546        |    04/05/2012
3            |    100        |    08/08/2013
3            |    034        |    10/10/2012
3            |    066        |    02/03/2013
4            |    777        |    05/07/2014
4            |    233        |    01/11/2012
5            |    087        |    10/03/2011

ExpectedOutput
``````````````
EpisodeID    |    EventID        |    EventDate
-------------+-------------------+---------------
1            |    023            |    05/10/2009
2            |    004            |    02/03/2013
3            |    546            |    04/05/2012
4            |    233            |    01/11/2012
5            |    087            |    10/03/2011

 

[pr2-eugin]

It could have been a simple GROUP BY Query if not for the Event ID, so you might need a SubQuery to save your day. Something like.

SELECT 
    tQ.EpisodeID, 
    tQ.EventID, 
    tQ.EventDate
FROM 
    (
        SELECT 
            EventsTable.EpisodeID, 
            EventsTable.EventID, 
            EventsTable.EventDate 
        FROM 
            EventsTable 
        INNER JOIN 
        (
            SELECT 
                EventsTable.EpisodeID, 
                Min(EventsTable.EventDate) AS nEventDate
            FROM 
                EventsTable
            GROUP BY 
            EventsTable.EpisodeID
        ) As tmp 
        ON 
        EventsTable.EpisodeID = tmp.EpisodeID 
        AND 
        EventsTable.EventDate = tmp.nEventDate
    )  AS tQ;

 

[83dons]

Hi this seems to work as expected I tested 3 or 4 rows and they do return the correct EventID and the Min EventDate. If the original table had other columns how do I ammend the query above to brings all of them across? They have to all come from the row that the Min Event Date is on though. Say for example we also had columns:

EventOutcome
EventSeenBy

 

[pr2-eugin]

Add them to the First SELECT tQ.EventOutcome, tQ.EventSeenBy and then to the second select as, EventsTable.EventOutcome, EventsTable.EventSeenBy

 

[83dons]

The actual code is below it wont let me save it as it is saying 'Syntax Error in FROM clause'. Strangely this error did appear with the simpler version but only after I viewed the results and tried to widen the columns in the display which was odd!

SELECT tQ.PIN, tQ.P__Business_Unit, tQ.EPISODEUNIQUEKEY, tQ.D_S__Episode_Start_Date, tQ.D_S__Finish_Date, tQ.S__Episode_Type, tQ.CLINICALENCOUNTERUNIQUEKEY, tQ.D_S__Attendance_Date. tQ.S__Event_Type, tQ.S__Seen_By, tQ.S__Outcome
FROM [SELECT 
            [2-5 All Man Report Events within Man Referral Episodes].PIN,
            [2-5 All Man Report Events within Man Referral Episodes].P__Business_Unit,
            [2-5 All Man Report Events within Man Referral Episodes].EPISODEUNIQUEKEY,
            [2-5 All Man Report Events within Man Referral Episodes].D_S__Episode_Start_Date,
            [2-5 All Man Report Events within Man Referral Episodes].D_S__Finish_Date,
            [2-5 All Man Report Events within Man Referral Episodes].S__Episode_Type,
            [2-5 All Man Report Events within Man Referral Episodes].CLINICALENCOUNTERUNIQUEKEY, 
            [2-5 All Man Report Events within Man Referral Episodes].D_S__Attendance_Date,
            [2-5 All Man Report Events within Man Referral Episodes].S__Event_Type,
            [2-5 All Man Report Events within Man Referral Episodes].S__Seen_By,
            [2-5 All Man Report Events within Man Referral Episodes].S__Outcome
       FROM
            [2-5 All Man Report Events within Man Referral Episodes]
        INNER JOIN 
        (
            SELECT 
                [2-5 All Man Report Events within Man Referral Episodes].EPISODEUNIQUEKEY, 
                Min([2-5 All Man Report Events within Man Referral Episodes].D_S__Attendance_Date) AS nEventDate
            FROM 
                [2-5 All Man Report Events within Man Referral Episodes]
            GROUP BY 
            [2-5 All Man Report Events within Man Referral Episodes].EPISODEUNIQUEKEY
        ) As tmp 
        ON 
        [2-5 All Man Report Events within Man Referral Episodes].EPISODEUNIQUEKEY = tmp.EPISODEUNIQUEKEY 
        AND 
        [2-5 All Man Report Events within Man Referral Episodes].D_S__Attendance_Date = tmp.nEventDate
    ]. AS tQ;

 

[pr2-eugin]

So many typos, you have a . instead of a , in the first SELECT. You need to use ( brackets for subqueries not [.

The last line has a Square bracket and a full stop where it should be a just ')' bracket. Also close all columns with square brackets. Something like,

SELECT 
    tQ.[PIN],  
    tQ.[P__Business_Unit], 
    tQ.[EPISODEUNIQUEKEY], 
    tQ.[D_S__Episode_Start_Date], 
    tQ.[D_S__Finish_Date], 
    tQ.[S__Episode_Type], 
    tQ.[CLINICALENCOUNTERUNIQUEKEY], 
    tQ.[D_S__Attendance_Date], 
    tQ.[S__Event_Type], 
    tQ.[S__Seen_By], 
    tQ.[S__Outcome]
FROM (SELECT 
        [2-5 All Man Report Events within Man Referral Episodes].[PIN], 
        [2-5 All Man Report Events within Man Referral Episodes].[P__Business_Unit], 
        [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY], 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Episode_Start_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Finish_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Episode_Type], 
        [2-5 All Man Report Events within Man Referral Episodes].[CLINICALENCOUNTERUNIQUEKEY],  
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Event_Type], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Seen_By], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Outcome]
       FROM
            [2-5 All Man Report Events within Man Referral Episodes]
        INNER JOIN 
        (
            SELECT 
                [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],  
                Min([2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date]) AS nEventDate
            FROM 
                [2-5 All Man Report Events within Man Referral Episodes]
            GROUP BY 
                [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY]
        ) As tmp 
        ON 
        [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY] = tmp.[EPISODEUNIQUEKEY]
        AND 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date] = tmp.[nEventDate]
    ) AS tQ;

 

[83dons]

Hi i think Access 2003 is changing your code when I save the query! I cut and pasted your suggestion into the SQL box and it ran ok producing output as required (not tested yet by looking up rows). But when I go back to design view I find your code has been changed to the following. There are no error messages though on running the query again to get the results is it ok that the coding has been changed by Accces to:

SELECT tQ.[PIN], tQ.[P__Business_Unit], tQ.[EPISODEUNIQUEKEY], tQ.[D_S__Episode_Start_Date], tQ.[D_S__Finish_Date], tQ.[S__Episode_Type], tQ.[CLINICALENCOUNTERUNIQUEKEY], tQ.[D_S__Attendance_Date], tQ.[S__Event_Type], tQ.[S__Seen_By], tQ.[S__Outcome]
FROM [SELECT 
        [2-5 All Man Report Events within Man Referral Episodes].[PIN], 
        [2-5 All Man Report Events within Man Referral Episodes].[P__Business_Unit], 
        [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY], 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Episode_Start_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Finish_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Episode_Type], 
        [2-5 All Man Report Events within Man Referral Episodes].[CLINICALENCOUNTERUNIQUEKEY],  
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Event_Type], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Seen_By], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Outcome]
       FROM
            [2-5 All Man Report Events within Man Referral Episodes]
        INNER JOIN 
        (
            SELECT 
                [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],  
                Min([2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date]) AS nEventDate
            FROM 
                [2-5 All Man Report Events within Man Referral Episodes]
            GROUP BY 
                [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY]
        ) As tmp 
        ON 
        [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY] = tmp.[EPISODEUNIQUEKEY]
        AND 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date] = tmp.[nEventDate]
    ]. AS tQ;

 

[pr2-eugin]

Hi i think Access 2003 is changing your code when I save the query!

This is so bizarre, I have never come across.

it ran ok producing output as required (not tested yet by looking up rows).

Good luck !

 

[83dons]

Has anyone else come across Access changing ( to [ and adding full stops?

I am abit loathe to use this query now despite it appearing to display the correct results.

Bizarrely if I go to the results list and try to drag a column wider and save the layout then it comes up with the same error as mentioned above!

Can you get this query to work and stay unchanged in Access 2003?

 

[Frothingslosh]

Has anyone else come across Access changing ( to [ and adding full stops?

I am abit loathe to use this query now despite it appearing to display the correct results.

Bizarrely if I go to the results list and try to drag a column wider and save the layout then it comes up with the same error as mentioned above!

Can you get this query to work and stay unchanged in Access 2003?

I have...twenty years ago using Access 2. Had a query that worked fine as long as it was never opened in the QBE grid, but opening it there flat-out broke it. It was slightly complicated.

In modern versions, however, I have not.

 

[83dons]

On a brief google seach it seems that Access 2000-2003 has a query optimiser that can change the code if it thinks its wrong. In 2007 this isnt the case. I will try the query as is in 2007 as we will all here be moving to that soon but it would be better if I could get it to work in both versions!

 

[83dons]

Hi the query works in Access 2007 but Access 2003 tries to optimise it and breaks it!

 

[83dons]

Hi sorry to be a pain any idea how I would add in one more criteria to the code below in order to narrow the output down to just episodes with a start date equal or grater than 1/9/14 (ie [D_S__Episode_Start_Date] >=# 01-09-2014#? With it being complicated query I wasnt sure how to fit this in?! Thanks again

SELECT tQ.[PIN], tQ.[P__Business_Unit], tQ.[EPISODEUNIQUEKEY], tQ.[D_S__Episode_Start_Date], tQ.[D_S__Finish_Date], tQ.[S__Episode_Type], tQ.[CLINICALENCOUNTERUNIQUEKEY], tQ.[D_S__Attendance_Date], tQ.[S__Event_Type], tQ.[S__Seen_By], tQ.[S__Outcome]
FROM (SELECT 
        [2-5 All Man Report Events within Man Referral Episodes].[PIN], 
        [2-5 All Man Report Events within Man Referral Episodes].[P__Business_Unit], 
        [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY], 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Episode_Start_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Finish_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Episode_Type], 
        [2-5 All Man Report Events within Man Referral Episodes].[CLINICALENCOUNTERUNIQUEKEY],  
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Event_Type], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Seen_By], 
        [2-5 All Man Report Events within Man Referral Episodes].[S__Outcome]
       FROM
            [2-5 All Man Report Events within Man Referral Episodes]
        INNER JOIN 
        (
            SELECT 
                [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],  
                Min([2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date]) AS nEventDate
            FROM 
                [2-5 All Man Report Events within Man Referral Episodes]
            GROUP BY 
                [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY]
        ) As tmp 
        ON 
        [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY] = tmp.[EPISODEUNIQUEKEY]
        AND 
        [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date] = tmp.[nEventDate]
    )  AS tQ;

 

[83dons]

Any ideas pr2-eugin ?