Need help cross-time records table for time attendance

[yakirdvi]

hi,



i have new biometric time attendance clock and it export data of in and out in spread records and i need every shift be on same record,

So how do i create query or report from one table that contain every record one field with time and in other field with check indicate if this record is checkIn and checkOut, to report contain in one field check In time and in other checkOut time, and if i have 2 records checkIn on after the other without checkOut between in one record the check out will be blank.

Also i need consider that i have many employee and i can't mistake between them

for example this is the database table:
I - for Check In
O - for Check Out




and for example the result i need:
( this i made manual on excel



thank you so much for your help,
Yakir

[June7]

Pulling value from another record of same table is tricky. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Give this a try:

SELECT Table1.USERID, IIf([CHECKTYPE]="I",[CHECKTIME],Null) AS CHECKIN,
(SELECT TOP 1 Dupe.CHECKTIME FROM Table1 AS Dupe WHERE Dupe.UserID=Table1.UserID
AND Dupe.CHECKTYPE="O" AND Dupe.CHECKTIME>Table1.CHECKTIME) AS CHECKOUT
FROM Table1
WHERE [CHECKTYPE]="I";

[yakirdvi]

Thank you very much this work
but now i have one more problem if the worker was not check in but check out
the checkout does not appear
it's the same like the check in but not out opposite

and i want to export one field of every record that indicate which clock he check in/out

for example the new results i need:


Thank you so much for your help, it's very helpful.
Yakir

[June7]

How can there be one without the other anyway? Employees forget? People do manage to screw up the simplest things.

Don't think can handle both situations with one query.

Query 1:
SELECT Table1.USERID, Format(IIf([CHECKTYPE]="I",[CHECKTIME],Null), "mm/dd/yyyy hh:nn:ss") AS CHECKIN,
Format((SELECT TOP 1 Dupe.CHECKTIME FROM Table1 AS Dupe WHERE Dupe.UserID=Table1.UserID
AND Dupe.CHECKTYPE="O" AND Dupe.CHECKTIME>Table1.CHECKTIME),"mm/dd/yyyy hh:nn:ss") AS CHECKOUT, Table1.DEVICENO AS DEVICEIN,
(SELECT TOP 1 Dupe.DEVICENO FROM Table1 AS Dupe WHERE Dupe.UserID=Table1.UserID AND Dupe.CHECKTYPE="O" AND Dupe.CHECKTIME>Table1.CHECKTIME) AS DEVICEOUT
FROM Table1
WHERE (((Table1.[CHECKTYPE])="I"));

Query 2:
SELECT Table1.USERID,
Format((SELECT TOP 1 Dupe.CHECKTIME FROM Table1 AS Dupe WHERE Dupe.UserID=Table1.UserID
AND Dupe.CHECKTYPE="I" AND Dupe.CHECKTIME<Table1.CHECKTIME), "mm/dd/yyyy hh:nn:ss") AS CHECKIN,
Format(IIf([CHECKTYPE]="O",[CHECKTIME], Null),"mm/dd/yyyy hh:nn:ss") AS CHECKOUT,
(SELECT TOP 1 Dupe.DEVICENO FROM Table1 AS Dupe WHERE Dupe.UserID=Table1.UserID
AND Dupe.CHECKTYPE="I" AND Dupe.CHECKTIME<Table1.CHECKTIME) AS DEVICEIN, Table1.DEVICENO AS DEVICEOUT
FROM Table1
WHERE (((Table1.CHECKTYPE)="O"));

Query 3:
SELECT * FROM Query1
UN ION SELECT * FROM Query2;

Note that the date values are actually text strings because of the Format() function. I had to use it to eliminate Nulls, otherwise the UNION would not pull in the record for User 4 and I don't know why except that strange things can happen with UNION and null fields.

[yakirdvi]

Hii,
This work perfect, and yes some times employees forget to check in/out,
so these queries makes order in the mess

Again Thank you so much for your help, it's very helpful.
Yakir

[yakirdvi]

Hi,

At the moment I noticed that final query discrepancy,

Duplicate entry values

The original sample employee 16:


And this is the result:


I tried to fix it but I failed,
Are you also the same result or you get proper values?

Thank you,
Yakir

[June7]

Mine has proper results based on the sample data provided in first post.

USERID	CHECKIN	CHECKOUT	DEVICEIN	DEVICEOUT
1	07/10/2014 08:19:16	07/10/2014 12:10:00	1	2
1	07/10/2014 13:00:00		1
2	08/10/2014 07:00:00	08/10/2014 14:37:41	2	2
4		08/10/2014 16:00:00		3

[yakirdvi]

I think that because right now I have more than 4 lines per employee
Try to get the data I took on the employee 16

[June7]

I can only work with data provided.

[yakirdvi]

I was upload a picture with the data
But any way i was see it because I have more than 4 lines per employee
You can try add records and see this.

[June7]

I don't want to try to make up data to match situation. Provide data that you are working with.

[yakirdvi]

I understand,

[yakirdvi]

Hi,

any luck with this?


Thanks
Yakir Dvir

[June7]

Why does your output show CHECKOUT field before CHECKIN? In my results CHECKIN is before CHECKOUT.

Post the exact queries you built.

Or provide db. Follow instructions at bottom of my post.

[yakirdvi]

Hey how are you?

First of all, thank you for your help

I uploaded the database with the relevant table and queries,
The data are drawn from the clock presence,
My goal is that if the employee is not check In before check out, field will be empty, just like the chackin without check out, just upside,ClockInOut TestQuery.zip