Search code examples
sqlms-accessvbams-access-2016

MS Access automatically fill Query column based on start and end date

I have a Query called Records Query which I'm entering the information with a form.

Every record has a date.

In another table I have a list of date ranges with the name of the range. That means the first column is the Range name, the second Start date and the third, End date.

I want the last column on the Records Query to show the name of the date range that that specific range is in.

For example, if I have the following date ranges:

+------------+----------+----------+
| Start date | End date |   Name   |
+------------+----------+----------+
| Jan 1      | Jan 10   | session1 |
| Jan 11     | Jan 20   | session2 |
+------------+----------+----------+

If the date of that record in Records Query is Jan 2, the value of column Session should automatically be session1

If anyone can help it would be greatly appreciated.

Solution

  • Assuming that your dates are stored using DateTime fields and that your date ranges are contiguous (no gaps), you can determine the appropriate session name for your date using the following correlated subquery:

    SELECT q.*,
(
    SELECT TOP 1 r.Name 
    FROM Ranges r 
    WHERE q.MyDate <= r.[End date] ORDER BY r.[End date]
) AS SessionName
FROM [Records Query] q

    Here, I assume that your date ranges are stored in a table called Ranges and that the date field in your Records Query query is called MyDate (change this to suit your data).

    In the above example, I'm assuming you have the following setup:

    Ranges

    Ranges

    Records Query

    Records Query

    SQL

    SQL

    Result

    Result

    Unfortunately, you cannot simply inject this subquery into an UPDATE query -

    UPDATE [Records Query] q
SET q.Session = 
(
    SELECT TOP 1 r.Name 
    FROM Ranges r 
    WHERE q.MyDate <= r.[End date] ORDER BY r.[End date]
)

    ...as the resulting query will not be updateable (as the new value references the table being updated).

    As such, you will likely need to output the session names to and primary key from your Records Query query to a local table which can then be used to update your query - others may hopefully know a more elegant way to avoid this.