Ms-access – Return only records with a date after the most recent September 1st

datems accessquery

I have a table with data with a date column called "dtAssessmentDate" in a table called "tblAssessmentRecords". What I want to achieve is for a select query to return only the records from that table where the date value is after the last September 1st.

For example, if the current date is #27/02/2021# then the query would return the records with any date after #01/09/2020# as that was the most recent September 1st in the past.

I can do this for a record after a fixed date with:
[dtAssessmentDate] > #01/09/2020#
but I need the fixed date to roll over as the years go on, so if the current date was 27/02/2022, the query would return records after #01/09/2021#

Best Answer

Answered my own question, leaving it here for refence for others as I couldn't find the solution anywhere else. By adding 4 months to the date from "dtAssessmentDate" I was able to make the range of values I needed to return as any date from a single year. I extracted the year I am looking for by doing the same 4 month addition to the current date, then using DatePart to exctract the year and only return records who's year matches the extracted year.

Expression in criteria box in access is:

DatePart("yyyy",DateAdd("m",4,[dtAssessmentDate]))=DatePart("yyyy",DateAdd("m",4,Date()))

Related Solutions

Ms-access – Displaying only the most recent activity of an item

Try this:

    SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM <tablename> a
    INNER JOIN 
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM <tablename>
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Update:

In your specific case:

SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM tblSend_Recieve a
    INNER JOIN 
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM tblSend_Recieve
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Ms-access – Minimum value on most recent date per account MS ACCESS SQL

This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords.

Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their implementation of Window Functions here.

SELECT CHACCTBS, CHRQDATE, CHSCORE
FROM
(
    SELECT
         MIN(CHSCORE) OVER(PARTITION BY CHACCTBS) As CHACCTBS
        ,CHRQDATE
        ,CHSCORE
        ,ROW_NUMBER() OVER(PARTITION BY CHACCTBS ORDER BY CHRQDATE DESC, CHSCORE) As Limiter
    FROM RiskScore
) X
WHERE Limiter = 1

The subquery returns your MIN and other columns, and creates a filter by assigning row numbers by your partition and ordered by descending dates. You then take the first row of each partition in the WHERE clause.

Best Answer

Related Solutions

Ms-access – Displaying only the most recent activity of an item

Ms-access – Minimum value on most recent date per account MS ACCESS SQL

Related Question