Employee scheduling program Results

Hello Everyone
I need to make an employee schedule in access. I am not sure if it is possible, but here are my constraints:

20 employees

5-6 different truck locations (sm1, sm2, sm3, sm4, sm5, sm6) These locations are scattered within our service area.

2 employees must meet at the same truck, (each truck has 2 employees working on it)

some employees can only meet at certain locations (ex. paul can meet at either sm2 or sm3)

all employees are PT so they can only work on certain days or a certain amount of days per week. (ex. paul can work up to 4 days a week and jim can work only sat and fri)

I would like to make a program in access that will take all these factors into consideration. EX. if i try to schedule paul on sm4 the program will not let me do it or it will give a warning or if i try to schedule paul for 5 days it will give me a warning.
It would also be great to have a counter that will show how many days each employee has left to be scheduled for, EX: if i scheduled paul for 2 days it will show that he can actually work 2 days more if i need him to.

I know this might be a lot, but i also know there are so many talented people here so i am sure it is feasible. Please let me know if you know how to do this or if you could guide me through it.

or if you know about a different program that can solve this please let me know
Thanks in advance

Hello All,

I have been trying for quite some time now to complete this report to no avail. I have searched the fourm and haven't found anything that is working for me, so...

I have a scheduling program, it runs in a two week increment to coniside with our payroll. I have a table for all the shifts (a one number or letter code represents ten or twelve shifts).
I want a two page report, the first has the emplyoee name, the dates and then the shift working:

Name Sunday1 Monday1 ...
Foo-----1-------B----.....

I have this report working just fine. The problem comes with the next page. I want the number of employees on a shift by day:

Shift Sunday1 Monday1 ....
B ------1---------5-----....

I have not been able to get the count of the shifts. I hope I have explained my problem well enough, if not please let me know and I will try to clear it up.

Many Thanks!!

I have made several posts requesting help for the coding of a scheduling program I am attempting to make. However, as I keep thinking through the process; it seems there must be a better way. I need Access to figure out how many people I have staffed at any given half hour during the day (this is for a 24/7 operation). I currently have part of it working, with one table to store the number staffed at each half hour interval and the other to edit the start and end time of each representatives shift. I have created an AddStaff and RemoveStaff function that removes 1 from the specified time slot on the Staff table upon entering the control in the Shift table and then adds 1 from the specified time slot upon exiting the control. This seems to work except for a glitch when the value is Null.

But as I'm thinking through this project, it seems my whole approach may be wrong. I am dealing with a little under 200 employees. About half of these have set schedules. My original plan was to create a new table for each new schedule. It would create a record(including Start and End Times/Monday through Sunday) for each active employee. I was then going to have the set schedules stored in a separate table and have that updated in the new table for those employees who are set. For each shift it added it would then have to use the AddStaff function to increase the number in the Staff table by 1 during that shift time. I'm just realizing that in addition to being a lot of work to create, the project described above is also going to require alot of processing by the computer.

Does anyone have any suggestions for a better approach? It would be ideal if there was some way for access to dynamically evaluate the number staffed during each interval in a query. I'm guessing there is no way for it to be that simple.

Hello,

I am trying to build a DB for an lab inspection process. I am a safety professional who has about 2000 labs that need to be inspected annually and I need to generate some type of system that will enable me to schedule, track, and record inspections. Also, I will have three seperate employees that will be performing these inspections. Is there any type of software program I can buy to learn these tasks? Could you possibly provide me with a sample of a generic program and a brief detailed explanation of how it works? I am desperate and not used to using access a lot professionally. However, once I get a feel for it I will be using it everyday. hopefully as proficiently as the general population eventually. I appreciate your time and thanks for your input.

Rob

I work for an ambulance provider and have recently been put in charge of creating an Access program to facilitate scheduling of our employees. We run multiple shifts that start at various times of the day to provide overlapping coverage. Some of these shifts run accross days (i.e. 8a Tue-8a Wed, or 6p-6a). The question that I have is; is there a simple way that I can have employees enter their available time that will still stay within their perception of days. For Instance 6p Tuesday - 6a Wednesday is perceived as the Tuesday Shift. I have tried to enter end times that are beyond 24 hours as some scheduling program use and have found that this doesn't work. Does anyone know how Outlook performs this function? or an effecient way to enter these times without having the employee enter their start in one day and then go to another to end the time? My hope is to have an ASP page were employees are presented with a calendar and can enter their available times.

