Shift circular event

[Manos39]

hello, i am trying to accomplish this
in work there are 5 groups or teams working each day and weekends, in 5 different shifts (eg morning, afternoon, night, rest,day off).
like in this example

TeamShiftstbl

ID	teamID	DateParam	shift
1	1​	1/11/2022​	Night​
2	2​	1/11/2022​	Rest​
3	3​	1/11/2022​	Day Off​
4	4​	1/11/2022​	Morning​
5	5​	1/11/2022​	Afternoon​
6	1​	2/11/2022​	Rest​
7	2​	2/11/2022​	Day off​
8	3​	2/11/2022​	Morning​
9	4​	2/11/2022​	Afternoon​
10	5​	2/11/2022​	Night​
11	1​	3/11/2022​	Day Off​
12	2​	3/11/2022​	Morning​
13	3​	3/11/2022​	Afternoon​
14	4​	3/11/2022​	Night​
15	5​	3/11/2022​	Rest​

as this is a rule, and 1st of November 2022, team1 works Night, lets say another given day (DateParam) eg 1st of May 2023 what does Team1 work? It is circular
and i would like your help to calculate that

 

[The_Doc_Man]

A little bit more info might be needed to define exceptional cases. For example, is there ever a holiday when NOBODY works? Is this only for weekdays? Are there any special cases?

Here is the basic math for the "no exceptions, no specials, no holidays" case:

1. Pick a reference date, call it RefDT, and use this expression: CurDateNum = CLNG( Date() - RefDT )
2. Build a table of possible actions with OptID and OptTxt
<0, Rest>, <1 Day Off>, <2, Morning>, <3, Afternoon>, <4,Night>
Actually, you might have to "diddle" with the number assignments to get the order you wanted for the first case - and that doesn't matter as long as those assignments stay the same once you start the process.
3. Build a table of teams with whatever else you need and the number of the team as TeamID.
4. Now to determine the correct action, select the element from the action table based on OptID = ( CurDateNum + TeamID ) MOD 5

You could write code to generate this into a table via recordset operations, or you could run a multi-layer query or complex join query once per day, or you could have a calendar table and use it to feed the CurDateNum computation. You didn't say your level of code proficiency so I'm not sure which way to go from here and the code is just tricky enough that I don't want to waste my time going in a direction that's not right for you.

 

[Manos39]

Thank you and excuse me for not reporting my level of code proficiency. For that its primitive in comparison with yours i suppose. I think people work in teams and irrelevant from personal duties name holidays etc their teams always work. If it wasnt so it would not be a shift covering 24/7 as my question imply.
So i need the team to match the case not the person in that. Its not always put your code here to copy just an idea for someone to try. So i ll try that you said with my poor knowledge.

 

[The_Doc_Man]

Thank you and excuse me for not reporting my level of code proficiency.

First and foremost, you don't need to apologize. It is something that doesn't come up that often and very few people offer it. The only reason I asked was because I saw this could get complex and I didn't want to introduce any extra difficulty.

Now, the next part of this is how you want it to be run. One of my questions perhaps was asked in a way that you missed it. Are you trying to do this one day at a time or did you want to build an arbitrary "futures" calendar that goes some number of days into the future?

Part of this particular problem is if you want to build a list of calendar days, you either need a separate calendar to DRIVE the list, or you need to write some kind of code that will generate a day list - but then you have to have a way to tell it you have enough entries - i.e. STOP building a list after you have enough. So give us an overview of the end goal, including WHAT you wanted it to do and HOW & WHEN (HOW OFTEN) you wanted to do it. Part of any project whether large or small is proper prior planning. Decide where you want to go with this - because if you don't, how will you ever know you got there?

 

[ebs17]

I was tempted to create the desired table shown as a query in a simple way. Here's the approach:

SELECT
   Q.AnyDay,
   Q.TeamID,
   S.ShiftDescription
