Re: Crosstab Query II
From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 09/03/04
- Next message: John Spencer (MVP): "Re: sum of column information"
- Previous message: Paul James: "Data type mismatch in criteria expression"
- In reply to: RA: "Re: Crosstab Query II"
- Next in thread: Frank Stone: "Crosstab Query II"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 3 Sep 2004 16:41:22 -0500
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 -- "RA" <anonymous@discussions.microsoft.com> wrote in message news:5a9201c491e1$7dee12c0$a601280a@phx.gbl... > 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 > >-- > > > >"RA" <anonymous@discussions.microsoft.com> wrote in > message > >news:57b801c491b5$6604b870$a601280a@phx.gbl... > >> 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; > >> > > > > > >. > >
- Next message: John Spencer (MVP): "Re: sum of column information"
- Previous message: Paul James: "Data type mismatch in criteria expression"
- In reply to: RA: "Re: Crosstab Query II"
- Next in thread: Frank Stone: "Crosstab Query II"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