Also, does anyone know of an effecient function that will auto-populate a schedule based upon employee availablility. I have written one, but it is SLOW!

Thank you in Advance

Can anyone suggest a good program that will handle scheduled emails to people in our Access database? I need to send an email automatically to each of our employees on their birthday. I need the program for much more than that but that is what I am working on at this time.

Thank you!

Ben

i am looking for some major help. i have posted this problem before but i am wanting to come back to it from a different angle. i have a diary on a form at the minute. this displays a clients name, time and expected finish time. this is all done by a list box via queries. the only snag is that i dont seem to be able to differ from employees. ie have a clumn for joe and 1 for john. i am looking for suggestions that i can explore to be able to achieve this. my must haves for this are

i want a time column(15 min increments)
employee column(dont mind if this has to be manually added for each employee as they leave the business or start)
within the employee column i would like to display the appointment. have the total time for the appointment 'coloured in'(lets have grey) in the grey colour i would like the clients name and what treatments they are in for.

i have been told that a 'listview' might manage this but i seem to hit a wall when finding out info about this. vba told me that it is all about programming and to be hones i dont really know much about programming. im not trying to do this without having to work for it but i kind of need a leg up to get me started. if anyone does have any suggestions how i can achieve this then please just reply.

thanks in advance.

As a tech savvy, I was asked to find a program for our school bus transportation. The goal is that every bus driver should get a daily printout with all changes of stops. Top portion will be kids from other buses coming for a bus change to this bus, and bottom portion for kids normally on this bus, but have a change to another bus. Like that the driver will know where to stop or not to stop.

It should work with calendar, so some changes are only for a day, others might be several. On each daily printout the relevant and active changes should be printed. Sure enough for each driver separately.

Basically it should be more or less automated. The user should be able to just select the day, and only drivers who have any change would show up on the report. Each bus driver also has a bus "color". This color can vary depending on which trip. So when choosing a driver and which trip, the color should be shown automatically.

I'm not sure how to do that. I first thought maybe there's a website for similar things. Maybe Google Calendar. But I saw there are several features lacking in Google Calendar. Maybe a standalone program? Maybe a program that integrates with an online service? Maybe employee scheduling? Maybe Microsoft Excel or Access?

I'm not sure if this is the right place to get this help, but right now I have nowhere else to turn. Any guidance will be greatly appreciated!

So when I made an access 2007 program to track time off at our company, everything worked. Only issue is, I need to account for different employee's schedules. Some employees work mon-fri. Others work mon, wed, fri, sat, etc. So my best thought to add this ability is to add to each employee record 7 yes/no fields for sunday-saturday and check them accordingly. Now all my calculations start with the formula WorkDays which is as follows...


	Code:
	Public Function WorkDays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        WorkDays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday]

Hello all-

I have what I thought was a simple programming problem... but I think I am going about it the wrong way as the solution has turned into a disaster.

Basically I have a form called "frmworkCallSub" that users can use to enter scheduling information for employees. There is an employeeID field, Date, StartTime(in Short Time), EndTime(in Short Time), and other details.

I have on the form a calculated control to find the total time for each individual record, or "work call"
TotalTimePerCall=Format([StartTime]-1-[EndTime],"Short Time")

What I am trying to do is calculate the total number of hours for each employee over a specified date period.

