Question: Table Design - How to track time from multiple entry?

[warhead92100]

Hi All,

I'm new here, apologies if this is not the right place for this question.

I am going to build a tracker for staff scheduling. I have not started building but currently working on the design, and I am stuck on how to check multiple entry with different time.

My idea is to have a table that would hold the employee time, e.g. start - 9 am end - 5 pm, then I can count it into another table which will tell me that this employee is at the office at 9am, 10am, 11am, 12pm, 1pm, 2pm, 3pm, 4pm and 5pm. but if he had another work shift or overtime, let say 6 pm to 8pm as another entry on the table, how would I tell that the same employee is working at 9am, 10am, 11am, 12pm, 1pm, 2pm, 3pm, 4pm, 5pm, 6pm, 7pm and 8pm? I am using unique employee tag number as the basis of this lookup.



Any idea is greatly appreciated.

Thanks!

[ranman256]

I personally use a
[EMPid], [ClockIN], [ClockOUT]
45152, 7/1/15 8am, 7/1/15 5pm

This way I can easily find out , who didnt clock in , who didnt clock out.
query calculates elapsed time.

[warhead92100]

Thanks ranman, however I need to produce a report for employees per hour, e.g. 200 employee are in the vicinity at 1pm, 180 employee are in the vicinity at 3pm.

I am creating a table for each day with hours as fields, tbl_Monday would have employee tag number, 12mn, 1am, 2am .... 11pm as its field, this would be replicated to tbl_Tuesday to tbl_Sunday. My problem is how to cross check if an employee has multiple work shifts. Is there a way to query multiple entries from another table?

Appreciate the ideas.

Thanks!

[ranman256]

it still works for that.

[kagoodwin13]

Thanks ranman, however I need to produce a report for employees per hour, e.g. 200 employee are in the vicinity at 1pm, 180 employee are in the vicinity at 3pm.

I am creating a table for each day with hours as fields, tbl_Monday would have employee tag number, 12mn, 1am, 2am .... 11pm as its field, this would be replicated to tbl_Tuesday to tbl_Sunday. My problem is how to cross check if an employee has multiple work shifts. Is there a way to query multiple entries from another table?

Appreciate the ideas.

Thanks!

Use a query to tell you which employees have clocked in, but have not clocked out. That will tell you who is still working.

I would think about restructuring your tables though. Having separate tables for separate days will not work in the long run. Your information will be scattered, which will make it difficult to run queries and reports. Additionally, you'd probably have to wipe your data each week to keep track of which date Monday and Tuesday you are referring to, which would be a completely unnecessary activity.

You should have only one table should keep track of employee timesheets. It needs to at least have the following fields: EmpID, TimeStamp, Action (clock in/clock out)

You can use queries later to convert TimeStamp to a date, day of the week, and time of day, all from the one field.
You can also use queries to keep track of each EmpID over a long time, such as "How many employees are here right now? How many hours does each employee work each week? How many employees worked overtime last week? How many employees worked overtime last month?"
I think you'll be able to meet your business requirements a lot better in that way.

[warhead92100]

it still works for that.

Use a query to tell you which employees have clocked in, but have not clocked out. That will tell you who is still working.

I would think about restructuring your tables though. Having separate tables for separate days will not work in the long run. Your information will be scattered, which will make it difficult to run queries and reports. Additionally, you'd probably have to wipe your data each week to keep track of which date Monday and Tuesday you are referring to, which would be a completely unnecessary activity.

You should have only one table should keep track of employee timesheets. It needs to at least have the following fields: EmpID, TimeStamp, Action (clock in/clock out)

You can use queries later to convert TimeStamp to a date, day of the week, and time of day, all from the one field.
You can also use queries to keep track of each EmpID over a long time, such as "How many employees are here right now? How many hours does each employee work each week? How many employees worked overtime last week? How many employees worked overtime last month?"
I think you'll be able to meet your business requirements a lot better in that way.

Thanks guys, didn't thought of that until I started working on the table - there were so many. However, there's no "bundy clock". It would be from a sharepoint site that each supervisor will be submitting the schedule of their teams which I use as the main source table. I now have 12 query that create 12 more tables - checking on hours, days, teams, etc. etc.
It is a bit long process to get each information I want, any idea to make this more efficient is greatly appreciated.