Appending an auto-increment date to a Query (1 Viewer)

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
Hey everybody,


I am picking Access up again after a long time. I am trying to insert (append) some new records onto an existing table. These new records need to have a date that auto increments by one day for each new record, based on the existing latest date,but I'm hitting a roadblock.


In the query I've managed to cobble together the following expression:


Code:
MyField: CDate(DMax("[Latest Arrivals Query].[DateServed]","Latest Arrivals Query")+1)
but the result I get is not what I expected.


What I expected: for each new record, a new day would be presented, so that if, for instance, the latest date on record is 3rd Sep 2019, the first new record would have 4th Sep 2019, the second new record would have 5th Sep 2019 and so on.


What I get: For each record, I am getting the same date, which is the latest date+1, so as in the example above I would get 4th Sep 2019 for all record.


I think the issue should be solvable either through a Query expression or an SQL statement, without the need for VBA code.



Does the problem make sense?



Happy to expand on the db structure if that helps.




Thank you
faap
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,358
Hi. That result was to be expected because the query engine calculates the value first and then applies it to the process. What you want is also a dynamic way to increase the number of days to add as the query continues with each record. For example, you might be able to use the record count.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:33
Joined
May 7, 2009
Messages
19,175
the problem is that the query is evaluating your expression only once.
if you introduced a VBA, the expression will be evaluated on each record.
 

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
For example, you might be able to use the record count.


Thank you theDBguy, I appreciate it, you gave me some food for thought, I will try with a record count :D
 

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
the problem is that the query is evaluating your expression only once.
if you introduced a VBA, the expression will be evaluated on each record.


I see, thanks arnelgp - the issue is, I am unable to use VBA for this project.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,358
Thank you theDBguy, I appreciate it, you gave me some food for thought, I will try with a record count :D
Hi. You're welcome. My usual approach is to use a tally table. See this blog to see what I mean.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,358
Thanks again. I will study your blog posts about it.
Good luck. Please let us know if you get stuck or found a better way to do it. Cheers!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2002
Messages
42,981
Can you explain in words why you want to change the date on each record to a new date and what that new date would be? A before and after example would be very helpful.
 

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
Can you explain in words why you want to change the date on each record to a new date and what that new date would be? A before and after example would be very helpful.


Thanks Pat. Firstly I want to clarify, I am not intending to change the date on each record. I am looking to create new records and, along with that, for each new record created I want to set a new date.


As to what the date would be:

let's say that the following query lists all the volunteers providing induction training to new arrivals, which take place daily. Next to each volunteer's name you have the date of when the training took place.





What I am looking to do is extend this type of schedule in the future, so that a proposed training timetable can be created. Assuming that the latest date is today, the query would create the list of volunteers starting from tomorrow's date, and incrementing the date by 1 day for each record (i.e. each volunteer).


So the end result of the append query would look like this (this is the table view, the records in blue are the new ones)





In practical terms, when the query is launched, I would probably prompt for a digit expressing how many days (records) in the future I am intending to create, and so the routine would loop according to how many days I entered in there.
 

Attachments

  • query01.png
    query01.png
    7.1 KB · Views: 446
  • query02.png
    query02.png
    11.3 KB · Views: 374

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
I posted a reply to last post but it seems like it's gone into a moderation queue.
 

isladogs

MVP / VIP
Local time
Today, 12:33
Joined
Jan 14, 2017
Messages
18,186
It did indeed. Now approved.
Until you have ten posts you need to zip attached files.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2002
Messages
42,981
OK, you want to create x days worth of records and you want to use each of the 11 teachers once? So if x is 2, then 22 records will be created. Should weekends and holidays be excluded? How do you know that the trainers will be available on the specified dates? I think this is a little more complicated.