I set up a query taking the "TotalTimePerCall" and EmployeeID, and an empty field called "HourTotal" I then wrote a function that loops through each employeeID and calculates a running sum. (HOURS worth of work on freakin' date/time syntax mind you!) However, when I try to run the sql statement in the function to update the "HourTotal" field- it updates that field for EVERY record in the query. i end up with the total for my first employee in EVERY employee.

Obviously I'm doing something wrong. Is there a way to only update the current record?

Help??
Thanks!

I need some help creating some new tables in my existing staff database.
Each employee will have:
1. Start Date
2. End Date
3. Activity for that period
What I need is to be able to track where each person is throughout the year.
Im thinking what I need is a Usage table and a Need table.
When a user selects a certain date range, I need to be able to check and see if that person is already scheduled to do something on that day or for that date range.
Lets say a person is scheduled from 1/1/05 until 3/10/05 to do programming. When a user comes in and selects the same person to do programming from 2/5/05 until 4/5/05, I will need it to say that this person is not available for this set of dates.
Is this something that is doable? Can I actually compare dates and account for vacation, sick days, etc.
Any help, ideas, examples (possibly) would be greatly appreciated.
Thank you.

Hi-

I am an intern with Verizon and I have a pretty big project in Access 2000 that I dont have much techinical help with. I have run into a major problem though, so in surfing the great garbage heap of an Internet I found this site.

I am trying to build a program that helps schedule employees due to union rules. I have run into a major problem with some of my update queries.

The user first is presented with a form and is requested to enter Emp ID, name, etc. then they press a button (which calls the macro "Continue") to return to the main form. "Continue" first tries to run an update query which (tries to) update the values of another table to the enetered Emp ID. So the form is still technically open so I can get to the information in it, but nothing happens. It doesn't update.

What am I doing wrong?

Any help would be gratefully appreciated! Thank you all so much!

Jewellissa

Hi,
My attached database has 3 set tables for training programs at a company, and 2 for storing employee data. I have a form set up to schedule the training for people with a radio button that asks if you want to schedule by task or subject. When the button says yes I want to be able to have the form enter the training date and trainer for all tasks under the current subject. I can't figure out how to do this. Any help would be appreciated.

Link for the db: docs.google.com/open?id=0Bzb-wlmE2hcoOTB4Yjd2c0hnbVU

Hi,

I will warmly welcome the all advises and help to develop this database design in correct way and proper way.

The Process Flow Chart is attached to have a look for better understanding.


Its a manufacturing process flow chart of Pipes Manufacturing Company where I want to focus from Raw Materials Receipt to Finish Goods Transferring to Warehouse.

PROCESS DESCRIPTION

1. Raw Materials Receipt. (From P01 - P02):

The different type of Raw Materials based on Purchase Orders (almost 50 Items) received at "P01" and recorded in ERP system.

1.1 Laboratory Tests & Quality Control.
A sample bag is sent to Lab. For Testing the quality of Materials and a Lab. Technician will Test the Materials as Test Code T001 based on the Type of Raw Material, there are Specified elements to check for each category of Materials and especially if it not as per standard values, so a report is submitted to Purchase/Stores for rejection of Material.

2. Raw Materials Preparation (From P03 - P05):

The Clay Raw Materials (two types mostly) processed and milled at "P03" through different stages and converted into required particle sizes and then mixed by appropriate percentage as per given "Dry Powder Body Composition" (Which is issued by Lab.) at P04 and send to next process P05 to add water into and prepare Mixed Clay.

2.1 Prodn. Data Recording and Data Type:
- @ P03: The Control Room Operators record the kgs loaded into Hoppers (4 Units) for each type of material.
- @ P04: The Control Room Operators record the kgs of each type of material mixed according the given compositions of dry powder mix.
- @ P05: The Mixer Operators (2 Units) record the kgs of each batch (normally fixed quantity like 1,400 kgs) and the quantity of water added to this mix (variable quantity) to maintained the moisture standards of Mixed Clay.

2.2 Laboratory Tests & Quality Control.
- After P03: The Lab. Technician took random samples throughout the shift and performs Test T002 and advises the concerns.
- @ P04: The Lab. Manager issues Dry Powder Compositions and that is followed at mixing stage for related products until new instructions.
- After P03: The Lab. Technician took samples of each batch and advises the Mixer Operators to maintain the standard limits of moisture.

2.3 Notes:
- Touch Screen Data Entry at P05 will be the best practice.

3. Product Making and Drying Process ) (From P06 - P07):

The mixed Clay Body is directly sent to the Extruder Machines P03 (3 Units) where 3 different Size of Pipes as per Monthly Production Plan extruded and loaded on Dryer Cars (as a Carrier to take Pipes trough next Process).

Those loaded dryer cars automatically pushed into the dryers (5 Units) as programmed by Product size. The Dryers are programmed at fixed cycle time (70 Hours and can be changed) and also other parameters like humidity % and Temperature.

3.1 Prodn. Data Recording and Data Type:
- @ P06: The Operator of each machine (3 Units) records the production data as, Dryer Car #, Time, Good Pipes, Bad Pipes and any comments during extrusion.
- The Operators also filled in the Quality check sheet which contains 17 Elements to checked and write down after 3-4 hours Intervals.
- After P06: The Line Controller records the Dryer Car # and the time it pushed into which Dryer #.

3.2 Laboratory Tests & Quality Control.
- After P06: The Lab. Technician took daily random small Pcs of samples from Pipes from Dryer Cars and performs Test T04 and advises the concerns.
- After P07: The Lab. Technician took daily random small Pcs of samples from Pipes from Dryer Cars to measure the moisture of Pipes and performs Test T05 and advises the concerns.

