CODEWITHSUNDEEP
sqlms-accessvbams-access-2016
user-DY
user-DY

Reputation: 123

SQL Server LEAD Equivalent Function in MS Access 2016

I am trying to create some sort of Validity (Date Range) fields on my MS ACCESS table. It will need to be group by ID, Subject and Level to derive the effective period of each record. If there is no corresponding test taken to improve the grade, the effective to date will be set to 31/12/9999.

I have the following table now: 

ID  | Subject | Level | FromGrade | ToGrade | TestDate
101 | Math    |  5    | C+        | D       | 31/11/2016
101 | Math    |  4    | D         | A       | 01/12/2016
101 | Math    |  5    | D         | B+      | 12/12/2016
101 | Math    |  5    | B+        | A       | 25/12/2016
102 | English |  4    | B         | B       | 20/12/2016
102 | English |  4    | B         | C       | 28/12/2016

The end resulting table i should be getting the following:

ID  | Subject | Level | FromGrade | ToGrade | TestDate   | EffectiveTo
101 | Math    |  5    | C+        | D       | 31/11/2016 | 11/12/2016
101 | Math    |  4    | D         | A       | 01/12/2016 | 31/12/9999
101 | Math    |  5    | D         | B+      | 12/12/2016 | 24/12/2016
101 | Math    |  5    | B+        | A       | 25/12/2016 | 31/12/9999
102 | English |  4    | B         | B       | 20/12/2016 | 27/12/2016
102 | English |  4    | B         | C       | 28/12/2016 | 31/12/9999

To do this in SQL Server, i could easily use the LEAD OVER function:

SELECT [ID]
, [Subject]
, [Level]
, [FromGrade]
, [ToGrade]
, [TestDate]
, [EffectiveTo] = LEAD([TestDate], 1) OVER ( PARTITION BY [ID], [Subject], [Level]  ORDER BY [TestDate] )
into StudentTable2
FROM [dbo].[StudentTable1]
ORDER BY [ID], [Subject], [Level], [TestDate]

and then follow by a 

update [dbo].StudentTable2set [EffectiveTo] = DATEADD("DAY", -1, [EffectiveTo]) where EffectiveTo is not null

However, this is not working on Ms Access/VBA script, is there any other way i can achieve the same results?

Upvotes: 1

Views: 1365

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can use a correlated subquery. To get the next value in MS Access:

SELECT st.*,
       (SELECT MIN(st2.TestDate)
        FROM dbo.StudentTable1 as st2
        WHERE st2.ID = st.ID AND
              st2.Subject = st.Subject AND
              st2.[Level] = st.[Level] AND
              st2.TestDate > st.TestDate
       ) as EffectiveTo
FROM dbo.StudentTable1 as st
ORDER BY [ID], [Subject], [Level], [TestDate];

Upvotes: 1

Related Questions