Employee tracking

[Homegrownandy]

This thread is mainly me thinking out loud and hopefully people interrupting to guide me in a better direction. I'm trying to expand a system we currently have to include more detail. When tracking employees on a certain project before, I have had in a table: weekending, hours, miles, expenses.

Now I want to make it so this becomes the way we gather ALL timesheet information. I've just created this and realise its not suitable:



This is the information that I want to track. However. People may work on multiple projects on one day. So saying 8 hours Monday wouldn't work. (I think this whole table setup is wrong). I see a way I can achieve what I want through a junction table but I'm anticipating problems with data entry. I want to make this as simple as possible.

There is a lot I want to do with this but its probably for the best I keep it simple and iron out problems before expanding.

So what we NEED:

to track hours/miles/expenses for every day. This should also describe what project they are working on. (I have the projects table up and running.)

This is a weekly entry system so we can assume once data is entered for that week it's correct. I will only be tracking that we have information for a person for a week. (obviously I can drill further into that information to view daily)

I also want to be able to see which days someone is sick or on holiday. I cant get my head around how to achieve this so any tips appreciated.

[ArviLaanemets]

A possible setup.

1. A single-field table tblWeeks: WID (it may be weekend date as in your table above, or week number in format "yyyy.ww"). You may set it up to be automatically updated when you start your application.;
2. A table tblEmployees: EmplID, EmpoyeeForename, EmployeeLastName, ...;
3. A table tblEmployeeCalendary: ECID, EmplID, WID, CalendaryHrs. It will be reasonable whoever will work with application can enter calendary hours in advance - this means you must have weeks registered for some period in advance too. This table doesn't depend on how much employee worked really, it depends only on general working hours in week, and on employees contract;
4. A table tblTimeTypes: TTID, TimeType, IsWorktime, DeducesWorktime,... . Here you define all different time entry types (working hours, annual leave hours, sick hours etc.) and how the different time types affect your calculations;
5. A table tblProjects: PID, Project, ...
6. A table tblWorkingTimes: WTID, PID, WID, EmplID, TTID, Hrs, ... . NB! Don't mess different info into one table - so have separate tables for registering working time, mileage, and expenses!;
7. A table tblMileages: MID, PID, WID, EmplID, Miles;
8. A table tblExpenses: ExpID, PID, WID, EmplID, Expenses.

[Homegrownandy]

Ill set this up in a test environment and see what problems I encounter. Thanks for the suggestion#

[ArviLaanemets]

Some thoughts how I would design a front-end.

A unbound form fMain with multi-page Tab control tbMain with pages pgEmployees, pgProjects, pgWeekWorktime, ...

On pgEmployees is a subform sfEmployees with form fEmployees (it can bee either single or contionous form) as source. When single form, then it contains a contionous subform sfEmplCalendary with form fEmplCalendary as source. When continuous, then you must have a hidden unbound control where fEmployees OnCurrent event stores current EmplID value on tab, and another continuous subform sfEmplCalendary beneath or below sfEmployee. You have to link sfEmplCalendary to unbound control manually.

On pgProjects is a subform sfProjects with form fProjects a source.

On pgWeekWorktime you have an unbound combos to select week, and Employee, and a subform sfWeekWorktime with contionous form linked to both unbound controls, where you can select an employee and time type, and enter according hours. Or you can add 3rd unbound control for employee too, and the have 3 subforms at once to enter working times, mileages and expenses on same sheet.

...

[Homegrownandy]

Okay, This information will be received via email. (currently a word document). I would like to create process where by emails coming in are uploaded automatically, or with little human input. But this is a task in itself, ill see about creating a database that can deal with the info first.

[Homegrownandy]

I realise I may be adding confusion with that last post. But the format of data we receive is to be determined. Ill create a process that suits the database.

[Homegrownandy]

Okay, I'm working on it but I have a question. I want to see information for every mon-fri. so rather than working on week ending I could list every date mon-fri and validate something exists for every person for every day. even if its a holiday or sick. But I wont use this table in the relationships because people May work weekends.

So I will store the date of every work activity and use the dates table just for validation against the input.


How does that sound?

[ArviLaanemets]

Write a function which returns WID for given date, and a function which returns a specified weekday for given WID, and you can do whatever you want.

[Homegrownandy]

Smart I like your idea. Really appreciate your help. Ill come back next week after ive spent some time on this.

[Homegrownandy]

Okay, Ill create a system so that every day in the week (and additional weekends) is checked through a combination of human verification and possibly access/excel/vba. but lets assume we are at a stage where that one timesheet for that one week for the one person is correct.

should I create a junction table between the sundays/weekend dates and staff. Then during the upload process of that timesheet, it can check if that relationship exists already and after it can see which timesheets are outstanding.

How does that sound?

[orange]

Andy,

How do people/employees record their time etc?
If by date on a day to day basis, then proceed.
But if they report by week, how would you interpolate the data?

A sample timesheet might be helpful to readers.