Solved Subtract hours

[zelarra821]

Good afternoon.

I have a table with start and end times for work, and, in between, stops for breakfast and lunch.

I have created a dropdown with the type of time record: entry, breakfast start, breakfast end, lunch start, lunch end, and output.

Now what I have to do is subtract:

breakfast start - Entry

-------------------------------------------------- -------------------

If((breakfast end - breakfast start)>0.0208333333333333;0.0208333333333333;breakfast end - breakfast start)

0.0208333333333333 equals half an hour

-------------------------------------------------- -------------------

Start lunch - breakfast end

either

Output - breakfast end

depending on whether it exists start lunch

-------------------------------------------------- -------------------

If((End lunch-Start lunch)>0.02083333333333333;0;(End lunch-Start lunch)-0.0208333333333333)

-------------------------------------------------- -------------------

Departure - End of meal

I've created a function, and what I was trying to achieve is to create an array with the hours and the type, and then loop through it, but I'm not able to. It is also true if what I have thought is the most optimal, that is why I am asking if someone can help me and/or suggest something more functional and faster.

I leave you the database.

Thank you so much.

 

[MajP]

This is very unclear. No one, not even me, is going to dig into this database trying to decipher this vague statement. You need to be much more specific.. Use a real example and step through it explaining what you want to do. Explain where and when the code would execute.
1. Where is this code used?
2. Where is the function you created?
3. What does this array hold for values? What hours? What type?
4. Loop an array for what purpose?

 

[zelarra821]

Hello, sorry. I explain.

If you enter FCalendario, and double click on March 8, you can see an example of the hours. It is in Spanish, but here is its meaning in English:

entry (entrada)
breakfast start (inicio del desayuno)
breakfast end (fin del desayuno)
lunch start (inicio de la comida)
lunch end (fin de la comida)
output (salida)

Here's an image of what you should see:

My idea is to use the code when saving the record (Guardar registro button, which you can see in the same screenshot above). Nuevo registro creates a new record. Cerrar is to close the form.

The function that I have created is in mdlCodigos and it is called Horas. In it, I create a string with the SQL of the query that I am going to use. Then I run the query. Through a conditional, I check that there are records, and I go to a for next. I've tried setting To rst.RecordCount, but it tells me there's only one record, so I've set it to 6, but this is definitely wrong. I then move on to saving the record type to a variant variable (to store the values of the array). Finally, I move to the next record and pass it a next. That is where I have stayed, and it is what I was saying before, that I do not know if this is the most optimal way. My original idea was, once I have created the array of 2x... (times and types for all the records of a specific date), with an if or a select case detect the type and proceed to the subtraction having into account the position. It should be remembered that there may or may not be breakfast or lunch, and that has to be reflected in this function that I am creating. Once I have the subtraction for each of the cases, I add it and set the value of the function. The position is calculated by itself when saving the record in the FRecords form.

To take the values, I was thinking of creating a field in the form FRegistros and putting in the source =Horas(TxtStartDate), which is a field of the form that takes the date of the selected day. Next, my idea is to save the sum of the subtractions in a table created specifically, so that I don't have to calculate it every time I need it.

The last thing I would need to finish off the database would be to be able to delete a record, but I don't know how to do it so that I don't mess up the records for the selected day.

If you need any further clarification, let me know. Sorry for the first attempt.

Thanks.

 

[zelarra821]

The subtractions that are made between each of the types, have to follow the rules of the first post, because it is how it later appears in my work time record.

 

[MajP]

I am still not sure what you are doing, but maybe the following.

If Every day has the events
Enter
Start Breakfeast
End Breakfeast
Start Lunch
End Lunch
Leave


I think I would create a seperate table
TblLogTimes
-BeginningEvent ID
-EndingEventID
-Description (Type)
-Beginning Time (date time)
-Ending Time (date time)
-CalcDuration_InMins

