Appending an auto-increment date to a Query

[faap]

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

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?

 

[theDBguy]

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?

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.

 

[faap]

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.

Ok, so:


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

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

 

[faap]

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.

Thanks Pat, that's why at this stage I am attempting to establish whether what needs to be done can be done via SQL. My belief is that it can, only I'm not fluent enough in SQL to come up with it easily.


At the end of the day, if it's not easily doable, I may ditch the Access prototyping idea and move on to other solutions.

 

[theDBguy]

Ok, so:


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

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

Hi. To give you an idea of one way to generate new dates, please take a look at this old article.

 

[theDBguy]

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.

It wouldn't, and that was my point earlier about how we can easily create new dates but not be able to assign people to them.

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.

This sounds like an interesting idea, but the next issue would be how can we make the list of people go back to the beginning when we've run out of people to assign?

Sounds like given enough time, an SQL only solution may be possible. We just don't know if there's enough time for the project's deadline.

 

[faap]

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.

 

[theDBguy]

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.

Hi. No sweat. Please let us know what you decide.

 

[theDBguy]

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.

I get that but what I was thinking was let's say we have a list of 10 names and the last one used was #7th. So, we know we need to start with the 8th person. However, what if the user wants to generate 5 dates? That means we'll have to assign the first 3 dates to #8, 9, and 10th persons. Then the next two dates will have to start again with person #1. Would this be easy to do using a query? I don't know or I'm not sure. I'm just thinking out loud too.

 

[theDBguy]

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.

Good luck and enjoy!