probelm with multiple attendance at same day

[shod90]

Dear Gents,
I recently created a program that manage employees attendance , It was working fine till one employee attend more than 2 times per day ( In Out then In Out again )
attached photos to be more explained

here at 8/5/2023 he has 4 records; I need to make a query to bring the 4 records in 1 record and calculate total working hours on that day.


something like what i done but more flexible.

here in that query um taking the max records from punch state I and punch state O to bring the final punch for employee, but on 8/5/2023 the query was conflicted!
I also attached the database if anyone can help .

[ranman256]

then they would have 2 records in the list, but you cannot save work hrs like that. (in time format)
you must save it as decimal: 2.5 hrs, 1.78 hrs

THEN you can sum the employee hrs normally.

[shod90]

can you edit for me the attached file , Since i cannot show 2 records in the list .

[Gicu]

You need a ranking query to get the previous record value, have a look at the modified sample attached.
http://allenbrowne.com/subquery-01.html#AnotherRecord
Cheers,

[shod90]

You need a ranking query to get the previous record value, have a look at the modified sample attached.
http://allenbrowne.com/subquery-01.html#AnotherRecord
Cheers,

That's incredible , Many thanks for the help .. If i tried for ages i won't do that , It's too complex for me actualy

[Gicu]

Not that complicated if you take it step by step, have you look at the sample file I've uploaded, the qryCheckinTotals is the final query that should give you what you want.
Cheers,

[ArviLaanemets]

... but you cannot save work hrs like that. (in time format)
you must save it as decimal: 2.5 hrs, 1.78 hrs
THEN you can sum the employee hrs normally.

Or you import your data into Excel, and do the summing there

The lack of coordination between different teams in Microsoft is sometimes incredible! The way the dates and times are defined in Excel and Access is exactly same. Almost all date and time formats in both programs are same - except one group of them. In Access you cant use formats like "[h]", or "[h]:mm"! When there is some reasonable explanation for this, I haven't seen it jet!