in/out query

[realspido]

Hi,
I have a table:
Emp_no Date Time In/out
58001 27/7/06 7:01 In
58001 27/7/06 8:23 Out
58001 27/7/06 11:55 In
58001 27/7/06 17:04 Out
58002 27/7/06 17:00 Out
58003 27/7/06 7:00 In
58003 27/7/06 17:00 Out

What I need to get as result is something like:

Emp_no Date In Out
58001 27/7/06 7:01 8:23
58001 27/7/06 11:55 17:04
58002 27/7/06 - 17:00
58003 27/7/06 7:00 17:00

How to build a query which will do it for me?
PLS HELP ME!!! APPRECIATE FOR ANY HELP.

realspido

 

[Michel Walsh]

Hi,


SELECT a.empNo, a.dateTime As LogIn, b.dateTime As LogOut

FROM (myTable As a LEFT JOIN myTable As b
ON a.empNo=b.empNo AND a.dateTime <= b.dateTime)
LEFT JOIN myTable As c
ON a.empNo=c.empNo AND a.dateTime <=c.dateTime

WHERE a.InOut = "in" AND (b.InOut="Out" OR b.InOut Is NULL)
AND (c.InOut = "Out" OR c.InOut Is
NULL)

GROUP BY a.empNo, a.dateTime, b.dateTime

HAVING Nz(b.dateTime,0) = Nz(MIN(c.dateTime), 0)



Should do. If you are only interested in completed 'transactions' ( those
that have and a in and a out), you can simplify a little bit:


SELECT a.empNo, a.dateTime As LogIn, b.dateTime As LogOut

FROM (myTable As a INNER JOIN myTable As b
ON a.empNo=b.empNo AND a.dateTime <= b.dateTime)
INNER JOIN myTable As c
ON a.empNo=c.empNo AND a.dateTime <=c.dateTime

WHERE a.InOut = "in" AND b.InOut="Out"
AND c.InOut = "Out"

GROUP BY a.empNo, a.dateTime, b.dateTime

HAVING b.dateTime = MIN(c.dateTime)




I also assume your dateTime is in ONE field, it is, isn't it? If not, it is
much more verbose. As example, when you compare 3' 11" with 3' 8", it is
harder than if you compare 47" with 44", in the last case, it is just ONE
test, in the first case, it is:

feetA > feetB OR ( feetA=feetB AND inchesA >= inchesB)


much, much less elegant than


totalInchesA >= totalInchesB


Same for date, and time.




Hoping it may help,
Vanderghast, Access MVP