time attendance

[srdjanub]

I need a calculation of the time spent in a room.
I have eg 2 or more entrances and so many exits to and from a room. I have an access controller at each door both at the entrance and at the exit in both directions (defined in the event_point_id field. So I have a record of each entry and exit, for example:
event_point_id = 1 is IN (door 1)
event_point_id = 2 is OUT(door 1)
event_point_id = 3 is IN (door 2)
event_point_id = 4 is OUT (door 2), etc.

I have to calculate the time spent in a room for a certain period. Signs OUT-INPUT = TIME spent. Every worker goes in and out several times during the day.
Now there are a number of things to keep in mind:
- if there is no exit and there is an entrance (then the exit is ignored and only a warning is issued that there is no exit) and in the calculation part it stands at 0 because we do not know how much he spent inside)
- if it does not have an input and has an output (similar to the first situation - in fact, if the last one was an input it cannot have the same input again afterwards)
- if it happens several times within a few seconds (then ignore those items and only calculate the first item, ie completely erase them from the calculation)
- if there is no exit for more than a couple of hours (about 15 hours) then the worker is considered to have left without checking out and counter
- if the worker reports in the morning when he arrives at work and does not check out and the next day when he arrives at work he does not report or logs off as being considered a mistake because it is impossible to spend more than 15 hours in the room, although this is practically very possible because there are magnetic locks on each door
- the calculation should be by items for the selected worker in the specified selected period

I posted the base with a message. The database is called Access, and the table where the reads are is called acc_monitor_log
I was stuck at the very beginning of calculating the time spent in the room and defining the items that go before: ENTRANCE goes before EXIT, as well as throwing out incorrect items (double entry or exit) and leaving only the correct ones. By what procedure do this?
Thanks

 

[plog]

Using the data you uploaded, can you show what data you expect to end up with?

 

[srdjanub]

Hello, good day

I'd would like to do the calculation as in the picture in attacment but there would be more entrances and exits in one working day
E.g:
Item1. IN 30.04.2020 13:21:10 - OUT 30.04.2020 14:10:32 - WORKING TIME 00:49
Item2. IN 30.04.2020 14:29:15 - OUT 30.04.2020 14:34:40 - WORKING TIME 00:05
Item3. IN 30.04.2020 14:50:07 - OUT 30.04.2020 16:21:44 - WORKING TIME 01:31
...
How to calculate each this items

 

[plog]

You have no April 30th data in your uploaded database; all your expected results are for April 30th. Therefore your starting data and expected results don't jive. So, let's start from scratch.

I need to know what data you are starting with and what data you expect to end with. That's 2 sets of data, the ending data must correspond to whats in the starting data. So, please provide me with 2 sets of data:

A. STarting data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.

Here's what I don't want--explanations, expected results that don't jive with starting data, attempts that do not produce what you want. Just 2 sets of data, starting and expected.

 

[srdjanub]

Ok. I gave you an example of the previous table, but here's an example with the exact data in the table.

 

[arnelgp]

you can create a Query based on the table you posted:

select card_no, [in], [out], Format(switch(IsNull([in] + [out]), Null, True, [out] - [in]), "nn:ss") As WorkingTime from yourTableName;

 

[srdjanub]

Thank you for quick reply
Yes, but how to throw out items from table that are incorrect?
Example from previous post: no_in , no_out, double_in

 

[arnelgp]

you mean don't show them on the Query?

select
    card_no,
    [in],
    [out],
    Format([out] - [in]), "nn:ss") As  WorkingTime
from yourTableName
where not (([in] + [out]) Is Null);

 

[srdjanub]

No,
I was think about all items that have incorrect information. All the items that have a description of the error to throw them out of the query as in the picture below.
I mean, for example, if there is no IN and there is an OUT, that item is mistaken and is eject from the report.

 

[arnelgp]

so use [error description] as your Criteria:

select
    card_no,
    [in],
    [out],
    Format([out] - [in]), "nn:ss") As  WorkingTime
from yourTableName
where (([error discription]) Is Null);

 

[srdjanub]

Yes, but how to catch errors, because i don't have that information in the database?

 

[plog]

I don't think your B data (expected results) is right, but I don't know enough about your data to put it together. I was able to apply card_no=13132807 and got 25 records, but I don't know how to limit it to the user_name=Administrator. What field in acc_monitor_log is that?

 

[srdjanub]

I don't think we understand each other, let's ask a more specific question:
How in this query should i throw out times data that are similar , times that have differences greater than 20 seconds ?
example:
time1: 28.4.2020. 13:51:23
time2: 28.4.2020. 13:51:24
time3: 28.4.2020. 13:51:35
time4: 28.4.2020. 13:51:43
How to cut all time1,time2 and time3 and leave only time4 in this query?

 

[plog]

I don't think you understand how we start to understand each other: I need to be shown data. I need to know what data you are starting with and what data you want to end with. You've yet to successfully do that and you keep throwing new datasets at me refusing to let me get my head around the previous data.

So, here's the last chance. We are throwing out all the data previously and you must provide me with 2 new sets of data that jive in this manner:

A. STarting data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.

Suppose you wanted a query that show all the females with a height over 65 inches:

A:

tblPeople
ID, Name, gender, height
1, Steve, M, 70
2, Nelly, F, 66
3, Gloria, F, 60
4, Bill, M, 71
5, Amy, F, 68


B:

Id, Name, gender, height
2, Nelly, F, 66
5, Amy, F, 68

Starting data, ending data that jive. That's what I need.

 

