Tables and Forms

[kastaman]

Hi there,

I have a project to use Access 97 as opposed to Excel in entering Data for some reporting in the future.

We receive 3 reports (hard copies) and we need to enter this info.

I thought I creted the tables necessary but when I was planning on creating a form this is where I got stuck.

Here is the info of the report and data it contains:
Operator Report
Date
Time Interval
Agent ID
Agent Name
# of calls
# of abandon
# outbound
-------------
Queu Report
Date
Time Interval
Abandon Call #
-------------
Total Operator Report
Date
Agent ID
Agent Name
Calls Transferred
Login Time
Not Ready Time
InBoundTalk time
Internal Calls
Outbound Calls
Internal Talk Time
External Talk Time
----------------------

There are redundant info and the interval time (8:00, 8:30, 9:00 until 6:00 pm)is giving me a headache on how to create the tables and form that minimizes the keying.

Any thoughts are appreciated.

Kastaman

 

[GingerR]

essentially, as you probabaly can image, you get the bare, raw data in there, then you can add and subtract and group and calculate to your heart's desire.

1) you will need a table Operators with fields
AgentID
AgentName
etc

2) you will need a table to store the CALL INFO. I'm not too sure of exactly what fields you will need because I am not totally familiar with your business processes. I'm trying to translate from what you wrote above so you may have to tweek this.

so, table Calls with fields:

AgentID
Date might be redundant? see DateTime fields below
possible fields??
CallReceivedDateTime
CallAnsweredByAgentDateTime
CallCompletedByAgentDateTime ??} can't tell--you have to figure this out to get what you need above.
Internal (Yes/No)
Transferred (Yes/No)
Outbound (Yes/No) {if Oubound is the exact opposit of Internal (or Inbound), then you only need one or the other; then on reports, etc you will count YES = Outbound and NO = Internal (or visa-versa) ok?}
Abandoned (Yes/No)

what does NotReadyTime equal? the time between the CallReceivedDateTime and the CallAnsweredByAgentDateTime? this is the kind of thing you have to figure out. In this case, say this were true. you will NOT store NotReadyTime in a table any place; it will be a 'calculated field' on forms and reports.

3) in your reports it looks like TimeInterval is something the user enters, i.e "I want to see all the stuff for 12/1/01 thru 12/31/01?? is that it? you will have a form (frmReportChoice) with buttons on it that the user presses to get these reports. on that form will be two text boxes, one called txtDateFrom and the other called txtDateTo (add appropriate labels). you will use these fields in your criteria for generating reports.

4) make a query. bring down both tables and join at Agent ID. bring all the fields down into the columns of the query grid. run it--see how it all fits? then play with making TOTALS queries (menu View+TOTALS). for example, choose View+TOTALS, leave AgentID and AgentName as GroupBy, but make Date (from CALLS table) be COUNT. Group on DATE too. this would give you how many calls were for each agent for each date. you can play around with that to get the reports you want.

another example is you could COUNT the field ABANDON if it = YES in the criteria, for example to see how many ABANDONED calls each agent got.

5) as for date range: in DATE criteria, put

 Between forms!frmReportChoice!txtDateFrom and forms!frmReportChoice!txtDateTo

6) as for calculated fields (External Talk Time) you can calculate this in totals queries too (use SUM) however if you may first have to subtract CallCompletedByAgentDateTime-CallReceivedByAgentDateTime, and then SUM on that (if that's how you set it up).

so, i know this isn't so concrete, but you have to figure out the business process, i.e. what makes up those calculated time fields, etc. Why don't you get started, see how much you can get set up, and come on back if you need more help....

g

 

[kastaman]

GingerR,

Thanks for the quick reply. I forgot to mention that all these data are already calculated, all we need to do for the moment is to input them in a effecient manner.

These details are from a call centre report.

I guess I need some help in planning the tables and forms.

Can you reasses and provide some advise.

Thanks,

Kastaman