select query

[realspido]

Hello,
I strugle with problem:
I have a table CREXPORT:
GH_1_1 DE_1 DE_2 DE_3
58001 27/6/06 7:01 In
58001 27/6/06 16:57 Out
58001 28/6/06 7:05 In
58001 28/6/06 17:00 Out
58002 27/6/06 17:01 Out
58002 28/6/06 7:09 In
58002 28/6/06 16:55 Out
......
I'd like to create a query which could give me as result:

No Date In Out
58001 27/6/06 7:01 16:57
58001 28/6/06 7:05 17:00
58002 27/6/06 - 17:01
58002 28/6/06 7:09 16:55
......
Could anyone help me, please?

Thanks

 

[Jerry Whittle]

TRANSFORM First(CREXPORT.DE_2) AS FirstOfDE_2
SELECT CREXPORT.GH_1_1, CREXPORT.DE_1, First(CREXPORT.DE_2) AS [Total Of DE_2]
FROM CREXPORT
GROUP BY CREXPORT.GH_1_1, CREXPORT.DE_1
PIVOT CREXPORT.DE_3;

I got this to work only with all the fields at Text data type. You might
want to consider putting the date and time in one day/time field. You might
still run into problems if the 'out' happens after midnight.