Time Clock Query

[bennyhana88]

Hello,
I use Access 2010 in my office and 2013 at home and often develop databases between both versions of Access. Please keep in mind I am a new user and giving me instructions without telling me how to get to certain parts of Access will not be helpful. I see most answers in this forum assume a user is an expert.
_______________________________________________

The problem I am having is almost exclusively in Access 2010. I developed a time clock with two subforms in a parent form. One subform tracks employee punches in while the other tracks employee punches out. Each record posts to its own table (Table1 and Table2 respectively).

I'm trying to query each table so I can compare a punch in with a punch out to determine how much time a person worked in a given shift, but I'm running into a problem where every punch in for a particular person matches with every punch out. This makes my query needlessly long and terribly difficult to sort through to identify a person's worked hours. Is there a way I can pair one punch in to a person's next punch out?

Provided is a screenshot of my query and of my relationships. IDOne and IDTwo are my primary keys in each table. Anything denoted by a "One" belongs with Table1 and anything denoted by a "Two" belongs with Table2.

Any feedback is appreciated. I've worked to solve this problem for a week and can't come up with any ideas.

Thanks,


Bennyhana88

[pbaldy]

I've created a time clock app, and I put the clock out field in the same record with the clock in. My theory was that every clock in would have to have a clock out, so it made sense for them to be in the same record. I handle users forgetting to clock in or back out with exception reports and let management edit the time records.

I don't understand your relationships at all, but maybe that's just the image. I won't say you can't make your design work, but I think it will be more complicated. Either way, I'd store the date and time together, which will make date math easier when you cross midnight.

[ranman256]

you table design looks wrong.
Clock in /out should be the same table

[personID]
[Date time]
[I/O] = in or out

if I is the last entry ,then O is next
if O is the last entry then I is the next

[bennyhana88]

RE Ranman: That only works if the same employee is clocking out right after they clocked in. If I have 20 punches in and 20 punches out in no particular order, then I can't make it as easy as in or out.

RE Pbaldy: Sorry about the relationships, they can be difficult to understand. Keep in mind I'm a new user. I've created things in multiple records because I can't give my clients access to a background database to edit their time out because then they'll have access to everyone else's time and personal info. I don't want too much personal information revealed. I'd rather them have an ID or PIN that matches their name in my form. Provided is a picture of my form with the two subforms if that helps for reference. I have tried doing things in just one record and can't make it work. Please help me reconcile the duplicate values in my query if you think you may know how. It's a sure fire way of spitting out time worked. Sorry, I can't make the form picture any larger. My computer freezes every time I try to modify it.

[pbaldy]

I would never give clients access to the database or tables. I give my management a form to edit through. You could use one that filters by client so they only see their own data. What if a person clicks on punch in or out twice accidentally? I think you still need to give management a way to fix goofs, which will invariably happen.

If you want to stay with this, I envision a query with a subquery or non-equi join. If you can post your sample db, I can play with it (but I still don't like it).

[bennyhana88]

I'm open to ideas. I just want an easy way to use a calculated field to determine time worked. I really just want my clients to be able to use something simple to punch in and out. Some of them have developmental disabilities and need something very straightforward.

The website won't let me post the DB because it's 3.5 megabytes, is there another way I can get it to you?

[pbaldy]

If you compact/repair and then zip, it should work. I've seen ranman's design before, but to me it still means you're jumping through hoops to match clock in's and out's, but maybe I don't understand well enough. With both in one record, the calculation is simple, and could even be a calculated field.

[bennyhana88]

I'd love to see how your single record form worksTime Clock Take 2 - Copy.zip.

I tried to attach my DB. Hopefully you can open it up. Hold shift down upon opening.

[pbaldy]

I use an unbound form with a bound subform that starts out hidden. The employee enters their ID number, and has to enter a password. At that point the subform populates with any activity they may already have that day, and the clock in/out buttons are activated.

The Cliff Notes version of the button code is that the clock in button will insert a record into the table with the clock in date/time and the employee ID. The clock out button will update the record where the clock out field is Null and the employee ID matches the person on the main form, adding the clock out date/time. There's a lot more to it, but that's the short version.

I have a different program (drivers going out and back in) where I give them a single button, but have the same table structure (in and out in one record). If they don't have an open record, I create one (clock in), otherwise I update the open record (clock out).

[bennyhana88]

If you have trouble reconciling my method would you be willing to work with me do develop something similar to your method?