Employee Timesheet

[joshirohany]

Dear Experts,
I have a log from Biometric Attendance Machine. The same are imported in Access. I want the form should consider only First In and Last Out and Difference in hours based on date.


Your help is already appreciated.

 

[Gasman]

Look at using functions DateVal;ue and TimeValue, Min and Max
Group on EmployeeID and result from datevalue.
Use format on the time difference of the Max and Min to get hh:mm

 

[joshirohany]

Ok, that works, but how to this value in Form, since there are many employees.

 

[Gasman]

Ok, that works, but how to this value in Form, since there are many employees.

Sorry, I do not understand what you are saying?
You only supplied one employee?

If that query was the source of a form, it would show those values?

You will not be able to sum the worked though as that has been formatted.
Leave it as a number and then format in form.

I only used Timevalue - Timevalue, so if it goes over midnight, you would need to subtract min from Max.

 

[Pat Hartman]

I have a log from Biometric Attendance Machine. The same are imported in Access. I want the form should consider only First In and Last Out and Difference in hours based on date.

To be clear, if someone comes in at 8AM, leaves at 8:30, returns at 4PM and leaves at 5PM, you want the time between 8AM and 5PM or 9 hours rather than 1.5 hours?

 

[plog]

I want the form should consider only First In and Last Out and Difference in hours based on date.

Why? And what's the big picture?

A form is for adding/editing data. But the above request is asking for an aggregation of data, which will no longer be editable (because it'll be based on multiple records at once). So that defeats the purpose of a form.

What is it you actually want to accomplish? What will you be doing with the First/Last and total hours?

 

[LarryE]

Here is a screenshot of the first 35 records of 105 records:

You don't have any other tables. So you cannot relate an employee table with a dates worked table and further, a times worked table. So the very first thing you need to do is design the database application properly with tables relating to one another. At the very least, you will need an Employees table, a Dates worked table and a Times worked table.

1. You have multiple employees
2. Each employee works multiple dates
3. Each date worked has multiple times worked during that date worked
4. Each Time period worked has a Beginning time and an End time

Then, and only then, can you begin to determine which employee worked each date and for what hours. Then, you can calculate the difference between hours.

 

[tvanstiphout]

Here is a screenshot of the first 35 records of 105 records:
View attachment 111678
You don't have any other tables. So you cannot relate an employee table with a dates worked table and further, a times worked table. So the very first thing you need to do is design the database application properly with tables relating to one another. At the very least, you will need an Employees table, a Dates worked table and a Times worked table.

1. You have multiple employees
2. Each employee works multiple dates
3. Each date worked has multiple times worked during that date worked
4. Each Time period worked has a Beginning time and an End time

Then, and only then, can you begin to determine which employee worked each date and for what hours. Then, you can calculate the difference between hours.

Given that dates and times are all DateTime data type, point 3 and 4 can collapse to:
3&4: Each period worked has a Beginning datetime and an End datetime.
This also accommodates working past midnight, or 25 hours in a row, or other esoterica.

 

[LarryE]

Given that dates and times are all DateTime data type, point 3 and 4 can collapse to:
3&4: Each period worked has a Beginning datetime and an End datetime.
This also accommodates working past midnight, or 25 hours in a row, or other esoterica.

And each employee and related dates and related times all need to be in separate tables and relationships established. At this point the OP is a long long way from creating forms or queries or calculations. They need to design it properly first.

 

[Gasman]

Would just another field indicating entry or exit help the situation?

 

[arnelgp]

see FirstInLastOutQry query and TimeDifferenceQry query (also in design view).
now open TimeDifference form that uses TimeDifferenceQry as recordsource.
the User-defined functions is what i get from CHATGPT.

 

[Pat Hartman]

The reason that time keeping systems require users to log out is because you cannot otherwise reconcile the issue I pointed out earlier. Therefore, you need to add a logout option. You also need to "close" any "open" login records.

I prefer the Draconian approach to things like this. So, my assumption would be, if there is an open log in record, it was an accident and so I would update it with the same time as the log in. i.e. ZERO hours worked.

Eventually, they get the message and remember to log out.

 

[joshirohany]

Team,
Sorry was out of town so, couldn't reply. Its just one employee as sample, there are many employee.

a. I can see First in and Last out of the employee.
b. The Sheet 1, which is created was more of horizontal since there are many employee and different months attendance to considered.

Request to share a drop box for month so we can filter which month attendance and a drop box for other employee.

Thanks for helping.

I have attached another employee details.

 

[arnelgp]

try and test.

 

[ebs17]

The data is incomplete, or the task is incorrect and arbitrary.

When people cannot dematerialize, an IN is always followed by an OUT, and an OUT is always followed by an IN. A presence would therefore be the difference between the time OUT and the immediately preceding IN.

So if you wanted to calculate times per day, you would break an even number of time recordings per day. This is not the case with your data on the first day. If the data were complete, employees would stay overnight, which would result in completely different times.

You can do some quick calculations, but what's the point of an arbitrary and data-remote calculation other than to pass the time.

Pat's suggestion is good, but with seven time recordings per day, in the given representation you don't know which record you would have to neutralize.

 

[Pat Hartman]

Pat's suggestion is good, but with seven time recordings per day, in the given representation you don't know which record you would have to neutralize.

I would neutralize all of them if they didn't pair. There is no way to make sense out of random in/out punches.

The other option with this data is to simply pair two punches. the guy works for an hour in the morning, comes back at 6 and works until 9 the next morning. The punches are also out of order so I don't know what that means. I guess you generate negative hours.

GIGO

 

[LarryE]

Dear Experts,
I have a log from Biometric Attendance Machine. The same are imported in Access. I want the form should consider only First In and Last Out and Difference in hours based on date.


Your help is already appreciated.

Have your questions been answered?

 

[joshirohany]

Have your questions been answered?

Yes. Super thanks to all.

 

[LarryE]

@joshirohany:

1. Have you been able to convert your Sheet1 imported table information into an ACCESS relational design?
2. Is anyone allowed to enter data into your application that has NOT been imported from an EXCEL file?
3. How are you selecting "weeks" to display?

 

[joshirohany]

Hello LarryE,

1. Have you been able to convert your Sheet1 imported table information into an ACCESS relational design? I do it manually
2. Is anyone allowed to enter data into your application that has NOT been imported from an EXCEL file? I import logs manually
3. How are you selecting "weeks" to display? weeks are not required since in India its on day.

Sorry for late reply.