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.
sql ms-access access-vba ms-access-2016
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
asked Nov 13 '18 at 22:08
AllanAllan
11
add a comment |
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.
sql ms-access access-vba ms-access-2016
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
asked Nov 13 '18 at 22:08
AllanAllan
11
add a comment |
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.
sql ms-access access-vba ms-access-2016
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
asked Nov 13 '18 at 22:08
AllanAllan
11
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.
sql ms-access access-vba ms-access-2016
sql ms-access access-vba ms-access-2016
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
asked Nov 13 '18 at 22:08
AllanAllan
11
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
asked Nov 13 '18 at 22:08
AllanAllan
11
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
edited Nov 13 '18 at 23:04
Lee Mac
4,10431441
4,10431441
asked Nov 13 '18 at 22:08
AllanAllan
11
asked Nov 13 '18 at 22:08
AllanAllan
11
asked Nov 13 '18 at 22:08
AllanAllan
11
11
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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]
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
add a comment |
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
Records Query
SQL
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.
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
@Allan Are you saying that theSessionNamefield of my first SQL statement is empty?
– Lee Mac
Nov 14 '18 at 23:17
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
@Allan Did you changeMyDatein my code to the name of your date field inRecords Queryas instructed?
– Lee Mac
Nov 14 '18 at 23:41
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53290242%2fms-access-automatically-fill-query-column-based-on-start-and-end-date%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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]
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
add a comment |
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]
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
add a comment |
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]
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
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]
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
answered Nov 14 '18 at 9:08
GustavGustav
29.7k51835
29.7k51835
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
add a comment |
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Sorry i'm a bit new. Where do i type this
– Allan
Nov 14 '18 at 17:12
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
Open a new query, switch to SQL view, paste code. Of course, change names of table/query and fields to those of yours.
– Gustav
Nov 14 '18 at 21:23
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
I tried this, but the value in the Session Name column does not say the session name. The only thing it does is filters the records based on the last session
– Allan
Nov 14 '18 at 23:05
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
Then you probably have mistyped something and/or the dates fits that session only. We have neither your data nor your query, so it is not easy to be more specific.
– Gustav
Nov 15 '18 at 7:55
add a comment |
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
Records Query
SQL
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.
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
@Allan Are you saying that theSessionNamefield of my first SQL statement is empty?
– Lee Mac
Nov 14 '18 at 23:17
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
@Allan Did you changeMyDatein my code to the name of your date field inRecords Queryas instructed?
– Lee Mac
Nov 14 '18 at 23:41
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
add a comment |
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
Records Query
SQL
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.
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
@Allan Are you saying that theSessionNamefield of my first SQL statement is empty?
– Lee Mac
Nov 14 '18 at 23:17
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
@Allan Did you changeMyDatein my code to the name of your date field inRecords Queryas instructed?
– Lee Mac
Nov 14 '18 at 23:41
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
add a comment |
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
Records Query
SQL
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.
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
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
Records Query
SQL
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.
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
edited Nov 14 '18 at 23:45
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
answered Nov 13 '18 at 23:01
Lee MacLee Mac
4,10431441
4,10431441
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
@Allan Are you saying that theSessionNamefield of my first SQL statement is empty?
– Lee Mac
Nov 14 '18 at 23:17
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
@Allan Did you changeMyDatein my code to the name of your date field inRecords Queryas instructed?
– Lee Mac
Nov 14 '18 at 23:41
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
add a comment |
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
@Allan Are you saying that theSessionNamefield of my first SQL statement is empty?
– Lee Mac
Nov 14 '18 at 23:17
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
@Allan Did you changeMyDatein my code to the name of your date field inRecords Queryas instructed?
– Lee Mac
Nov 14 '18 at 23:41
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
For some reason it still dosen't say the session name, in the session column
– Allan
Nov 14 '18 at 23:14
@Allan Are you saying that the
SessionName field of my first SQL statement is empty?– Lee Mac
Nov 14 '18 at 23:17
@Allan Are you saying that the
SessionName field of my first SQL statement is empty?– Lee Mac
Nov 14 '18 at 23:17
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
Yes. Really not sure why, I double and triple checked to make sure i dident make a mistake
– Allan
Nov 14 '18 at 23:38
@Allan Did you change
MyDate in my code to the name of your date field in Records Query as instructed?– Lee Mac
Nov 14 '18 at 23:41
@Allan Did you change
MyDate in my code to the name of your date field in Records Query as instructed?– Lee Mac
Nov 14 '18 at 23:41
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
@Allan I've updated my answer with screenshots.
– Lee Mac
Nov 14 '18 at 23:45
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53290242%2fms-access-automatically-fill-query-column-based-on-start-and-end-date%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
