Creating report for totals

[BRZ-Ryan]

I have a table that stores a log of calls that we receive. We log the time that they were entered and also set a time bracket (separate field, see attach example for time brackets). We also record whether the customer left a message or hung up. There are also extensions that record what line the customer called into. (see attached, probably easier to understand than my explanation).



I need to total the number of calls received in each time bracket in both a total field and separately (hung up or left message). I would also like to have it sectioned into monthly reports (there is a field that stores the exact time\date the entry was logged). A section like this is needed for each extension. I dont know where to start with creating this query\report. Can someone assist.

Fields in my table I think we would use:
Log_time - Exact time\date entry entered (use to determine which month to log under)
Bracket 12:01AM-7:00AM, 7:01- etc see attached
Type - Left message\Hung up
Extension - Special 4942, regular 4997, theres a few more.

I attached a sample of how our current reporting system works, built in Excel. I want to do the same in an Access report.

example.pdf

[June7]

Access can do it, might not be as easy as in Excel.

Probably use CROSSTAB queries. See this recent thread about using CROSSTAB https://www.accessforums.net/queries...ess-39164.html

[Dal Jeanis]

Are the brackets already assigned in your database on the Call Log records, or does the query have to assign them as well?

[Dal Jeanis]

If you already have brackets in your database, this request is pretty easy. I made the assumption that you didn't, to generate this crosstab query. Here's the layout I assumed for the log table:

tblCallDetails
  CallID      Autokey
  CallExt     Text   
  CallTime    Date/Time
  CallType    Text      Values "LM" or "HU"

Create these two tables and load them with the data as shown. The Bracket Name can be anything you want - after you have it working, you can substitute "12:01AM - 7:00AM" as the name for bracket A, for example. I recommend very short names while debugging and laying out the reports.

 
tblBrackets               Create records with these values
  BrackMin    Number      0    7   12   16   17   21   0
  BrackMax    Number      6   11   15   16   20   23   23
  BrackName   Text        A    B    C    D    E    F   Total

tblSums                   Create records with these values
  SumType     Text        LM   HU   Tot

This Crosstab query will then give you the basic layout for your report:

TRANSFORM Sum(IIF(Q1.SumType = "Tot",1,IIF(Q1.SumType = "LM",
          IIF(Q1.CallType = "LM",1,0),IIF(Q1.Sumtype = "HU",  
          IIF(Q1.CallType = "HU",1,0),0)))) AS TheSum
SELECT 
     Q1.CallYear,
     First(Q1.DispMonth) As CallMonth,
     Q1.BrackName
FROM
   (
   SELECT
      Year(TC.CallTime) As CallYear,
      Month(TC.CallTime) As CallMonth,
      First(Format(TC.CallTime,"mmmm")) As DispMonth,
      TC.CallExt AS CallExt,
      TB.BrackName As BrackName,
      TC.CallType AS CallType, 
      TS.SumType,
      Count(TC.CallType) As CallCount 
   FROM  tblCallLog AS TC,  tblBrackets AS TB,  tblSums As TS
   WHERE Hour(TC.CallTime) >= TB.BrackMin
   AND  Hour(TC.CallTime) <= TB.BrackMax
   GROUP BY 
      Year(TC.CallTime),
      Month(TC.CallTime),
      TC.CallExt,
      TB.BrackName,
      TS.SumType,
      TC.CallType 
   ) AS Q1
GROUP BY 
     Q1.CallYear,
     Q1.CallMonth,
     Q1.BrackName
PIVOT Q1.[CallExt] & " " & Q1.SumType;

A call that lands by the microsecond exactly onto the hour will go into the wrong bracket, but this gets you pretty close. You could subtract 1 second or 59 seconds from the Calltime in the Year(), Month(), and Hour() calculations, and it would work for those calls.

[BRZ-Ryan]

If I attach the db, can you assist by setting it up for me? Sorry but this is all so new to me. Thank you so much for the info by the way.

Edit: I modified your query with my table fields etc and the report isn't coming out as I want. Any ideas? Can I send you the db and you can try to work your magic on it?

[BRZ-Ryan]

See edited post. Thats what I get when I run query

[June7]

If CallTime field is not a complete date/time value, Access will default the year to 1899 with the Year() function.

How are you inputting the CallTime?

[BRZ-Ryan]

Its a time field, so "5:24 PM", there's also a separate date field "11/5/2013"

[Dal Jeanis]