[srdjanub]

A:
tables
acc_monitor_log

id time card_no event_point_id
32 28.4.2020. 13:01:04 13751793 2
37 28.4.2020. 13:19:17 13751793 2
38 28.4.2020. 13:19:22 13751793 1
43 28.4.2020. 13:26:57 13751793 2
5 28.4.2020. 13:26:57 13751793 2
44 28.4.2020. 13:27:01 13751793 1
6 28.4.2020. 13:27:01 13751793 1
55 28.4.2020. 13:29:58 13751793 2
56 28.4.2020. 13:29:59 13751793 1
58 28.4.2020. 13:30:01 13751793 1
57 28.4.2020. 13:30:01 13751793 2
59 28.4.2020. 13:30:03 13751793 1
61 28.4.2020. 13:30:04 13751793 2
60 28.4.2020. 13:30:04 13751793 2
62 28.4.2020. 13:30:06 13751793 1
63 28.4.2020. 13:30:07 13751793 2
76 28.4.2020. 13:51:16 13751793 2
77 28.4.2020. 13:51:23 13751793 2
20 28.4.2020. 13:51:23 13751793 2
78 28.4.2020. 13:51:24 13751793 2
21 28.4.2020. 13:51:24 13751793 2
79 28.4.2020. 13:51:26 13751793 1
22 28.4.2020. 13:51:26 13751793 1
80 28.4.2020. 13:51:35 13751793 2
23 28.4.2020. 13:51:35 13751793 2
24 28.4.2020. 13:51:38 13751793 1
81 28.4.2020. 13:51:38 13751793 1
82 28.4.2020. 13:51:43 13751793 2
25 28.4.2020. 13:51:43 13751793 2
83 28.4.2020. 14:02:26 13751793 2
87 28.4.2020. 18:53:40 13751793 1
30 28.4.2020. 18:54:17 13751793 2
31 28.4.2020. 18:54:19 13751793 1
88 28.4.2020. 19:16:49 13751793 2
90 28.4.2020. 20:05:50 13751793 1
96 28.4.2020. 20:10:17 13751793 1
99 28.4.2020. 21:54:16 13751793 2
100 28.4.2020. 21:54:17 13751793 1
101 28.4.2020. 21:54:18 13751793 1
102 28.4.2020. 21:54:18 13751793 2
104 28.4.2020. 21:54:21 13751793 1
103 28.4.2020. 21:54:21 13751793 1
105 28.4.2020. 21:54:24 13751793 1
136 29.4.2020. 18:42:45 13751793 2
214 29.4.2020. 19:06:18 13751793 2
216 29.4.2020. 19:06:24 13751793 1
278 29.4.2020. 19:19:33 13751793 2
280 29.4.2020. 19:19:41 13751793 2
327 29.4.2020. 19:25:27 13751793 2
316 29.4.2020. 19:25:27 13751793 2
328 29.4.2020. 19:25:29 13751793 1
317 29.4.2020. 19:25:29 13751793 1
352 29.4.2020. 19:29:47 13751793 2
351 29.4.2020. 19:29:49 13751793 1
353 29.4.2020. 19:29:49 13751793 1


B:

card_no, in, out, workingtime


13751793, 28.4.2020. 13:19:22, 28.4.2020. 13:26:57, 00:07
13751793, 28.4.2020. 13:27:01, 28.4.2020. 13:29:58, 00:02
13751793, 28.4.2020. 13:30:01, 28.4.2020. 13:30:01, 00:00
13751793, 28.4.2020. 13:30:03, 28.4.2020. 13:30:04, 00:00
13751793, 28.4.2020. 13:30:06, 28.4.2020. 13:30:07, 00:00
13751793, 28.4.2020. 13:51:26, 28.4.2020. 13:51:35, 00:00
13751793, 28.4.2020. 13:51:38, 28.4.2020. 13:51:43, 00:00
13751793, 28.4.2020. 18:53:40, 28.4.2020. 18:54:17, 00:01
13751793, 28.4.2020. 18:54:19, 28.4.2020. 19:16:49, 00:22
13751793, 28.4.2020. 20:10:17, 28.4.2020. 21:54:16, 01:44
13751793, 28.4.2020. 21:54:18, 28.4.2020. 21:54:18, 00:00
13751793, 28.4.2020. 21:54:24, 29.4.2020. 18:42:45, 20:48
13751793, 29.4.2020. 19:06:24, 29.4.2020. 19:19:33, 00:13
13751793, 29.4.2020. 19:25:29, 29.4.2020. 19:29:47, 00:04

 

[arnelgp]

there are 4 queries (zzQuery1, zzQuery2, zzQuery3, qryFinal) I made and 2 functions.
the final query that you will be using is qryFinal.

 

[srdjanub]

bravo thats is perfect, thanks a lot
just one detail how to correct chkOUT() function to take first record on the way out and not the last one?

 

[arnelgp]

here I already made the changes.
I am getting 20 hrs reading? so I add the hrs (hh:nn:ss format).

 

[srdjanub]

thank you so much
just one more thing
how to define in zzQuery2 in=1 and out=2 to be a variable? because 1 maybe be 1 and 3 and 5 (3 entry - 3 doors in) or all tree data, and out maybe 2 and 4 an 6 (3 out data-3 door out)?
in: IIf([event_point_id]=1;[time];Null)
out: IIf([event_point_id]=2;[time];Null)

 

[arnelgp]

there are only 1 and 2 on the sample you post. try to post more data with 3, 4, 5 and 6 data.