Results 1 to 6 of 6
  1. #1
    ddc_operations is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2021
    Posts
    13

    Trying to map out a Database for daily call center records - need help talking through a design

    I am pretty new to relational databases, so please be gentle... I'm looking for some pretty basic design ideas for the best way to build a database that is efficient and will compile data run over time, allowing me to manipulate it through reporting/queries (which I suppose is the whole purpose of relational databases anyway). I have worked on a few previously designed databases and have been able to pick through them and get the idea of how they are set up and build off of them, but for a ground-up build, I am a little in the weeds, and don't want to go too complex, too quickly.



    Here's the situation:

    I have a call center that just got a new NEC phone system, and the reports natively available from the system are (unsurprisingly) not so useful on their own.

    I can run several reports, and download the data, but with some idiotic idiosyncrasies. I thought I could start off with dealing with just one of these reports, get the guts of the database in place and then add more reports/data after I have a good foundation from which to expand.

    For example: A report called "Agent Call Summary" gives the following fields:
    - 'Agent' (in an infuriating "Name - xxx" format, where xxx is the extension number)
    - 'Time Logged in' (in hours:mins:secs)
    - 'Number of Calls'
    - 'Avg Calls per hour'
    - 'Number of unanswered calls'

    What is NOT INCLUDED ANYWHERE in the .csv is the freaking DATE the data is from!

    Now, I have written some queries to break up the agent field into separate name/extension fields, and created a couple of new fields manipulating some numbers, but I don't know a few things:

    How do I set up my database so that it is centered around either an agent name or extension number (both of which should remain constant over time, i.e. person 1 will always be at extension 1, etc.)

    How do I best connect the queries that I have written to the data originally downloaded? (this might be worded weird, but I have functional queries that create new fields by taking the original data from an external file), but I don't know what to do with it once I have it, so that everything is kind of back in one place.

    How can I append a date field to the data each time I download it (assuming I run it once per day at the end of the day), so that I can throw it to an Archive table, and then pull data by dates or date ranges?

    OK. I hope that's not too much rambling, and that it makes sense what I am trying to do. At the end of the day, I want to be able to choose a date, or range of dates for which I can print a report of the data that I pulled from this report, adding some fields that I created to show, say a ratio of answered to unanswered calls, etc., and then can further pare it down by agent if necessary. (basically, what did agent 'a' do on days 'x,y,z')

    Thanks for your help - feel free to point me to resources that may be better suited to step me through this.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    When new records are imported, follow with an UPDATE action SQL that adds current date to those new records.

    UPDATE table SET datefield = Date() WHERE datefield IS NULL;

    Example of search utility http://allenbrowne.com/ser-62.html

    For a db, consider https://www.microsofttemplates.org/m...-database.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is there any documentation describing the intrinsic database within the NEC phone system?
    Is there a user group for this product and its software?

    With Access don't use names with embedded spaces or special characters. Use alpha and numeric plus underscore(_) if necessary.

    You might want to get a sample of each type of report and "reverse engineer" the underlying database structure or ,perhaps more realistically, one that you can populate repeatedly that allows you to get you what you need.

    You could download some sample data -and let readers assist/advise.
    Good luck.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    It is a summary?, what do you expect.?
    You would need to export the detailed log to get that data?

    I used to import data from two calling systems into Access, as one did not keep the logs going back too far. The other was a call recording system.
    I created users in the Access system to match their telephone extensions, then used that to produce reports/examine calls etc.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    ddc_operations is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2021
    Posts
    13
    Yes, I understand that this report in and of itself is a summary... there are slightly more detailed reports, but I was using this simple example as a jumping off point for my build. Unfortunately, according to the folks over at NEC, there is not a way to dump a detailed log of all activity (or at least not of the type of activity I am looking for). The data for which I am searching is part of a call center (ACD- Automatic Call Distribution) queue system, so the data (according to the gentleman with whom I spoke) is controlled by and made accessible by a third party's proprietary software (what I used to generate the simple summary I described) and there is no way to get the full data dump.

    I could capture SMDR data from the system as a whole, but that will not show any difference between calls that were routed through the ACD, and those that were dialed direct, nor would it allow me to see how each of my agents responded to each call (whether they answered, let it pass through their spot in the queue, or how it traveled through the queue).

    So I am stuck with what I've got. But I do think creating users first, matching them to the extensions, and then pulling in whatever data I have from several different reports would be the way to get started. I just have to come up with a clever way to add a date field in such a way that I would minimize the possibility of it being incorrectly recorded/associated with the data it should be associated with if someone else's hands are also in the pot.

    Thanks for the replies all... if you have any other ideas, please don't hesitate to chime in (again)!

    Thanks,
    -d

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No, that sort of data you would have to get from their system.
    My bosses were mainly interested in how long people were on the phones etc.

    As they say 'garbage in, garbage out'
    Doesn't sound like a very good system. Good luck with it

    We had one system we used, where we could have asked for tailored reports, but I decided that it would be better for us (turnaround and cost) to do the reports ourself?
    So we commisioned a oneoff program to denormalize the data, and dump everything via date selections.
    Then I just imported that into another DB and did whatever the bosses wanted with it.

    You might be able to explore that route, if it is not too cost prohibitive?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 04-08-2020, 08:50 AM
  2. Check my DB and see what Iam talking about
    By Lou_Reed in forum Access
    Replies: 7
    Last Post: 03-10-2016, 03:44 PM
  3. Need help and advice designing a call center database.
    By AbandonedRobot in forum Database Design
    Replies: 3
    Last Post: 07-20-2014, 01:49 PM
  4. Database Design: Normalize daily scheduled time
    By Lorlai in forum Database Design
    Replies: 1
    Last Post: 03-12-2013, 12:26 PM
  5. Database for daily entry of records datewise.
    By Goldenbird in forum Database Design
    Replies: 13
    Last Post: 06-20-2010, 10:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums