Moving staff roster to next month (1 Viewer)

[pekajo]

Hi,
Can someone help.
I have a roster where there the rows with the dates for a month. Ever employee has a field, So I can match the date and employee the a site.
My issue is that the employees have set days of the week the work. My problem is when I start a new month the 1st is not always on a Monday hence I have to move the employees up or down to slot them into the correct days the day of the week.
Has anyone have this issue and do you solve it.
Regards
Peter

 

[DHookom]

I believe you need to study "normalization". There are many posts in this site as well as resources on the web. Your "problem" will never end if you continue with your current structure with "Ever employee has a field". Data is supposed to be stored in fields rather than used as field names.

Come back with some specific requirements and I'm sure the good people here will have lots of great solutions that are bullet-proof in the future.

 

[CJ_London]

You probably need tables along these lines

Tblemployees
EmpPK
empNamr
….
..,,.

Tblrotas
RotaPK
empFK
dayID - 1=sunday, 2=monday etc
One record for each day an employee works

For any given month, use the weekday function to find the day of each week for each day and link to the dayID for a list of those working that day

Add additional data to the rotation table if employees only work every other week, only the first week of the month

Further table required to cover holidays, illness etc so they can be excluded from the months report

 

[GPGeorge]

Hi,
Can someone help.
I have a roster where there the rows with the dates for a month. Ever employee has a field, So I can match the date and employee the a site.
My issue is that the employees have set days of the week the work. My problem is when I start a new month the 1st is not always on a Monday hence I have to move the employees up or down to slot them into the correct days the day of the week.
Has anyone have this issue and do you solve it.
Regards
Peter

This approach to table design is often referred to as "Spreadsheet tables", and that is not a Good Thing™. It usually turns out that the original source of the data was an Excel worksheet imported directly into Access.

Here are some links to resources that will help you learn the appropriate approach, as Duane suggested.

Problems With Repeated Columns – Roger's Access Blog

www.rogersaccessblog.com

Note: The YouTube videos are delivered in a less formal approach than the actual rules of normalization would be because they are intended for people not yet deeply immersed in database development. However, they are understandable and accurate so far as they go.

 

[Ken Sheridan]

Firstly create a two column table as follows:

EmployeeDays
....EmployeeID (FK)
....WorkDay

This table is 'all key', i.e. its primary key is a composite of the two columns. The WorkDay column should be of integer number data type, and in each row contain a value for an employee's working day. So if they work Monday, Tuesday and Wednesday each week these would be represented in the table by three rows with values 2, 3 and 4 in the WorkDay column.

Next create a WorkDaysCalendar table with dates of all working days over a period from the present into a number of years into the future. The Calendar.accdb file in the attached Zip archive enables you to easily create such a table, amongst others.

The following query will then return the rota for each employee for the month entered as the parameters at runtime. If you change this query into an 'append' query you can use it to insert rows into a Rotas table, in which you can then enter data into any other columns to record each employee's attendance data for the day in question:

SELECT
    EmployeeDays.EmployeeID,
    WorkdaysCalendar.calDate
FROM
    EmployeeDays
    INNER JOIN WorkdaysCalendar ON EmployeeDays.WorkDay = WEEKDAY(WorkdaysCalendar.calDate)
WHERE
    YEAR(calDate) = [Enter Year:]
    AND MONTH(calDate) = [Enter Month:]
ORDER BY
    EmployeeID,
    calDate;

The month parameter should be entered as an integer number between 1 and 12 inclusive.