Phone call log design - please check my logic

[CaveCanem]

I work in a children's social service agency where we receive about 80-100 phone calls per day that several staff must return. Most of those calls are pulled from voice mail, written to paper, then passed around to return. I think that a database could handle this task better, especially now that we are about to be spread out into different office locations where passing paper call logs around will be impractical.



The data that is typically collected from voice mail is pretty simple, but the relationships can be a bit tangled:

• the caller's name and phone number(s),
• one or more alternate contact's name(s) and phone number(s),
• the name(s) of one or more of their children,
• a brief reason for the call, and
• incoming call date/time, and
• call "status" (available for return, reserved for return, and closed/returned).

Later, we will need to record return call date/time for each return call attempt.

Additionally, my coworkers need two forms:

• one that will allow them to easily enter relevant call data as they are pulling it from voicemail, and
• another that will:
  
  • display available calls
  • allow them to change the call "status" as they reserve and then close the call,
  • and will allow them to enter return call date/time for each attempt

It seems that the best practice would be to create a database that contains the following separate but related tables for:

• all contact's names and phone numbers (which could also have a separate table, since different contacts could have one or more phone numbers and could have multiple phone numbers in common)
• the children's names (since they may be related to more than one contact)
• incoming phone call date/time, status, and reason for call (since multiple contacts could leave multiple messages regarding the same child or children)
• return call date/time (since we might have to try multiple times to reach multiple contacts)

However, I can't think of how I'm going to link all of that up in practical way since it is difficult for us to consistently identify related calls when we pull them from voicemail. Unfortunately, callers don't always leave the exact same information from one phone call to the next, so it is difficult to know which incoming calls are related to one another. For instance, a caller might leave their name and phone number in one call but leave their spouse's name and phone number in the next. Or they might leave their name and cell phone number in one call, but leave a work phone and no name in the next.

So my alternate idea is to simply make two related tables:

• one with each record representing data related to incoming calls, and
• another with each record representing data related to returned calls.

And two forms (as mentioned earlier).

There would be some duplication of records in the first table, so it seems like an inelegant solution. I would appreciate any ideas or suggestions for which direction to go with the design.

[June7]

Presume that each child or family is assigned a case number and calls could be related by the case number.

Explore this MS template db http://office.microsoft.com/en-us/te...1033&av=ZAC000

[CaveCanem]

Thank you for your prompt reply. I had thought about the case number idea, but I'm not sure how to apply it in practice. That idea seems to me to work best if we were able to build the case file with all of the relevant demographic information before we have to record multiple phone contacts. However, in our case, obtaining demographic information is the last thing that we do after (sometimes) calling different contacts with different names at different phone numbers back and forth several times about one particular case—all without us knowing that the voice mails are related until we finally get hold of someone who gives enough information to make it obvious that all of the prior calls were connected. And by then, the prior calls have been recorded with separate case numbers.

My best idea to write a little VB macro that executes on exit from the phone number field to display prior cases and calls associated with the entered phone number. Perhaps I could build in a function that would allow the user to select and associate prior calls with case, thereby changing the a previously entered case number to match the proper case.

Does that sound best? I would appreciate any better ideas.

[June7]

Essentially, it comes down to configuring the db interface to accommodate your office dynamics and information flow. But why immediately assign a new case number to a phone call? Why not just leave that data empty until the existing case number is identified or it is determined this is entirely new case? Yes, each call should have a unique ID assigned (autonumber field will serve) when it is logged into the db but that would not be the case file number. The case file number (if not provided by the caller) will have to be searched for and selected. Report can be generated showing phone calls that have no case file number yet associated.

[CaveCanem]

Hmm, good thought. Let me see if I can make that work. Thanks again for your help.