Hi,
Things are clearer now. I was assuming you where working by week, while,
indeed, your table is per day, not per week.
1+( DateDiff("d", #01-01-2005#, D) MOD 70 )
return the field to be used given the day D. As examples:
? 1+( DateDiff("d", #01-01-2005#, #04-01-2005#) MOD 70 )
21
? 1+( DateDiff("d", #01-01-2005#, #04-30-2005# ) MOD 70 )
50
as you mentioned it in your numerical example. Note that the main difference
is to use MOD 70 rather than MOD 10 (since you have 70 entities (fields), as
in 70 days, not 10 entities, as in 10 weeks).
The result is numerical, make sure you "cast" it to a string if you use a
DLookup, and to make it between [ ]. Otherwise, DLookup will return the
number, not the value hold under the field (I was missing that detail in my
previous answer).
DLookup( "[" & (1+( DateDiff("d", #01-01-2005#, D) MOD 70 )) & "]" ,
"tableName" , "Autonumber=1")
which, for first of April 2005 will become
DLookup("[21]", "tableName", "autonumber=1")
as example.
Note that I am not sure about the criteria, ie, about what Autonumber should
be equal to, but I assume that was something you had already solved. It is a
matter to pick a specific record. I used Autonumber=1 to generate a valid
syntax, but your case is surely different.
Remember that when you have a cycle with integers, MOD is generally involved
in the picture. Indeed, x MOD 3 returns the rest of the division by 3. So,
for x=1, 2, 3, 4, 5, 6, ... the result are 0, 1, 2, 0, 1, 2, 0, 1, 2, ...
ie, cycle of 3. Here, we need to have a cycle of 70, so we end up using
expression MOD 70. Note that the cycle would supply values from 0 to 69 and
we wish 1 to 70, so the 1+ added in front of the expression. The expression
is about to compute the difference in days since a given date where the
schedule have to return [1]. Anything left to do was a matter of syntax.
Hoping it may help,
Vanderghast, Access MVP
Paul B. said:
Sorry, I'm not sure I explained it correctly....
In the tblSchedule, the fields 1, 2, 3, 4, etc.... represent the day of
the
rotation. In other words, because it is a 10 week rotation, there are 70
fields. Each field then tells me what the employee is working.
The autonumber is the rotation number (row of tleSchedule) that applies to
the employee.
So I am trying to get to this, if January 1.05 is the start date....the
rotation ends on March 11/05, and begins again on March 12/05.
If I need to print the calendar for April, it would begin with field 21
and
end with field 50.
Is this clearer, or I am not understanding your solution??
Cheers
Michel Walsh said:
Hi,
Assuming the employee are the fields (employee 1 is under the field [1])
and that the schedules are the rows ( first schedule is row 1, second
schedule is row 2),. then, for day D, the schedule for employee 4 is
Dlookup( "4" , "tblSchedule", "Autonumber=" & (1+ (DateDiff("ww", D,
#01-01-2005#) MOD 10 )))
Note that is the autonumbers are 0 to 9 (rather than 1 to 10), remove
the 1
+ in front of ( DateDiff( ) MOD 10 )
Hoping it may help,
Vanderghast, Access MVP
Thanks for the reply....
I have spent the last few hours reviewing what you wrote and I am still
having trouble understanding it.
The table structure for the 'tblSchedule' is:
Autonumber | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | etc...
1 0 0 d d n n 0 0
2 d d n n 0 0 0 0
3 n n 0 0 0 0 0 0
4 0 0 7 7 7 7 7 0
etc...
d = dayshift
n= nightshift
7 = days M-F
etc...
Employee1 shift = 1
Employee2 shift = 2
etc...
Rotation start Date = Jan 01/05
So, this means that on Jan 05/05 Employee1 is on nightshift, Employees
2 &
3
are off, and Employee4 is on 7 hour day. etc...
Can you please break down your response to show me how the logic works?
Thanks again.
If the schedules are S0. S1, S2, ..., S9, one per week, with S0
occurring at a given date, say the #01-01-2005#, then at another day,
D,
the
schedule will be "S" & DateDiff( "ww", D, #01-01-2005#) MOD 10
Hoping it may help,
Vanderghast, Access MVP
I am working on a staff schedule database and I need to set a start
date
for
a 10 week rotation, for arguments sake, January 01, 2005.
Now when someone comes to me in October and says they need to know
what
shifts they are working in December, I want to pull their 10 week
schedule,
apply it to the rotation start date of Jan 01/05, and then show the
rotation
as it would fall for the month of December.
All you date and time gurus out there must have come across
something
like
this.
Any ideas greatfully accepted.
