By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

Create a field for every hour between Time In and Time Out

P: n/a
I have a database that I am using as a Time clock where employees can
clock in and out. Is there a way to make it so that when they clock
out a form would open displaying their work day hour by hour with combo
boxes next to each hour that they could select what task they did for
the coorisponding hour. For example: lets say the clock in field says
"7/5/2006 11:00:00 AM" and the clock out field is "7/5/2006 8:00:00
PM." Once they clock out a form opens that would look something like:
Employee Name
7/5/2006

11:00AM Combo box with list of possible tasks
12:00PM Combo box with list of possible tasks
1:00PM Combo box with list of possible tasks
2:00PM Combo box with list of possible tasks
.........................etc.
.........................etc.
8:00PM Combo box with list of possible tasks

I can build the form ok but I am unsure how to have this form populate
with combo boxes for only the hours worked or for multiple hours
period. Is this even possible?
Any insights or comments would be greatly appreciated.
Thanks

Jul 7 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I would envision two more tables besides the project and employee
table.

1) a table of std times with one for every timeperiod that you want to
have records for.
(A record for each hour from midnight through 11:00PM)

tblStdTimes
ID PK
StdHour date/time

2) Child table for an employee's time and project
tblWorkTimes
ID autonumber
EmployID PK FK employee owner for these tasks
WrkDate PK shortdate
WrkStdHour PK
WrkProjectID
other info as required

When the employee clocks out run an append query that creates
tblWrkTimes records for all records in the tblStdTimes between the
employee's start time and his clock out time. Then requery the subform
showing the newly created records. By having the emplyID and workdate
and stdHour as PK then you will not get duplicates but an employee can
change his clock out time from 3:00 to 4:00 and have one extra record
created. You will have to decide what to do if he needs to change it
back to 3:00 from 4:00 because you now have an extra record.

Use a parent child type subform to show his time records and he/she
fills out the rest of the info.

You may wish some checks and balances that all records are filled out,
no deletes? no adds?
OR

have the tblWorktimes to simply be
tblWorkTimes
ID autonumber
EmployID PK FK employee owner for these tasks
WrkDate PK shortdate
WrkMinutes number number of minutes (integer) worked on project
or
hours and decimal part of hour
(allow 2 decimal places)
WrkProjectID
other info as required

And allow adds and deletes, no need for stdhrs table, but add a test to
make sure there are sufficient minutes to go from start time thru end
time and no more than that. Decide what to do about lunch time. This
allows for fewer records and multiple hours of work. Whole day would
need only 2 records one for lunch and one for project.

Justs some thoughts to get the brain juices working.

Ron

Jul 7 '06 #2

P: n/a
Thanks for the idea. I think what you are suggesting would work but I
think I'm looking a little different. The employees are actually
teachers for dance classes and they teach a different class every hour.
They also have other tasks to do and their really isn't a set schedule
for each teacher. I know that sounds wierd but that is how it works.
Right now I have a list of classes and duties, a list of employees, and
a list used to track clock in and clock out time stamps. Now I think
what I need to do is create a code or a query of some sort (running
behind a form) that finds how many hours they have worked, and creates
a field for each hour worked. It would be ideal if these fields could
be combo boxes linked to the table of classes and duties so that they
could just find the duty they performed that hour instead of typing it
in manualy.

Jul 7 '06 #3

P: n/a
I'm reasonably certain you are proposing an "unnormalized" design which
will, with some certainty, cost you a significant amount of extra work, and
personal grief in the future, when you try to manipulate the table you
describe.

Almost certainly, the date-time-class information should be stored in a
table related one-to-many from the instructor table. Sooner or later, you
are going to want to query this information, and the structure you describe
will make that difficult.

Larry Linson
Microsoft Access MVP

"Shawn Yates" <sy****@cc.usu.eduwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
Thanks for the idea. I think what you are suggesting would work but I
think I'm looking a little different. The employees are actually
teachers for dance classes and they teach a different class every hour.
They also have other tasks to do and their really isn't a set schedule
for each teacher. I know that sounds wierd but that is how it works.
Right now I have a list of classes and duties, a list of employees, and
a list used to track clock in and clock out time stamps. Now I think
what I need to do is create a code or a query of some sort (running
behind a form) that finds how many hours they have worked, and creates
a field for each hour worked. It would be ideal if these fields could
be combo boxes linked to the table of classes and duties so that they
could just find the duty they performed that hour instead of typing it
in manualy.

Jul 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.