3.3 Notes:

- The Dryer cars are same in structure and labelled with unique code number (001-400).
- Looking for Touch Screen Data Entry practice.

4. Inspection, Glazing and Loading Process) (From P09 - P12):

The Dryer Cars comes out from the Dryers and shifted to the Production Line ( 2 Units) where Inspectors check the quality and marked as good pipe , reject Pipes.

The Good Pipes lifted by cranes and dipped into the Glaze tank and shift to the flat forms where a Human loader lift the pipes with vacuum loader and set up on Kiln Cars (as Carrier to take the Pipes through the Firing cycle). The reject pipes scrapped in trash tank and send to scrap yard time to time.

4.1 Prodn. Data Recording and Data Type:

- @ P09/P11/P12: The Operator/Inspector of each Production Line (2 Units) records the production data as, Dryer Car #, Time, Good Pipes, Reject Pipes and reason for rejects, Loaded on Kiln Cars #, and any comments Like any Glaze added to the tanks during that specific Car no.

4.2 Laboratory Tests & Quality Control.
- @ P11: The Lab. Technician took daily random samples of Glaze from Tanks and performs Test T007 and advises the concerns.

4.3 Notes:
- The Kiln Cars are two types in structure and important keep them separate while recording and labelled with unique code number (001-100).
- Looking for Touch Screen Data Entry practice.
- When a dryer car is unloaded and it moves to again Process P06 for loading again. So would be better to know a how many cars waiting for unloading and how main ready for P06 and how many are inside Dryers.
- One Dryer Car will be having less no. of pipes comparing to the Kilns Cars. (Like Product SS200 will be having 21 Pipes on Dryer Car whereas 76 required on Kiln Car.)

- Glaze Supply: The Glaze (Liquid form) is supplied from Glaze section (another process of mixing Raw Materials and prepare Glaze) .

5. Pre-Kiln Dryers and Firing Process (From P13 - P114):

Those loaded Kiln Cars automatically pushed through the Pre-Kiln Dryers (3 Units) and then pushed through the Tunnel Kiln (Firing). The pushing cycle is based on the Firing Cycle (Like Max. 24 Cars per 24 hours and in actual Pushing is 15Cars/24Hrs, Etc).


5.1 Prodn. Data Recording and Data Type:

- @ P14:The Kiln Operator records what time which Kiln Car is pushed inside the Tunnel Kiln.

5.2 Laboratory Tests & Quality Control.
- @ P13:The Lab. Technician took daily random samples from Pipes and checks the moisture of Pipes at this stage and advises the concerns.

5.3 Notes:

- Looking for IPad Touch Screen Data Entry practice so while moving around Operator can enter the time and Kiln Car no.
- Kiln Exit Schedule: what is coming out (which product) from Kiln (Firing) in next 24 hours is very important to schedule the unloading Lines (2 Lines Only specified for products, Like Product-A Cant be unloaded at Line one due size and weights) to arrange the people and etc.

6. Unloading & Inspection - Sorting (P15):

The Pipes are unloaded from Kiln Cars and inspectors sort out based on quality standards and define in three categories, Good Pipes, Scrapped Pipes (Throw out) and Short Pipes (if full pipe is not good so they marked as GA, GZ, GE categories of short length pipes and processed to "P16" for Cutting into short lengths).


6.1 Prodn. Data Recording and Data Type:

- @ P15: The Inspector filled in the sheet either it is good, reject with reasons (list of reasons 6-7) and short length Pipes with reasons (Same of Reject Reasons).

6.2 Laboratory Tests & Quality Control.
- After P15: The Lab. Technician took daily random samples of Pipes and Performs different Test (4 tests) for different elements.


6.3 Notes:
- Looking for Touch Screen Data Entry practice.
- Very Important Area of Process which shows the results of quality of all the processes starting from P03.
- I am sure it will be possible, if we just click the Kiln Car No and would be able to know its sorting results, from which dryer cars loaded and those dryer cars was produced what time and from body mixed batch and also if there is any Lab test performed on those Pipes or before or after throughout the process.

7. Jointing of full Pipes and Short length Pipes (P17):

At this process, the pipes which marked as good by the Inspectors directly shift to the Jointing carousel for Jointing the Socket and spigot. The short length pipes taken from Cutting Section "P16" after some days or as required to joint.

