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.

 

[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.

 

[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.

 

[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.

 

[LarryE]

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

You asked how to do that. So I wondered if you were able to do it. As you have learned, you cannot use Max or Min or First or Last to get your Log In and Log Out dates and times. When you receive the EXCEL data, every SECOND record is a Log Out date and time for each employee. So each person could have times that span over two different days. I was able to translate your EXCEL imported data to dates and times logged in and logged out for each employee. That way, dates and times spent in attendance are accurate. Using Max or Min or First or Last will not give you accurate times spent in attendance.

I also created some code that automatically imports any file you select.

I can attach the file if you wish to look at how that was done.

 

[joshirohany]

Sure LarryE....

 

[LarryE]

Sure LarryE....