Crosstab Query II

[RA]

I collect call data, by customer, by time of day. For
example XYZ Co. called 10 times this week,(3)@1PM, (1)@
9AM, (6)@4:30PM

I want to pull a report that shows all customers and the
calls recieved in all time periods. I used the wizard to
build my intial query and while the format is correct, the
data is wrong. I have the same numbers for all customers
in all timeperiods. I'm sure I'm missing one step, any
ideas?

RA


TRANSFORM Count([Interval Level Data -
Table].calls_offered) AS CountOfcalls_offered
SELECT [Interval Level Data -Table].EnterpriseName, Count
([Interval Level Data -Table].calls_offered) AS [Total Of
calls_offered]
FROM [Interval Level Data -Table]
WHERE ((([Interval Level Data -Table].time_col) Between
#12/30/1899 8:0:0# And #12/30/1899 17:0:0#))
GROUP BY [Interval Level Data -Table].EnterpriseName
PIVOT [Interval Level Data -Table].time_col;

 

[Duane Hookom]

Crosstab Query Reply II:
====================
Can you share
-some sample data (with field names)
-your current SQL view of the Crosstab
-what you actually want for results based on the sample data

 

[Frank Stone]

hi,
I have not seen your querys or data so i am guessing.
is the data coming from 2 tables. if not, disrequard this
post. if it is then you have a link problem. you have
only 1 link and you may need 2. look at the query in
design mode. find another common field in both tables and
add a second link by clicking the second common field and
dragging to the second common field in the second table.

 

[RA]

Fields: Customer name, Date (Call Made), time (call made),
Number of calls


SQL below


Results desired:

Customer name 8:00 8:15 8:30 8:45......

Cust 1 0 2 0 0
Cust 2 2 2 0 0

etc, etc

The query result shows all 1/4 hour time periods from 8AM
to 5PM. The final query data would give me all customers
on the left and time periods across the top.

-----Original Message-----
Crosstab Query Reply II:
====================
Can you share
-some sample data (with field names)
-your current SQL view of the Crosstab
-what you actually want for results based on the sample data

--
Duane Hookom
MS Access MVP
--

I collect call data, by customer, by time of day. For
example XYZ Co. called 10 times this week,(3)@1PM, (1)@
9AM, (6)@4:30PM

I want to pull a report that shows all customers and the
calls recieved in all time periods. I used the wizard to
build my intial query and while the format is correct, the
data is wrong. I have the same numbers for all customers
in all timeperiods. I'm sure I'm missing one step, any
ideas?

RA


TRANSFORM Count([Interval Level Data -
Table].calls_offered) AS CountOfcalls_offered
SELECT [Interval Level Data -Table].EnterpriseName, Count
([Interval Level Data -Table].calls_offered) AS [Total Of
calls_offered]
FROM [Interval Level Data -Table]
WHERE ((([Interval Level Data -Table].time_col) Between
#12/30/1899 8:0:0# And #12/30/1899 17:0:0#))
GROUP BY [Interval Level Data -Table].EnterpriseName
PIVOT [Interval Level Data -Table].time_col;

.

 

[Duane Hookom]

First create a public function that converts any time into time "buckets"
(see function below). Then create a crosstab query with
Column Heading of ParseTime([Time])
Row Heading of [Customer Name]
Value of Count [Customer Name]

You may also need to add all possible time buckets into the query's Column
Headings property.

Public Function ParseTime(pdatTime As Date, _
pintMinutes As Integer) As String
'----------------------------------------------------
' Procedure : ParseTime
' DateTime : 9/3/2004 16:35
' Author : hookomd
' Purpose : partition times into time slots such as
' every half or quarter hour
' ParseTime(#6:24#,30) = "06:00"
' ParseTime(#6:24#,15) = "06:15"
' ParseTime(#6:24#,10) = "06:20"
' ParseTime(#6:24#,6) = "06:24"
'----------------------------------------------------
'
Dim strHour As String
Dim intMin As Integer
strHour = Format(pdatTime, "hh")
intMin = (Format(pdatTime, "nn") \ pintMinutes) * pintMinutes
ParseTime = strHour & ":" & Format(intMin, "00")
End Function

--
Duane Hookom
MS Access MVP
--

Fields: Customer name, Date (Call Made), time (call made),
Number of calls


SQL below


Results desired:

Customer name 8:00 8:15 8:30 8:45......

Cust 1 0 2 0 0
Cust 2 2 2 0 0

etc, etc

The query result shows all 1/4 hour time periods from 8AM
to 5PM. The final query data would give me all customers
on the left and time periods across the top.

-----Original Message-----
Crosstab Query Reply II:
====================
Can you share
-some sample data (with field names)
-your current SQL view of the Crosstab
-what you actually want for results based on the sample data

--
Duane Hookom
MS Access MVP
--

I collect call data, by customer, by time of day. For
example XYZ Co. called 10 times this week,(3)@1PM, (1)@
9AM, (6)@4:30PM

I want to pull a report that shows all customers and the
calls recieved in all time periods. I used the wizard to
build my intial query and while the format is correct, the
data is wrong. I have the same numbers for all customers
in all timeperiods. I'm sure I'm missing one step, any
ideas?

RA


TRANSFORM Count([Interval Level Data -
Table].calls_offered) AS CountOfcalls_offered
SELECT [Interval Level Data -Table].EnterpriseName, Count
([Interval Level Data -Table].calls_offered) AS [Total Of
calls_offered]
FROM [Interval Level Data -Table]
WHERE ((([Interval Level Data -Table].time_col) Between
#12/30/1899 8:0:0# And #12/30/1899 17:0:0#))
GROUP BY [Interval Level Data -Table].EnterpriseName
PIVOT [Interval Level Data -Table].time_col;

.