I could then loop the entire database pretty quickly to update the temp table.
For the first day (assume event IDs are 1,2,3,4,5,6

It applies your 30 minute rule

BeginningEventID	EndingEventID	Description	Beginning Time	Ending Time	CalcDurationInMins
2	3	Breakfast Start End	3/26/2023 11:00	3/26/2023 11:25	30

You can update this table or do this from scratch each time. Even with thousands of records it should be pretty fast.

The code will be pretty easy if there are no missing events.
They can skip breakfeast or lunch, but they cannot forget to Stop Eating or Leave for the day.

 

[MajP]

If that makes sense then you can build me the log table and add several days of data. I assume some meals are not taken so provide some data for that.

 

[Pat Hartman]

You are making this way harder than it needs to be. You don't really care why people punch out. All you care about is that they did. So, you have a series of in/out punches. Each in a separate record.

autonumberID, EmployeeID, PunchIn, PunchOut

You need logic to check when someone logs in, is there an open log out? Ask the user to fix the gap or fix it automatically. Is the person trying to log out but there is no log in? Require a log in first.

Then, each transaction is calculated separately. You don't need a function. Calculate the difference in minutes in each query. Then you can sum the minutes by day/week/month/whatever. For that, you probably want a function to calculate the result in hours since for anything other than a day, the result could exceed 24 hours and so you cannot use a datetime data type field to validly display the difference since date time is a POINT IN TIME. It is NOT elapsed time.

If you really want a reason, make it optional:

autonumberID, EmployeeID, PunchIn, PunchOut, Reason

 

[MajP]

You are making this way harder than it needs to be. You don't really care why people punch out. All you care about is that they did. So, you have a series of in/out punches. Each in a separate record.

This has been discussed in detail here, but the OP specifically wants two separate events (good or bad). This would be much simpler if events (appointments) simply had a start and end instead of creating two separate appointments, but the decision was made to go this route. This takes the Peter Hibbs calendar and customizes the code to support this logic.

Solved - Create a calendar to record work inputs and outputs

Good night. I have searched this forum for examples of calendars, and I have found this attached majP that more or less adapts to what I am looking for. What it lacks is that it can add the hours that I record each day when I enter and leave work, in such a way that it later allows me to...

www.access-programmers.co.uk

 

[Pat Hartman]

People can make whatever stupid design decisions they choose. We are always there to help

The subtractions that are made between each of the types, have to follow the rules of the first post, because it is how it later appears in my work time record.

Doesn't sound like a rational requirement. It sounds just like more misunderstanding of how relational database applications work given that storing the data in a way that minimizes data entry errors and reconciliation errors which therefore reduces complexity, does not preclude reporting them as a running list.

 

[zelarra821]

You need to know what kind of event it is because for breakfast you have half an hour, so if you go over that half hour, it only counts half an hour. The same with the lunch break: they give you half an hour. I clocked in at 2:30 p.m. and clocked in again at 2:31 p.m. and I had 29 minutes left. All this must be taken into account, so it is necessary to know what type of event it is.

 

[MajP]

You need to know what kind of event it is because for breakfast you have half an hour, so if you go over that half hour, it only counts half an hour. The same with the lunch break: they give you half an hour. I clocked in at 2:30 p.m. and clocked in again at 2:31 p.m. and I had 29 minutes left. All this must be taken into account, so it is necessary to know what type of event it is

That is understood, but why not simply have the three events with a start and stop versus the 6 individual events?

HoursWorked (startTime, StopTime)
Breakfeast (startTime, stopTime)
Lunch (startTime, stopTime)

The both can be done, but the latter is much more complicated and error prone. I know you purposely wanted it this way, but it causes a lot of complexity.

 

[561414]

I understand you want to get work time vs break time. Your TRegistros table is modeled in a strange way, you have a column for Inicio and a column for Final, and you also have a record for Inicio and a record for Final for each type.
Option1
If you're going to record the 6 events, then you only need to store 1 timestamp for each event
Option 2
If you're going to record 3 events (hours worked, breakfast break, lunch break), then you need only 3 records with Inicio and Final.

 

[MajP]

I understand you want to get work time vs break time. Your TRegistros table is modeled in a strange way, you have a column for Inicio and a column for Final, and you also have a record for Inicio and a record for Final for each type.
Option1
If you're going to record the 6 events, then you only need to store 1 timestamp for each event
Option 2
If you're going to record 3 events (hours worked, breakfast break, lunch break), then you need only 3 records with Inicio and Final.

This discussion has also already occured.

Solved - Create a calendar to record work inputs and outputs

Good night. I have searched this forum for examples of calendars, and I have found this attached majP that more or less adapts to what I am looking for. What it lacks is that it can add the hours that I record each day when I enter and leave work, in such a way that it later allows me to...

www.access-programmers.co.uk

In order not to break the Calender Control code (Peter Hibbs) I recommended leaving the second field in. For how the OP plans to use it with two events per checkin and checkout it is not used. .

 

[zelarra821]

Here you have the database with all my records for the month of March. I am also attaching the Excel with the values that the database should obtain.

 

[561414]

Oh, I've never modeled a calendar view in Access, maybe there's stuff I haven't considered. I modeled one in JS populated by an object to which you could push arrays of timestamps with icons and descriptions. No tables were bound to the calendar itself, only its timestamps. I guess something similar could be implemented here.

 

[zelarra821]

HoursWorked (startTime, StopTime)
Breakfeast (startTime, stopTime)
Lunch (startTime, stopTime)

I think it's a good idea.

 

[MajP]

I think it's a good idea

If that is the case this all becomes much simpler.

Then the 30 minute rule can be done straight in the query. I would calculate elapsed time in minutes. look at datediff function.

 

[zelarra821]

If that is the case this all becomes much simpler.

Then the 30 minute rule can be done straight in the query. I would calculate elapsed time in minutes. look at datediff function.

Yes, but I have several doubts:

1. How do I get the values of the above fields? You could use DLookUp, but that would slow down the computation.
2. How do I configure the rule that I put in the first post for breakfast and lunch? At breakfast the half hour is added (if you are more, you have to put half an hour; if you are less, I don't know what will happen because it has never happened to me); and at lunch, if it is less than half an hour, it is subtracted.

If((breakfast end - breakfast start)>0.0208333333333333;0.0208333333333333;breakfast end - breakfast start)

0.0208333333333333 equals half an hour

If((End lunch-Start lunch)>0.02083333333333333;0;(End lunch-Start lunch)-0.0208333333333333)

3. How do I manage when a user deletes a record?

 

[MajP]

I am not sure if I understand the question, but you just need to sum up your hours for the day or week. I assume total the work minus total the meals.

SELECT TRegistros.IdRegistro, TRegistros.Posicion, TRegistros.Inicio, TRegistros.Final, TRegistros.Tipo, GetDurationMinutes([Inicio],[final],[tipo]) AS Duration
FROM TRegistros;

Public Function GetDurationMinutes(StartTime As Variant, EndTime As Variant, EventType As String) As Long
  If IsDate(StartTime) And IsDate(EndTime) Then
    If EndTime > StartTime Then
      GetDurationMinutes = DateDiff("n", StartTime, EndTime)
      If EventType = "desayuno" Or EventType = "la comida" And GetDurationMinutes < 30 Then GetDurationMinutes = 30
    End If
  End If
End Function

Query1 Query1

IdRegistro	Posicion	Inicio	Final	Tipo	Duration
4​	2​	3/8/2023 11:00:00 AM​	3/8/2023 11:25:00 AM​	desayuno	30​
6​	4​	3/8/2023 2:00:00 PM​	3/8/2023 2:36:00 PM​	la comida	36​
8​	​	3/8/2023 8:00:00 AM​	3/8/2023 4:01:00 PM​	Entrada / Salida	481​

A simple group by query could calculate the hours worked per each day minus meals. Or sum between pay period start and pay period end.

 

[MajP]

The vba function could be done with pure iif functions, but would be somewhat of a pain to write.