Solved Subtract hours (1 Viewer)

[Pat Hartman]

The alternate version of the table I posted shows "reason". Use a combo or make assumptions.

If you don't care that they take more than a 1/2 hour for breakfast and an hour for lunch and if they take too long, you're going to assume the 1/2 or 1 hour, what is the point of bothering? Just take 1.5 hours out of the total work day.

I guess you also don't care if people have to take time off for appointments.

 

[zelarra821]

Several things:

1. You are taking the start and end date, and I have it set to calculate the end date by adding one minute to the start date. Reason? The goal is to record the hours, and when you punch in, you don't know when you're going to go out, or have breakfast, noon, or lunch. I should create a function that updates the date of the previous record with the one I enter: I mean, when I add the breakfast start time, I copy it to the record where you added the entry time, and so on with all types.
2. I know it may be hard to understand, but that's how the clocking machine works. The half hour of breakfast is not subtracted, what happens is that if you stay for more than half an hour, it only adds half an hour. As for food, it is subtracted, because that half hour is not included in the day. You can analyze it in the spreadsheet that I have given you.
3. How do I fix the problem when a user deletes a record?

 

[zelarra821]

@Pat Hartman, I'm not the one making the rules. I work for the Government, and this is the one that sets the rules that govern the working hours of civil servants. I'm just trying to copy the method to use to calculate the hours, hence I have to have those details.

 

[MajP]

I guess I am confused now. I thought you decided to go with one event with a start and end time, not two events for clock in and clock out. Which one are you going with.

 

[Pat Hartman]

I'm not the one making the rules. I work for the Government, and this is the one that sets the rules that govern the working hours of civil servants. I'm just trying to copy the method to use to calculate the hours, hence I have to have those details.

How are you supposed to know that a punch out is for breakfast rather than a dentist appointment? Does the clock have some mechanism that lets the employee indicate what the punch is for? I've never seen one that does. They're just dumb. Pull the card out of one side of the rack, punch it, put it in the other side of the rack.

Doesn't make sense to be processing these punches live. Do you get a file once a day? More frequently? As long as the process is batch, it is possible that you have orphan punches. You punch in during one time period and punch out during the other. Do you run multiple shifts or just one? What about weekends? Are the rules the same?

We don't know exactly how the punching works, whether it is batch or live, how you deal with non-breakfast or non-lunch or even how you tell whether a mid-shift punch is for a meal or a different type of absence.

You can't write code without defining the whole process in detail.

 

[zelarra821]

If your idea of three events seems ideal to me, and I think it's the right thing to do. However, you have taken a date that I do not know. I'll give you an example: I arrive at half past seven, and I register it as an entrance. At this point, I don't know when I'm going to stop. At eleven, I decide to take a break: I write it down. Now I have two records for day X. At this time, function 1 can be applied, called hours worked, just as you said. To do this, you have to take the difference between the times of those two records.

We went to breakfast, of which I already have the first hour: the start. When I come back, I write down the time. As before, I have to use function 2 in this case, which will take the start time of one record and the end time of the other record.

We continue. Now he would file and have the record with the start time of the second work period. When I clock in to go home, another record is created with that departure time. We apply function 1 that we have used above, taking the time of both registers.

All subtractions would be saved in a table along with the date, so you can easily add them later in a totals query.

I think that with that example it is clear that there would be three functions. Later he would need to calculate lunch, but it would be similar to what we have done for breakfast.

This has to be so because I don't enter an end date.

 

[MajP]

The whole idea is to get away from having to do two records for a single event. Instead every event has a start and end.
If it was me am logging every minute of the day. The next event starts at the end of the proceeding event

Work Block 0:730 - 11:00
Break 11:01 - 11:15
Work Block 11:16 - 12:00
Lunch 12:01 - 12:45
Work Block 12:45 - 16:00

For reporting Purposes you can calculate Start of day as 0:730 and end of Day as 16:00.

 

[zelarra821]

Yes, you are right, but I don't know how to get the start date of the day, which is in the first record, and the last one, which is in the last record.

 

[zelarra821]

Can you tell me how to get the start date of the day, which is in the first record, and the last one, which is in the last record?

 

[Pat Hartman]

Filter the query by date and sort it by EmployeeID, PunchDT

When I come back, I write down the time.

I thought you were using a time clock. If you are not using an actual time clock, then there is no reason to not take the advice you have been offered regarding having an in and out on the same record.

I am going away now.

 

[zelarra821]

What I do is write down the beginning of each of the events. I can't know, when I clock in, if I'm going to be working 2 hours or 1 hour, or if the break is longer and you only have to count half an hour... For this reason, I only want to manually note the start. Since @MajP recommended saving the ending, what I do is add a minute to it, but it's worthless to me. That's why I ask how I can search for the different types and then subtract them and finally add the results. That was my first question, because I tried to create an array to store the values and then go through them with a loop, but I couldn't, and I didn't know if my proposal would be optimal. I would like you to write me an example of what I have to do, to adapt it to my case. Thank you very much and sorry for the insistence.

 

[Pat Hartman]

I would like you to write me an example of what I have to do, to adapt it to my case.

I have tried to explain to you how to do this using a table. You are not receptive to changing your schema or even thinking about the reality of real world time keeping applications where people legitimately have absences that have nothing to do with breakfast and lunch. I don't have a specific example I can post and I am not willing to spend the time to build something custom for you because your requirements are not detailed, nor are they realistic so I would simply be wasting my time. I can't even tell if you are using an actual time clock because that would be the ONLY reason for EVER having in and out punches in different records.

You do NOT need to load ANYTHING into an array. A table or a query IS an array. You sort the table using a query and open the query and process it sequentially, row by row. You might also be able to simplify your work and use a union query to pair up punches.

I'm sorry but I can't help you. Good luck to you. Perhaps someone has a sample hanging around that you might understand and be able to adapt.