FROM
   (
      SELECT
         DateAdd("d", N.I, #11/1/2022#) AS AnyDay,
         T.TeamID,
         (N.I + T.TeamID - 1) mod 5 + 1 AS X
      FROM
         T999 AS N,
         tblTeams AS T
   ) AS Q
      INNER JOIN tblShifts AS S
      ON Q.X = S.ShiftID

 

[Manos39]

First and foremost, you don't need to apologize. It is something that doesn't come up that often and very few people offer it. The only reason I asked was because I saw this could get complex and I didn't want to introduce any extra difficulty.

Now, the next part of this is how you want it to be run. One of my questions perhaps was asked in a way that you missed it. Are you trying to do this one day at a time or did you want to build an arbitrary "futures" calendar that goes some number of days into the future?

Part of this particular problem is if you want to build a list of calendar days, you either need a separate calendar to DRIVE the list, or you need to write some kind of code that will generate a day list - but then you have to have a way to tell it you have enough entries - i.e. STOP building a list after you have enough. So give us an overview of the end goal, including WHAT you wanted it to do and HOW & WHEN (HOW OFTEN) you wanted to do it. Part of any project whether large or small is proper prior planning. Decide where you want to go with this - because if you don't, how will you ever know you got there?

Thank you for the solution, and to admit yours is the one working for me no more complexity was needed for there was already a table with a field DD with numbers in there from 1 to 31 and with dateserial function and parameters from a form i was able to append field to teams for each day of a desired month and that wich wasnt achieved was how to cirlulate the shifts for each one
That said back to homework becase from that and on and for my purpose. It is to make a report previewing for a selected team (and ofcource participants) what is their shift each day on a due month

like this

TEAM A
1 2 AND SO ON to 31 (DEPENDS ON MONTH SELECTED)
N REST
ALEX
VANGELIS
EMMANOUEL
MICHAEL

 

[Manos39]

I was tempted to create the desired table shown as a query in a simple way. Here's the approach:

SELECT
   Q.AnyDay,
   Q.TeamID,
   S.ShiftDescription
FROM
   (
      SELECT
         DateAdd("d", N.I, #11/1/2022#) AS AnyDay,
         T.TeamID,
         (N.I + T.TeamID - 1) mod 5 + 1 AS X
      FROM
         T999 AS N,
         tblTeams AS T
   ) AS Q
      INNER JOIN tblShifts AS S
      ON Q.X = S.ShiftID

Thank you! i saw it and it warks fine as to Doc_Man solution
need to ask what is table T999, what is its purpose.. question generated after the fact i've never seen query made inside of query ..

 

[ebs17]

T999 is a table that provides consecutive numbers from 0 to (enough) in one column. It is used twice in the query, once to increment the date from a fixed start date, once to calculate the key for shift.

 

[Manos39]

T999 is a table that provides consecutive numbers from 0 to (enough) in one column. It is used twice in the query, once to increment the date from a fixed start date, once to calculate the key for shift.

ebs17, because here knowledge is slow coming could i replace it with my dayofmonthtbl which excists already? it was made on same purpose i suppose

 

[ebs17]

my dayofmonthtbl

A table with daily numbers is not really useful. If so, then you use a calendar table that contains consecutive calendar days, i.e. also spanning months and years.

Date values are numbers internally, so consecutive date values are also consecutive numbers.

? Date, Date*1, Date+1, (Date+1)*1, Date+2, (Date+2)*1
30.10.2022     44864        31.10.2022     44865        01.11.2022     44866

So you could also use a calendar table instead of the number table. As written above: It was just a feasibility approach...

 

[Manos39]

The approach is great. I am trying to combine combine your query with my append query for my goal and that is tricky too..

 

[The_Doc_Man]

Looks like this ball is rolling so I'll just stand by and wait in the wings until I need to throw in something else. Glad I could get you started.

 

[Manos39]

ebs17 one small favour i noticed query fails to produce first day of a given month do you confirm that? oh no its not i must had seen wrong

 

[Manos39]

Another question. suppose i need not to raise blud preasure
Is there a way ebs17, Doc_Man in your query to include code so it shall listen to parameters from form a) for year like 2022, 2023 etc and
b) for month like selected 1,2,3, etc from my form, so query not only produce shifts for teams but also the actual days a given month has. If having that, desired report would be an easy one later.


