MS Access query earliest date of consecutive values

Question

I have data like this

ID    StartDate       EndDate      DesigCode     FacilityID
------------------------------------------------------------
1      1-Jan 2015      1-Feb 2015      1             1
2      3-Feb 2015     10-Mar 2015      1             2
3      5-Apr 2015      3-June 2015     2             1
4      2-Jul 2015      6-Aug 2015      3             1
5     15-Sep 2015     30-Oct 2015      3             4
6     14-Nov 2015      1-Dec 2015      1             4

I want a query that result, earliest date of consecutive same value in DesigCode column.

ID   StartDate       EndDate     DesigCode    FacilityID
------------------------------------------------------------
1     1-Jan-2015     1-Feb 2015      1           1
3     5-Apr 2015     3-June 2015     2           1
4     2-Jul 2015     6-Aug 2015      3           1
6    14-Nov 2015     1-Sec 2015      1           4

I have no idea about this query.

You want the earliest date for each DesigCode group? Try Top N with subquery. Review http://allenbrowne.com/subquery-01.html#TopN — June7, Apr 10 '19 at 17:16
Possible duplicate of [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) — June7, Apr 10 '19 at 17:17

score 0 · Answer 1 · answered Apr 12 '19 at 00:25

0

Try the following query. It returns the record with the lowest end date for each design code.

SELECT TOP 1 ID, StartDate, MIN(EndDate), DesigCode FROM [TABLENAME]
GROUP BY ID, StartDate, DesigCode;

answered Apr 12 '19 at 00:25

BankBuilder

466
2
10

MS Access query earliest date of consecutive values

1 Answers1