7.1 Prodn. Data Recording and Data Type:

- @ P17: The Operators record the information of production performed on that line.
- Looking for Touch Screen Data Entry practice.


8. Packaging & Transfer (P18 P19):

At this process, the pipes which ARE jointed from the previous process packed as per standards (for Product-A, 20 Pipes/Pallet) and shift to warehouse after Quality control check.



8.1 Prodn. Data Recording and Data Type:

- @ P17: The Packers record the information of production performed on that line.
- Looking for Touch Screen Data Entry practice.

8.2 Laboratory Tests & Quality Control.
- After P17/P18: The Lab. Technicians took daily random samples from Pipes and performs different test as per standards.

9. General Attributes in Data Recording.

Here I will mention some general attributes which are required on each process to be part of data recoding with special data requirements of that process.

- Date/Time
- Production Lines/Machines where data is record.
- Shifts (Day Shift & Night Shift)
- Operators (of that Process or Machine)

10. Main Possible Entities & Attributes.


- Workstation (can be called Work station, ID, Description)
- Process (Can be called Operations, ID, Description, belongs to Workstation)
- Employees (ID, Name, Designation, Nationality, Hiring Date, Process)
- Machines (ID, Description, Process, etc)
- Item (Can be Raw Materials, Finish Goods) (ID, Description, Type, Green Weight, Fired Weight, Etc).
- Dryer Cars (ID, Description)
- Kiln Cars (Two Types TK/SK Cars - ID, Description)
- Shifts (ID, Description)
- UOM
- Lab. Test (ID, Description, Performed at Process, Ref. Standard No, etc).
- Test Parameters (ID, Description, belongs to test, UOM, Standard Limits Min/Max, etc)
*One Test can have more than 2 parameters to be performed
- Reject Reasons (ID, Description, belongs to process, etc)
- Dow Time Reasons (ID, Description, Belongs to Process)
*If there is any down time at any machine, so will be recorded with reason.


May still something is missing but I think it is enough to have the basic idea of the Process.


Thanks you in advance.

Regards

Zee Attached Files Process Description.pdf (76.3 KB, 21 views) Process Flow Chart.pdf (62.3 KB, 24 views) Reply With Quote 10-29-2012,07:35 AM #2 orange VIP Windows XP Access 2003 Join Date Sep 2009 Location Ottawa, Ontario, Canada Posts 4,207 Seems you have a "corporate/company wide" database application to design that is tailored to your manufacturing processes. The best I can offer is a link to an existing free data model for a Dutch manufacturer. It may not be specific to your manufacturing, but it should give you some guidance on WHAT is involved and the Relationships between Entities.
see
http://www.databaseanswers.org/data_...attributes.htm

Good luck with your project.

I am fairly new to Access and have no formal training on this program, just a lot of trial and error. I have a database with 4 tables. They are tblEmployees, tblCourses, tblDates, and tblTraining. The key for each was an autonumber that is EMPLOYEEID, COURSEID, DATEID, and TRAININGID. tblEmployee lists pertinent information regarding an employee (name, serial #, shift, etc). tblCourse lists all courses that are available for an employee (course name, #hours, required attendees, type of training, etc). tblDates lists all available class dates and times for the courses in tblCourses and has a lookup field for COURSEID and COURSETITLE from tblCourses. tblTraining lists all the training scheduled for and completed by an employee and has a lookup/relationship with tblEmployees for EMPLOYEEID and EMPLOYEENAME. It also has a lookup/relationship field with tblCourses for COURSEID and COURSETITLE. Finally, it has a lookup/relationship with tblDates with lookup field/relationship with DATEID and CLASSDATE. I have successfully created a form where the training can be added to an employee (frmTrainingUpdated) and it has a subform (frmTraining) with all classes for that employee in a multilist at the bottom (from a query of tblTraining). The subform also has a field for whether the training was completed and then the hours are credited to the employee (txtCredit). The problem comes when I try to add all of the credited hours for a single employee in a separate field. I am trying to create a field somewhere on the form that will total all of the hours for all of the completed classes for the one employee on the form. I have tried to list the data for the textbox as =DSUM([txtCredit], tblTraining, WHERE (EMPLOYEE=Me.Employee)) and only get an error message displayed in the textbox. I tried to do a totals SUM on the query and it only gives me the individual hours for each class on the same line for that record. Any suggestions?


Not finding an answer? Try a Google search.