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

The_Doc_Man said:

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.

Click to expand...

jdraw said:

Just reading this thread and having same confusion as the others.
It sounds like: (may just be another interpretation)
You have 11 people. Each day you need to assign 1 person to something. So if you want to schedule, say 14 days worth of assignments, you assign person1 to day1, person2 to day2 up to day11. Then on day12 you assign person1, day13 person2, day14 person3.. Then the next round of assignments begins with person4....

This seems like a standard rotation, but what's standard about that?
This also assumes that the number of people is fixed (11). However, if new people enter your resources available list, you can adjust the assignments using the total people in the rotation. Similarly, if your people resource list gets diminished (someone leaves...) then your rotation list must be reduced.

I agree with Pat that defining the problem is most difficult.
I also agree with Doc that you ignored/didn't account for 9/2 (and 9/5), but there may be a rule or 2 that you have not identified in your posts.

Why no vba???

Click to expand...

1. The organisation I'm helping with this has a policy that blocks VBA
2. I am prototyping this as a proof of concept, and to do this I have identified Access as a relatively straightforward prototyping solution
3. This means that the final solution may end up being developed with a different technology. This is the reason why I was hoping that developing this via Query/SQL would suffice: portability. SQL should be ported more easily than VBA anyway

faap said:

Thanks for the questions


Firstly about VBA

1. The organisation I'm helping with this has a policy that blocks VBA
2. I am prototyping this as a proof of concept, and to do this I have identified Access as a relatively straightforward prototyping solution
3. This means that the final solution may end up being developed with a different technology. This is the reason why I was hoping that developing this via Query/SQL would suffice: portability. SQL should be ported more easily than VBA anyway

I want to go back though and check with all of you:



I am concerned with how to come up with a date that increments itself by one day per record (regardless as to what day it is, whether it's a weekend, a holiday, etc. - the rule is clear in my mind: you have the latest date in a set? Just add one day for the next record) -this should come as part of an append query which creates new records in a FIFO/round-robin way.


Are you guys saying that this is not easily achievable just by using Query/SQL technology, or that it's hard but it would be much easier via a programming language, such as VBA?


Or is it the initial requirement not clear enough?

Click to expand...

theDBguy said:

Hi. Creating sequential dates using SQL is not a problem. It's the other part of your requirements that's complicating things a bit.

Click to expand...

faap said:

Thanks - let me see if I understand you: if I had an SQL statement that creates sequential dates, what requirement would it clash with, exactly?

Click to expand...

The organisation I'm helping with this has a policy that blocks VBA

Click to expand...

theDBguy said:

Let's say the max date was 10/16/2019 and you want to generate 3 new dates, then using SQL, it's easy enough to generate 10/17/2019, 10/18/2019, and 10/19/2019. What's not so easy using SQL only is to determine the person to assign to each of the new dates.

Click to expand...

SELECT [Schedule].DateServed, [Schedule].PPId
FROM Schedule
WHERE id IN (SELECT MAX(id) FROM Schedule GROUP BY Schedule.PPId);

1. the number of future iterations that the SQL statement runs for is given by x (x being a variable being requested at query runtime)
2. The code above is for a SELECT query (meaning, with that code I am not expecting to write new records, but just to see what would happen if I ran the append query: so the code above would be a preview of what the append query would do)
3. whatever code we have for SELECT query could relatively easily be ported into a INSERT INTO query

Pat Hartman said:

Let's get back to:
That's sort of like asking you to dig a hole 6 feet deep but not allowing you to use a shovel. I remember the year we literally got snowed into our house. Both the front and back doors were blocked by three foot drifts and since the screen door opens out, we were stuck. We eventually realized that we could get out using the sliding door but of course it also had a three foot drift and all the shovels were in the garage. My husband eventually took a sturdy metal frying pan and a broom and used them to move enough snow so he could walk to the garage and get a shovel. Can you determine what the basis of this silly rule is and what it would take to change it? Otherwise we're going to have to use a bazooka to swat this fly.

Click to expand...

faap said:

Ok, so:


In this post the query in the first picture is generated by this SQL statement

Code:Copy to clipboard

SELECT [Schedule].DateServed, [Schedule].PPId
FROM Schedule
WHERE id IN (SELECT MAX(id) FROM Schedule GROUP BY Schedule.PPId);

Essentially Schedules is the table where the teachers are listed, next to the date they taught.

The SQL code above attempts to offer a view where teachers are shown just once with the latest date they taught, if that makes sense?


Assuming

1. the number of future iterations that the SQL statement runs for is given by x (x being a variable being requested at query runtime)
2. The code above is for a SELECT query (meaning, with that code I am not expecting to write new records, but just to see what would happen if I ran the append query: so the code above would be a preview of what the append query would do)
3. whatever code we have for SELECT query could relatively easily be ported into a INSERT INTO query

how would you go about modifying that SQL statement so that it generates new dates?


Thanks

Click to expand...

I may ditch the Access prototyping idea and move on to other solutions.

Click to expand...

Pat Hartman said:

Do they let you write macros in Excel or Word? That is done with VBA.

I didn't read the link posted by theDBGuy but I have used that technique to force queries to generate records. Normal SQL can only retrieve something that already exists. Making a Cartesian product by using a special date table lets you get x dates for y records but you have two variables here - people and days so I'm not sure that without code, this will work.

Click to expand...

Just thinking out loud. If you make a query that selects x trainers and assigns them a sequence value 1-x and then you select x dates (without using the cartesian product) starting at the day after the last set or at a specified date and assign them a sequence value of 1-x, then you can create a query that joins the selected people to the selected dates on the sequence number so person 1 gets date 1 and person 2 gets date 2,etc.

Click to expand...

faap said:

Thanks both.


I am going to study the blog post more in detail tomorrow and reflect on the best way forward.


I really do appreciate the time you guys have spent on this, thanks again.

Click to expand...

how can we make the list of people go back to the beginning when we've run out of people to assign?

Click to expand...

Pat Hartman said:

The query that selects the trainers has to start with finding the last trainer who was assigned. This can be done by the button click in the form that runs this. Look up the last person who did training. Then look up the person who is next in line and place that ID in a textbox on the form where the query can find it.

Click to expand...

Pat Hartman said:

I can't think of an SQL trick that would cause the name list to wrap. I'm off to play bridge. I've made my contribution to the logic. SQL is not a procedural language. It operates on sets. OK - new thought. Use two queries. One that gets the starting person to the end of the list and the second to get the entire list. Maybe these queries could append to a temp table. We'd have to know the maximum number of assignments to be made at one time because it might take more than x to the end plus the whole list once. Maybe you need x to the end plus the whole list 5 times. It doesn't matter if you pick more names than dates. There just won't be any matches for the extra names.

OK, I'm really done now. I'll be back in a couple of days. I'm not taking my computer.

Click to expand...