CODEWITHSUNDEEP
sqlms-accessvbams-access-2016
Allan
Allan

Reputation: 82

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.

Upvotes: 0

Views: 1756

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

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.

Upvotes: 0

Gustav
Gustav

Reputation: 55841

You can join by a simple Where clause:

Select
     [Records Query].*,
     [Date Ranges].[Name] As [Session Name]
From
     [Records Query],
     [Date Ranges]
Where
     [Records Query].[Date] Between [Date Ranges].[Start date] And [Date Ranges].[End date]

Upvotes: 1

Related Questions