Typically, when you are making schedules, you do them one or two weeks out so people can rearrange their lives or maybe you use a table with not-available dates for people so you don't schedule them if they're on vacation.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,358
OK, you want to create x days worth of records and you want to use each of the 11 teachers once? So if x is 2, then 22 records will be created. Should weekends and holidays be excluded? How do you know that the trainers will be available on the specified dates? I think this is a little more complicated.

Typically, when you are making schedules, you do them one or two weeks out so people can rearrange their lives or maybe you use a table with not-available dates for people so you don't schedule them if they're on vacation.
That was a good question. My understanding was if the number of teachers were 11 and the number of days inputted was 10, then the last teacher will not be included. So, as Pat said, this is still a little confusing, I think (since there are at least two interpretations right now).
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2002
Messages
42,981
Problem statement is always the hardest part of the development job. By the time you really understand what needs to be done, implementation becomes almost trivial once you have a little experience.
 

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
OK, you want to create x days worth of records and you want to use each of the 11 teachers once? So if x is 2, then 22 records will be created.

No. If x=2 the routine should create 2 records, not 22.

My understanding was if the number of teachers were 11 and the number of days inputted was 10, then the last teacher will not be included.

That is correct. That's the specification I posted.


Should weekends and holidays be excluded? How do you know that the trainers will be available on the specified dates? I think this is a little more complicated.

Typically, when you are making schedules, you do them one or two weeks out so people can rearrange their lives or maybe you use a table with not-available dates for people so you don't schedule them if they're on vacation.
These questions really pertain to the operational aspects of the system, which are well in hand, although I do appreciate the concern. What I'm keen to explore is to how create new records via an append query that deliver an incremental date based on the existing latest date on record.

theDBguy mentioned that, because of how the query engine works, the whole recordset is evaluated first, and so using a record count instead of the formula I used might be a way forward.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,358
No. If x=2 the routine should create 2 records, not 22.

That is correct. That's the specification I posted.

These questions really pertain to the operational aspects of the system, which are well in hand, although I do appreciate the concern. What I'm keen to explore is to how create new records via an append query that deliver an incremental date based on the existing latest date on record.

theDBguy mentioned that, because of how the query engine works, the whole recordset is evaluated first, and so using a record count instead of the formula I used might be a way forward.
Hi. Thanks for the additional information. I have one more question. Let's say you have 10 teachers but you only wanted to schedule 5 days for now. That means teachers 1 to 5 will be added to the table and teachers 6 to 10 will not. Now, let's say, in the next go around, you want to schedule 3 days. Which teachers should be added to the table at this time? Teachers 1 to 3 or 6 to 8?
 

faap

Registered User.
Local time
Today, 13:33
Joined
Oct 14, 2019
Messages
18
Hi. Thanks for the additional information. I have one more question. Let's say you have 10 teachers but you only wanted to schedule 5 days for now. That means teachers 1 to 5 will be added to the table and teachers 6 to 10 will not. Now, let's say, in the next go around, you want to schedule 3 days. Which teachers should be added to the table at this time? Teachers 1 to 3 or 6 to 8?


Thx for the question theDBguy - the criteria is based on FIFO, meaning: if I schedule 5 days and 1-5 teachers are scheduled, next time around if x=3 then teachers 6-8 would be scheduled.


Hope it makes sense.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:33
Joined
Feb 28, 2001
Messages
27,001
Question: Suppose that a person misses being in this list? I see your list of 11 people - but what happens if somehow Walter was also supposed to be there but wasn't put in.

Question: I noticed you allowed a gap in dates in your example. (1/9 was followed by 3/9, not 2/9.) Did you want to preserve the date gap?

Question: Suppose you have 11 persons but choose to schedule 12 events? Are you looking to do round-robin scheduling? So that the first person in the 11/12 case would also be the last person? It is clear that you might want to schedule fewer than the total number of people, but what about greater than the total number? Can that ever happen?

Question: WHY are you unable to use VBA for the project? It would be a matter of near triviality to do this in VBA.
 

Users who are viewing this thread

Top Bottom