Odd results from Crosstab w/ Date/Time field

[Greg H.]

I have a table like below:

Name Code Date/Time
Anna Shift Start 1/12/2009 6:20
Anna Lunch Start 1/12/2009 12:09
Anna Lunch End 1/12/2009 12:39
Anna Shift End 1/12/2009 15:20
Anna Shift Start 1/13/2009 6:10
Anna Lunch Start 1/13/2009 11:55
Anna Lunch End 1/13/2009 12:24
Anna Shift End 1/13/2009 15:08
Anna Shift Start 1/14/2009 6:06
Anna Lunch Start 1/14/2009 11:38
Anna Lunch End 1/14/2009 12:08
Anna Shift End 1/14/2009 15:05
Anna Shift Start 1/15/2009 6:06
Anna Lunch Start 1/15/2009 11:54
Anna Lunch End 1/15/2009 12:26
Anna Shift End 1/15/2009 15:06
Anna Shift Start 1/16/2009 6:03
Anna Lunch Start 1/16/2009 11:29
Anna Lunch End 1/16/2009 11:59
Anna Shift End 1/16/2009 15:08

When i try to run a cross tab on it, i want the date in the Row headding,
the Code in the column heading and the time as the value. I use
FormatDateTime to get the date value and time value but as you can see below,
my results are not ideal. What am i doing wrong and what can i do so i get 1
date with all the times listed and not multiple rows for the same date.
Below the table is the SQL code.

Date Shift Start Lunch Start Lunch End Shift End
1/12/200 06:20
1/12/200 12:09
1/12/200 12:39
1/12/200 15:20
1/13/200 06:10
1/13/200 11:55
1/13/200 12:24
1/13/200 15:08
1/14/200 06:06
1/14/200 11:38
1/14/200 12:08
1/14/200 15:05
1/15/200 06:06
1/15/200 11:54
1/15/200 12:26
1/15/200 15:06
1/16/200 06:03
1/16/200 11:29
1/16/200 11:59
1/16/200 15:08

TRANSFORM First(FormatDateTime([Login Data]![Date/Time],4)) AS [Time]
SELECT FormatDateTime([Login Data]![Date/Time],2) AS [Date]
FROM [Login Data]
WHERE ((([Login Data].Codes) In ("Shift Start","Lunch Start","Lunch
End","Shift End")) AND (([Login Data].[Rep Name])="Anna") AND (([Login
Data].[Date/Time]) Between #1/11/2009# And #1/17/2009#))
GROUP BY FormatDateTime([Login Data]![Date/Time],2), [Login Data].[Rep
Name], [Login Data].[Date/Time]
PIVOT [Login Data].Codes;

 

[Duane Hookom]

I suggest using DateValue() and TimeValue() functions. You were also grouping
by Login Data!Date/Time.

TRANSFORM First(TimeValue([Login Data]![Date/Time])) AS [Time]
SELECT DateValue([Login Data]![Date/Time],2) AS [Date], [Login Data].[Rep
Name]
FROM [Login Data]
WHERE ((([Login Data].Codes) In ("Shift Start","Lunch Start","Lunch
End","Shift End")) AND (([Login Data].[Rep Name])="Anna") AND (([Login
Data].[Date/Time]) Between #1/11/2009# And #1/17/2009#))
GROUP BY DateValue([Login Data]![Date/Time]), [Login Data].[Rep Name]
PIVOT [Login Data].Codes;