Crosstab Query, Group & Sort by Hour in the day

[Guest]

I have a call log with a time stamp and a buyer name.
I want to make a form based on a crosstab query that will give me a table
with the count of calls logged by hour in the rows and by BuyerName in the
columns.

My query gives me the data but I can't get it to format and sort correctly.

I want my first column in my FORM to look like this (sorted in chronological
order):
Want Getting
8 am 1 pm
9 am 10 am
10 am 11 am
11 am 12 pm
12 pm 2 pm
1 pm 3 pm
2 pm 8 am
3 pm 9 am
etc...

I've attempted to use DatePart and "Hour", but can't get the form to format
the hour with am/pm, changing the format etc...

When I use the following query, I get all the data, but all I get in the
first column is the 2 digit hour without the "am/pm" and when I bring it into
the form and try to apply a time format, in converts everything to 12 am.

TRANSFORM Count(Hour([CallLog]![LogTime])) AS Expr1
SELECT DatePart("h",[LogTime]) AS Times
FROM ContactType INNER JOIN (Buyers INNER JOIN CallLog ON Buyers.BuyerID =
CallLog.BuyerID) ON ContactType.ContactTypeID = CallLog.CallType
GROUP BY DatePart("h",[LogTime])
ORDER BY DatePart("h",[LogTime])
PIVOT Buyers.ShortName;

Thanks for any help,
MikeZz

 

[[MVP] S.Clark]

The Format function allows you to define how to format the data. See help
file for more info.

e.g. Format("mmm dd, yyyy hh:nn:ss", [DateField])

 

[Guest]

I got it to work by having both fields in the crosstab...
I have the 2 digit hour as the first column and sort by that and a second
column with the text version of the time. I just hide the first column on
the form and it works perfect.

Thanks,

The Format function allows you to define how to format the data. See help
file for more info.

e.g. Format("mmm dd, yyyy hh:nn:ss", [DateField])

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a call log with a time stamp and a buyer name.
I want to make a form based on a crosstab query that will give me a table
with the count of calls logged by hour in the rows and by BuyerName in the
columns.

My query gives me the data but I can't get it to format and sort
correctly.

I want my first column in my FORM to look like this (sorted in
chronological
order):
Want Getting
8 am 1 pm
9 am 10 am
10 am 11 am
11 am 12 pm
12 pm 2 pm
1 pm 3 pm
2 pm 8 am
3 pm 9 am
etc...

I've attempted to use DatePart and "Hour", but can't get the form to
format
the hour with am/pm, changing the format etc...

When I use the following query, I get all the data, but all I get in the
first column is the 2 digit hour without the "am/pm" and when I bring it
into
the form and try to apply a time format, in converts everything to 12 am.

TRANSFORM Count(Hour([CallLog]![LogTime])) AS Expr1
SELECT DatePart("h",[LogTime]) AS Times
FROM ContactType INNER JOIN (Buyers INNER JOIN CallLog ON Buyers.BuyerID =
CallLog.BuyerID) ON ContactType.ContactTypeID = CallLog.CallType
GROUP BY DatePart("h",[LogTime])
ORDER BY DatePart("h",[LogTime])
PIVOT Buyers.ShortName;

Thanks for any help,
MikeZz