in/out query

[realspido]

Hi, could anybody help me with following problem?
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?
APPRECIATE FOR ANY HELP.
Thanks in advance

realspido

 

[Ofer Cohen]

Try a CrossTab Query, use the query wizard to build one.

Something like

TRANSFORM Last(TableName.TimeField) AS LastOfTimeField
SELECT TableName.Emp_no, TableName.DateField
FROM TableName
GROUP BY TableName.Emp_no, TableName.DateField
PIVOT TableName.[In/out]

Note: If the fields in your table are named [Time] And [Date], its not a
good idea to name you field with a name that is a build in function in Access.

Date = Current Date
Time = Current time

 

[Ofer Cohen]

Sorry, I just noticed that you can have more then one In/Out a day, the
example I gave you wont work, it will display only one event a day

--
Good Luck
BS"D

Try a CrossTab Query, use the query wizard to build one.

Something like

TRANSFORM Last(TableName.TimeField) AS LastOfTimeField
SELECT TableName.Emp_no, TableName.DateField
FROM TableName
GROUP BY TableName.Emp_no, TableName.DateField
PIVOT TableName.[In/out]

Note: If the fields in your table are named [Time] And [Date], its not a
good idea to name you field with a name that is a build in function in Access.

Date = Current Date
Time = Current time

--
Good Luck
BS"D

Hi, could anybody help me with following problem?
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?
APPRECIATE FOR ANY HELP.
Thanks in advance

realspido

 

[David F. Cox]

If possible you should redesign your table to record time in and time out ,
otherwise it gets unecessarily very complex. You will have to design a query
that gets the minimum time out that is greater than the time in, and
probably cater for the case where one of the times is missing. i.e. the
minimum of time out that is greater than the time in is also greater than a
subsequent time in.

David F. Cox

Sorry, I just noticed that you can have more then one In/Out a day, the
example I gave you wont work, it will display only one event a day

--
Good Luck
BS"D

Try a CrossTab Query, use the query wizard to build one.

Something like

TRANSFORM Last(TableName.TimeField) AS LastOfTimeField
SELECT TableName.Emp_no, TableName.DateField
FROM TableName
GROUP BY TableName.Emp_no, TableName.DateField
PIVOT TableName.[In/out]

Note: If the fields in your table are named [Time] And [Date], its not a
good idea to name you field with a name that is a build in function in
Access.

Date = Current Date
Time = Current time

--
Good Luck
BS"D

Hi, could anybody help me with following problem?
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?
APPRECIATE FOR ANY HELP.
Thanks in advance

realspido