Otherwise because i might be a pain, how do i combine this solusion with mine (---whitch is part working becase it wouldnt produce shifts just the right days to append to table with all teams in a day---)

i have a single append query to insert rows in table
dutyrecordstbl
=============
ID
vardiaID (teamID)
YpiresiaDate (dutyDate)
shift

another table
dayofmonthtbl
========
DD (from 1 to 31 in a field)

append Q sql

INSERT INTO dutyrecordstbl ( YpiresiaDate, vardiaID, shift )
SELECT DateSerial(Eval([Forms]![ypovolesfrm]![ZYEAR]),Eval([Forms]![ypovolesfrm]![ZMONTH]),[dd]) AS iDate, QrVardSchedule.vardiaID, QrVardSchedule.ShiftL
FROM dayofmonthtbl, QrVardSchedule
GROUP BY DateSerial(Eval([Forms]![ypovolesfrm]![ZYEAR]),Eval([Forms]![ypovolesfrm]![ZMONTH]),[dd]), QrVardSchedule.vardiaID, QrVardSchedule.ShiftL
HAVING (((DateSerial(Eval([Forms]![ypovolesfrm]![ZYEAR]),Eval([Forms]![ypovolesfrm]![ZMONTH]),[dd]))<=DateSerial(Eval([Forms]![ypovolesfrm]![ZYEAR]),Eval([Forms]![ypovolesfrm]![ZMONTH])+1,0)));

 

[arnelgp]

i create a table as in your table (tblInitSchedule) in post #1 but only with first 5 records.
create query qryAppendShifts to append the dates and teamID.
next update the shift in dutyrecordstbl using query, qryUpdateShifts.

all the above is in your form createschedulefrm, when you press the "Create" button.

 

[The_Doc_Man]

Remember that dates are actually just numbers using the concept of a 'timeline' from a reference date. The units of NOW are just days and fractions of a day since the reference. Access has ways to take a number derived from a date and convert it back to a date parts including day of the week, day of the month, year, month, etc. So when you compute your next date for your table, that information is available easily.

Knowing the actual number of days a given month has is easy enough. If you have the year and the month, then day 0 of that month is actually the last day of the previous month. So the Day() function of a date synthesized from the month, year, and day 0 will tell you the days in the previous month - and it will even be accurate for leap year cases.

 

[ebs17]

In addition to the statement by The_Doc_Man: A calendar table was mentioned above. I tend to use one very quickly. Once it has been thought through and can be used in a variety of ways, also usable by other actions that you don't even know as a task.

A calendar table is an auxiliary table, outside of the data model with relationships. Different rules apply here. Duplicates and calculated values are explicitly saved here. This is also possible because a calendar cannot easily change, the paper calendar on the desk is also useful.

The calendar table contains the calendar day as a primary key continuously for a sufficient period of time. Formats for the date are stored as calculated values in other fields (year, month, quarter and many others as required). Each of these columns is indexed. This puts you in a situation where, if you can formulate good queries, it is better and faster to look up values in the calendar table instead of doing the calculations yourself over and over again.

Calculate once, save and look up when needed instead of calculating in each data record every time. Done right, it reduces effort and improves performance. In addition, certain tasks only really become solvable. Remember that they wanted to exclude public holidays and school vacations.

 

[Manos39]

i create a table as in your table (tblInitSchedule) in post #1 but only with first 5 records.
create query qryAppendShifts to append the dates and teamID.
next update the shift in dutyrecordstbl using query, qryUpdateShifts.

all the above is in your form createschedulefrm, when you press the "Create" button.

Arnel it is great. I shall try to match information with greeklish named fields and go on to report build. Thank you ALL

 

[Manos39]

Arnel made some tests before adopring your sample DB and with years and months
and i found that it is working eg for year 2022 , month 11th, and next month 12th, it is ok for shifts and teams..
but if gong to month 10 same year, shift for team 1 should be afternoon not rest and so on its wrong if put previus months from 11th this year

Is it a matter of it is working ok for future months not passed?

 

[arnelgp]

here i made change to the module.