Crosstab query, help

[elena]

Hi, All
I have table with a few fields of user's activities, it looks like foloowing:

IsDate IsTime Id Unit Activity Code
5/1/07 8:00 614 01 SignOn SO
5/1/07 12:00 614 01 Breakstart BB
5/1/07 12:30 614 01 Breakend BE
5/1/07 14:30 614 01 Breakstart BB
5/1/07 14:45 614 01 Breakend BE
5/1/07 17:30 614 01 Breakstart BB
5/1/07 17:40 614 01 Breakend BE
5/1/07 18:00 614 01 Statustart SB
5/1/07 18:30 614 01 Statusend SE
5/1/07 19:30 614 01 EndShift SF
5/1/07 19:30 614 01 Download DL

I need result looking as crosstab query:

ID Unit SignOn BB1 BE1 BB2 BE2 BB3 BE3 SS1 SE1
SF DL
614 01 8:00 12:00 12:30 14:30 14:45 17:30 17:40 18:00 18:30 19:30
19:30

It shows the time when user sign on, takes break(up to 3 breaks), then
status begin(some other activities) and status ends, shows end shift and
download time, and group by Id and Unit.
Please, help

 

[Duane Hookom]

Try this SQL:

TRANSFORM First(tblUA.IsTime) AS FirstOfIsTime
SELECT tblUA.ID, tblUA.Unit
FROM tblUA
GROUP BY tblUA.ID, tblUA.Unit
PIVOT

 & DCount("ID","tblUA","IsDate =#" & [IsDate] & "# AND ID = " &
[ID] & " AND Code ='" & [Code] & "' AND IsTime<=#" & [IsTime] & "#");

 

[Jerry Whittle]

Your data doesn't support the results that you want. To do so you need Codes
BB1, BB2, etc.

Otherwise you will need some pretty complex/convoluted code.

 

[elena]

Thank you for reply,
It didn't work out.
I change the table layout

IsDate Id Unit SO BB BE SB SE SF DL
5/1/07 614 01 8:00
5/1/07 614 01 12:00
5/1/07 614 01 12:15
5/1/07 614 01 15:00
5/1/07 614 01 15:30
5/1/07 614 01 15:45
5/1/07 614 01 16:45
5/1/07 614 01 17:00
5/1/07 614 01 17:01

How to produce result(group by Id, Unit)
IsDate Id Unit SO BB1 BE1 BB2 BE2 SB SE SF DL
5/1/07 614 01 8:00 12:00 12:15 15:00 15:30 15:45 16:45 17:00 17:01

Please, help

 

[Duane Hookom]

What didn't work? Did you try the SQL that I suggested? Can you reply with
the SQL view of your effort?

 

[elena]

Sorry, Duane
It does work! Data looks very accurate
I just have to limit some output fields
Thank you so much!