Results 1 to 3 of 3
  1. #1
    nparson is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020

    Public Services Catalog database for call routing

    I just accepted a job for a moderately sized state agency and I've come in to find they live in the middle ages. I work in the public hotline, where people call in and our job is to connect them to the people that are experts in dealing with the particular subject they need, in the geographical area they live in. Currently that is done by all of the other operators having done this job for a decade plus and having piles of random staff lists printed out and pinned to the walls of their cubes. Half the time those lists are out of date and many of the programs change names frequently enough that I'm having nightmares about my co-workers leaving and no one knowing who to send calls too.

    So, to that end, I'm trying to sit down and create a database that will track this and ease everyone's life substantially. We don't have any money budgeted for an actual developer or any real tools, so I'm starting in Access. So here is the question:

    I need to provide a tool that can tie together a list of over 1000 staff members spread throughout the state, associate each one with an arbitrary number of "services" or "programs" (some people only handle one thing, some people do a dozen different things) for an arbitrary number of counties (some services are handled statewide by a single person, others county-by-county, and some in larger regional groups).

    SO what I have right now is a single staff table, with all of the relevant info (name, division, contact info, etc), and another "services" table that is a list of all the different programs that people might call in for help with, I also have a table with an entry for every county in the state. So What I'm thinking is I need to put a weak entity table in between tblStaff and and tblServices that I'm tentatively calling tblZones, and that table represents a staff member providing a certain service for a period of time.

    Where I'm getting tripped up is on whether or not I can also use the tblZone to act as a sort of tripartite weak entity to show Staff>Service>County, or if I should have another weak entity between tblZone and tblCounty?

    So right now tblZone has a PK, FK's to both tblStaff and tblServices, and I made a separate tblZoneCountyLink table that has a PK, and FK's to tblZone and tblCounty. I think this will work, but I'm not sure if that is too unwieldy to make a form that can create viable record.

    Any insight or suggestions would be greatly appreciated. Thanks.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    Can you tell us a little about yourself? Do you have any experience with database? If so, what sort of activities?

    If you are brand new to database and have selected Access, then my first recommendation is --put Access on the back burner at this time. You need a document of some sort that identifies what is expected of you in simple English. This will set some scope to this project. The next step (which will undoubtedly be cyclic) is to perform some analysis of the business - starting a the highest level and gradually working toward more details. See Business Rules/Facts. Bottom line is you need a plan for your project, and eventually a blueprint for the database. You might do some prototyping with Access to confirm some interim decisions with management and/or others currently working in the business. But any activity with Access this early on can be considered "getting familiar", but it is definitely not where your post/description says you should be focusing attention at the moment.

    I don't mean to sound harsh nor to dissuade you --but many of us have been in your position. There are several articles on Database Planning and Design in the link in my signature. The tutorials from BA-Experts on Analysis are quite useful and humorous-but hit the key points. The tutorials from RogersAccessLibrary will lead you through a design procedure if you work through 1 or 2 of them. Then, once you have identified and vetted the business facts and have a clear description, you can use that same procedure to create models of your proposed database. You can test this -see the stump the model link in those articles.

    Your approach seems fine - the secret is to create some test data from the business and test your evolving model.
    You can do the model with pencil and paper (easier to erase/adjust things). Relatively very difficult to adjust a physical database.
    So be aware of too much detail into Access too early on. The key at the moment is to identify and confirm facts.

    Anyway, that's the insight and suggestions at the moment. Good luck with your project.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    Orange's advice is worthy of serious consideration. The problem for us much of the time is that everyone who joins here seems to get tagged with Novice in their user info (unless they think to edit it at the start) so sometimes that causes us to make incorrect assumptions about somebody's expertise. Sticking with the current assumptions, those links might cover normalization - I don't know. If not, you definitely need to understand that as it is the foundation of a well designed db. If you're great at Excel and don't know much about relational databases, you already have a handicap!
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

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

Similar Threads

  1. Replies: 13
    Last Post: 04-12-2019, 01:09 AM
  2. Replies: 0
    Last Post: 07-25-2016, 11:16 AM
  3. Replies: 1
    Last Post: 03-19-2015, 06:22 PM
  4. Replies: 3
    Last Post: 02-25-2015, 10:02 PM
  5. Querying an Access Web Services Database
    By melanemac in forum SharePoint
    Replies: 7
    Last Post: 04-12-2013, 08:44 PM

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 - Senior Forums