Automatically assigning 2 two digit values to each day of the year

[nvrwrkn]

Got a need to assign 2, two digit codes to each day of a yearly calendar. An example would be:

01 Jan 12 = 61,62
02 Jan 12 = 62,63
03 Jan 12 = 63,64


04 Jan 12 = 64,65
05 Jan 12 = 65,66
06 Jan 12 = 66,61

The point of this is that employees are assigned one of these 60 series numbers to indicate a day off group. Everyone is assigned a day off group and obviously, people's days off work are dictated by the day off groups listed as off for that day. In the above example, on 01 Jan 12, people in day off group 61 & 62 are off.

Our personnel table contains the day off group for everyone in the company.

The end goal is to create a simple app that when a user clicks on a calendar day and submits it, a report pops up and shows everyone who's working on that given day.

So I can let the app hit the personnel table with each request to pull the day off group numbers for a given day. What I think I need locally is a table generated that contains every day of the year and the associated day off group numbers, or conversely I think it can also be done on the fly with VBA.

Now comes the hard part.... is it better to create a static table that contains the days or have it done on the fly through code? As far as the DOG assignments to days, it never changes. That is that it doesn't matter what year we go into, the DOG assignments stay the same so I shouldn't need to create a calendar every year as the value should be calculated with a static formula.

Just need help on how to create the formula to calculate the 2 day off group numbers for any given day no matter when it is. I seem to think as long as we know one day's day off group numbers we can work off that forever.

Make sense?



*edit..... I'm thining this could be something where a recordset is opened and 01 Jan 12 is assigned 61,62 and the rest is calculated by taking the date value from a calendar and comparing datediffs and somehow calculating the DOG values from the known first value....just can't wrap my head around it, like do I have to have a static value for 01 Jan 12 in a local table and refer to it in the module or can I create a temporary variable with it and then make a recordset off the values I pull from the personnel table?

*edit 2..... as I think now, what about just a simple query that calcs the 2 day off groups then shows me the results from the personnel table where matching day off groups are found? Also could link that to the "time off" table where those people who've been granted time off on a certain day could be removed from the personnel list for a given day.


aaaand edit 3..... forgot to add 3 extra day off groups. day off group 8 is Saturday-Sunday, d.o.g. 9 is Friday-Saturday and d.o.g. 10 is Sunday-Monday. Those folks are in set dog's and their days off never change like everyone else's do.

[HMEpartsmanager]

Got a need to assign 2, two digit codes to each day of a yearly calendar. An example would be:

01 Jan 12 = 61,62
02 Jan 12 = 62,63
03 Jan 12 = 63,64
04 Jan 12 = 64,65
05 Jan 12 = 65,66
06 Jan 12 = 66,61

aaaand edit 3..... forgot to add 3 extra day off groups. day off group 8 is Saturday-Sunday, d.o.g. 9 is Friday-Saturday and d.o.g. 10 is Sunday-Monday. Those folks are in set dog's and their days off never change like everyone else's do.

Just for clarification, you have DOG: 61, 62, 63, 64, 65, or 66

There is no 67?

Group 8 = 68?
Group 9 = 69
Group 10 = 70?

I was trying to review it as it looks intriguing, but the actual numbers assigned to these DOG's is a bit sketchy after 66?

Can you clarify the above what the actual group numbers are?

Tim

[nvrwrkn]

I haven't forgotten about the thread I started! I take all my vacation at the end of the year and I should have waited a bit before starting this thread.

Anyway, yes there is no 67,68,69,70. The company uses 8,9,10 for fixed day off groups. So I guess when a day is evaluated the 8,9,10 can be evaluated by weekday I suppose. But along with those 3 day off groups, other 60 series day off groups will also happen on those days. As an example, the weekend of 15 Dec 2012, there are 4 day off groups assigned, 62,63,8,9.

This is because the 60 series advances by one each day and each day has (2) 60 series day off groups in it, and on FRI, SAT, SUN, MON there are the additional day off groups added in.

Thanks for looking at this Tim.

The overall goal of this function is to allow the company to look at any given day and reveal which day off groups are off that day. The point is to provide employee manpower projections for any date in the future as well as creating reports that will show a monthly calendar and the people who are off on each day.

We also would need to show people who have taken vacation off on those days as well, I think I can handle that though.