So in the Year() and Month() and DispMonth, use the CallDate field (whatever it's called).

That's getting you the general layout that you were looking for. After that, it's a matter of formatting the report based on the query output.

Now, I suspect that I did too much of the work manually, and that MS Access would have happily provided some of the totals that I coded the SQL to create. If that's true, then it will be a matter of pulling the 0-23 record out of the tblBrackets table, removing the tblSums table and TS references completely, changing the transform into a plain Sum(), and then using the features of the report to add totals columns for each extension vertically and each day horizontally. (yep, that easy.... ;0 )

[BRZ-Ryan]

I can't get it to look even close to what I want. I need to take a class.

Link to file if anyone is bored

https://dl.dropboxusercontent.com/u/...xTracker.accdb

[June7]

You have autonumber primary key set in some tables but you aren't using them as foreign key, and are instead saving the text descriptor. The autonumber fields aren't even needed in the tables. This is okay although in large databases, sorting and filtering can be faster with numbers.

The thread I referenced in post 2 is an example very much like what you want.
Here is another: https://www.accessforums.net/reports...ate-39214.html

Brackets in tblBrackets are missing spaces that are in the range value in Calls table. The records won't properly join.

The values in BrackName field of tblBrackets and the TimeSlot values in TimeSlots table are not the same.

Should be more consistent with naming conventions. Some field names have space in them. Advise against spaces in any names. Two tables have tbl prefix and no other objects have prefixes.


The real trick is getting rows in report for every year/month/bracket and columns for every extension/calltype even if data doesn't exist.

This query will return records for every extension/calltype but to get every year/month/bracket requires a dataset of all year/month/bracket combinations. (The part that is unavailable is all year/month combos. If Calls table has at least one record for each year/month, that dataset could be created by query):
SELECT Year([Call Date]) AS CallYear, Month([Call Date]) AS CallMonth, Format([Call Date],"mmmm") AS DispMonth, All_CallType_Ext.[Time Slot], Calls.[Log Date], Calls.[User ID], Calls.[Call Date], Calls.[Call Time], Calls.Caller, Calls.[Named Insured], Calls.[Policy Number], Calls.Comments, Calls.[Called Back], All_CallType_Ext.[Call Type], All_CallType_Ext.Extension
FROM (SELECT [Call Type].[Call Type], Extensions.Extension, [Time Slots].[Time Slot] FROM [Call Type], Extensions, [Time Slots]) AS All_CallType_Ext LEFT JOIN Calls ON (All_CallType_Ext.[Time Slot] = Calls.[Call Range]) AND (All_CallType_Ext.Extension = Calls.Extension) AND (All_CallType_Ext.[Call Type] = Calls.[Call Type]);

Use that query as source for:
SELECT Query1.CallYear, Query1.CallMonth, Query1.DispMonth, Query1.Extension, Query1.[Call Type], Query1.[Time Slot], Count(Query1.[User ID]) AS [CountOfUser ID]
FROM Query1
GROUP BY Query1.CallYear, Query1.CallMonth, Query1.DispMonth, Query1.Extension, Query1.[Call Type], Query1.[Time Slot];

Use that query as source for:
TRANSFORM Sum(Query2.[CountOfUser ID]) AS [SumOfCountOfUser ID]
SELECT Query2.CallYear, Query2.CallMonth, Query2.DispMonth, Query2.[Time Slot]
FROM Query2
GROUP BY Query2.CallYear, Query2.CallMonth, Query2.DispMonth, Query2.[Time Slot]
PIVOT [Extension] & " " & [Call Type];

Use that query as the RecordSource for a report. Set report Grouping & Sorting on the CallYear, CallMonth, Time Slot fields. Calculate the sum of HU and LM values with expression in textboxes in Detail section. Calculate the aggregate sum with expression in textboxes in footer sections.

[BRZ-Ryan]

I'd love to do all of that but I have 0 experience so it's prob not going to happen.

I do appreciate the time you put into it though

[June7]

I gave you 3 query statements that all you have to do is copy/paste into SQL View window of query builder. Then building a report with Grouping & Sorting and calculations in textboxes is not difficult. Access Help has guidelines. You don't get experience if you don't do.

[BRZ-Ryan]

I agree, I'm self taught in VB, Pawn, and C and I always tell new guys on the forums to practice and ask questions instead of asking for work. I just cant seem to grasp the logic that ties in queries to reports and how Access outputs the report. Here's what I got when I plugged in your above queries to a report

[June7]

That does look like grouped report. The DispMonth field could be included in the CallMonth group header. The CallMonth value doesn't need to be displayed, just use it to group and sort. Could tighten the spacing in each section.

Did you create that report with wizard? Form and Report Wizards group the controls. Select all controls, hover cursor over one control, right click > Layout > Remove. Rearrange and size controls as you wish.

Line up the count textboxes with the bracket textbox.

Use more labels in report header to caption the columns: Total, Message Left, Hung Up, and for each extension.

Textboxes in detail section to calculate sums for each extension: = Nz([Auto - 4955 HU],0) + Nz([Auto - 4955 LM],0)

Textboxes in CallMonth group footer to calculate aggregate totals: = Sum([Auto - 4955 HU])

That should look fairly close to the pdf you posted. The gridlines could be a bit tricky. Can set textbox borders to have an outline but there will probably be a little gap between each row. Or try a line control to separate the rows. Or just go with the alternate row color.