Auto Generated Shift Patterns In access 2003

Question

0.00/5 (No votes)

See more:

Hello

I'm looking for some solution i bet the question was ask like milion times here but i can't find any answares probobly i'm looking wrong

I want to create a database with employees and then i want to create shift patterns for them

Example:

Patterns:

1 - Sunday IN Monday IN Tuesday IN Wednsday OFF Thursday OFF Friday IN Saturday IN

2 - Sunday IN Monday OFF Tuesday IN Wednsday IN Thursday OFF Friday IN Saturday IN

3 - Sunday OFF Monday IN Tuesday IN Wednsday OFF Thursday IN Friday IN Saturday IN

Then after the shift patterns are set up I want to be able to add them to each of the employee in my database

Then from all of that I want to creat a week view what will show whos IN and who is off the problem is i want to be able to generate that for 52 weeks and every time I add an employee and i set up a starting pattern it will generate the rota for the employee to rest of the year

if the employee start at pattern 3 next week he will have rotation 1 and week after 2 and so go on to rest of the 52 weeks

It's a roster function but i trying to creat something and i'm just lack of skill to do that

Any one can help or have something like that what I can use in access ?

Posted 16-Jan-15 22:53pm

SebSCO

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2015-01-16T23:40:00

Solution 1

There are 2 parts to consider - the database (Access) part and the script (VBA) part:
1. Database part: you need at least 2 tables, for example:
First table called 'shift' that contains 2 fields: 'shift_id' (primary key), 'shift_desc'
that stores the patterns, e.g.
shift_id = 1, shift_desc= 'Sunday IN Monday IN Tuesday IN Wednsday OFF Thursday OFF Friday IN Saturday IN'
Second table called 'employee_shift' that contains at least 2 fields: 'employee_id' (primary key), 'employee_shift_id' (foreign key to 'shift_id' in the 'shift' table), etc
employee_id = '1'
employee_shift_id = 1
etc
That should be suffice for the database part.
2. As for generating the shift roster, that should be done by the script (you are using VBA) based on the business rules mentioned in your question.
Last but not least, you should learn database design in order to really understand how databases are designed and work, start from Introduction to database design[^]

Posted 16-Jan-15 23:40pm

Peter Leow

Updated 16-Jan-15 23:41pm

v2

Comments

SebSCO 17-Jan-15 7:12am

All done no problem with that but i'm looking to be able to add more shifts and set them as day In/Off using yes/no and it puzzle me how to creat that table or relationship

relating employee with shift is not a big deal but relating the days off or days in to the shift making me a problem

as later i want to realte it as well to calendar function to show it in a grid

Whre it will lokk more like

WEEK 3 18/01/2015
18/01 19/01 .......
Employee name Sunday Monday .........

David IN OFF

and so go on all list of employees
Coz in case one of them request a day off swap i need to be able to swap it
I will build that using CrossTab Queries but first i need to get the tables right and relationships between them

Peter Leow 17-Jan-15 7:50am

Based on your addition input, you may improve on the design of the 'shift' table with the following fields:
shift_id, sun, mon, tue, wed, thu, fri, sat
where the sun to sat fields represent the days of the week and take the value of either 1 (for IN) and 0 (for OFF) based the shift pattern.
In this way, you can find out an employee's shift pattern by referring to his employee_shift_id in the employee_shift table.
As for swapping which should involves 2 employees, you can capture this info in another table say 'swap_shift' with fields like swap_from_employee_id, swap_to_employee_id, date_swap etc.
Logically, before any swap detail can be stored into this swap_shift table, the program has to verify few things: 1. the swap_from_employee_id is really supposed to be 'IN' based on his shift pattern, 2. likewise for the swap_to_employee_id who is supposed to be 'OFF' on the date of swap.
To find out if an employee is off or in on a particular day, the program should first check out the swap_shift table, followed by the employee_shift table.
As for finding the day of week for a date, you can always ask Google.

SebSCO 17-Jan-15 8:11am

thank you

it helps me a lot

To sort the dates i wil just create tblCalendar with fields like calendardate month nr month name and same for days day nr and day name and then i will relate fields from shift by crosstab to make sure the sun to sat will be 1 to 7 ect and I pull names of the days from calendar day name field

and that i think will sort it

now i need only to sort the scripts to generate the roster from week 1 to week 52 and when all that will start work i can focus on rest

eh challenging

Thank you for ur time Sir :)