Solved

Advice on Access 2007 Database Design

Posted on 2009-05-12
8
913 Views
Last Modified: 2013-11-29
I have been asked to assist an investigations company in revamping their medical investigations database and business process. The process goes something like this: They get a fax from a customer with the information they need to do the investigation. Major topics include: Requestor Info | Type of Service Requested | Location and Time Frame to Search (sometimes multiple) | Subject Info (doctor name, address, alias, license, etc.). They enter some of this info into a single Access table, then use a typewriter and a pen to enter the results onto a triplicate form. It's really a nightmare, and especially so because some of their big clients have asked for paperless results. As of now, they haven't even been logging the results into the database.

They seem to want me to create a huge table with a ton of fields. I don't think this is what I should do, but I am inexperienced in creating complicated relational databases. I will post a couple screenshots of what they have, and what I have created so far in the hopes that ya'll can point me in the right direction or let me know if I'm on the right track. The relationships seem to work, but creating forms for data entry when the data is spread between a few related tables has proven to be above my skill set. I need some help please.

I will include a form I created to try and replace the old paper triplicate form they were using. I created it in Livecycle Designer and had hoped to somehow link it to the Access DB. I think it will help outline the situation. The top half of the form is the info we get via fax. The bottom half is the info the investigator responds with.

Thank you so much for taking the time to help me. I have gotten really frustrated while developing this solution, especially since I think I spent a lot of time learning tech that doesn't directly apply. Times winding down on my completion date and I freaked.

1.Before.jpg
2.My.Ideas.To.Change.jpg
3.The.Adobe.Designer.Form.jpg
0
Comment
Question by:Hillyman
  • 3
  • 2
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Hillyman,

In a nutshell:

The main table must be "Normalized" in order to work in a True relational database.

Meaning each of thoes "repeating fileds" " will be brought out into it's own table (Results 1A, Results1B, ...ect)
If not, then you will have to add more fields each tme you add a new "Result"
Again, this is an in-efficient design.

Perhaps another Expert can go into more detail, but basically you need to "Normalize" this table into seperate tables then relate these tables.

Here is a great reference:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201694719

If it is crunch time, then you might want to think about partnering with an Access developer who can work closely with you on this.
1. To get it done in a timely manner
2. To teach you the concepts of Database design and normalization.

JeffCoachman
0
 
LVL 84
Comment Utility
It seems you're on you way to correctly normalizing the data, but without knowing more about it it's pretty tough to give concrete advice regarding this. I'm not sure how your Locations table is related to your Results table, however, and it seems that relationship would be incorrect.

As to your UI - typically, a Master/Child relationship such as your Contracts-Results relationships would be shown through a Master/Detail form. You really need to get the table structure nailed down before you really even start on the UI, however.

0
 

Author Comment

by:Hillyman
Comment Utility
I read some suggested articles on data normalization and relational design, but I still have some problems getting my head wrapped around the proper design.

1) When we receive a investigation request from a client, they specify either a doctor or a company to investigate (see "Search Parameters" section of form above).  The doctors, and sometimes even the companies, often have multiple names or aliases.  I could create a Doctors table and a Companies table, but when I start to enter field names like AKA1 and AKA2 I break the normalization rules.  So should I create an Aliases table?  What's more, they often give us multiple addresses for these companies and doctors which muddles things even more.

2) About the locations (see "Area and Time Frame to Investigate" section on form above).  When we get a investigation request, our client often specifies more than one county they want searched.  The investigator does his thing and finds all results for each county (there can be none or many).  If I create a Requests or Orders table and enter reqCounty1 (ie Los Angeles), reqTime1 (ie 2000-2009), reqCounty2 (San Francisco), reqTime2 (2005-2009), etc. - I start breaking the normalization rules.  This is why I had the Locations table.  For every request, there can be many locations, and every location can have many results.

I am so upset by this it is unreal.  I can normally pick things up rather quickly, and this is giving me a really hard time.  I will not give up until I figure this out, and I appreciate any and all input.

One of the problems is that the existing database is not my design.  That huge list of fields you see above, the "Contacts" table (which should actually be "Requests", was not created by me, and it has been in use for about three years.  Of all the fields you see in the list above, only above 15 are being used, and those are being horribly abused.  There are field/value pairs like this:

Subject - "Jekyl, Hyde, MD (aka: Hyde Jekyl, MD)"
Address - "1) 458 Database Dr., Duarte, CA   2) 759 Frompton Ave., Dustin, NV"
ZIP - "1) 84589 2) 54163"

I am going to try and outline the business process the database is to serve a little better, then start a design from scratch to post and get feedback on.  I could use some advice on the whole doctors/aliases thing and anything else you might think pertinent in the meantime though.  Thanks!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
LSM is better at this topic that I am.

I will let you continue with him to avoid any conusion.

;-)

Jeff
0
 
LVL 84
Comment Utility
You'd have an Investigations table. Since an Investigation could encompass a person/company with multiple names, you'll need some way to relate all those names to an Investigation. This would require 3 tables:

tInvestigations
------------------
lInvestigationID
sInvestigationname
dStartDate
etc etc

tClientNames
---------------------
lClientID
sClientName
etc etc

tInvestigationClients
-----------------------------
lInvClientID
lInvID
lClientID

Since a client could have multiple locations, the same logic would apply:

tLocations
---------------------
lLocationID
lClientId
sLocationname
sLocationAddress
etc etc

so you can now store multiple clients for an Investigation, and you can store multiple Locations for each Client

You now must store results, apparently based on a Location (?) If that's true, then build another table:

tResults
----------------
lResultID
lInvID
lLocationID
dResultDate
sResult
etc etc

So for each location, you can store multiple Results. A Location is related to a Client, but I'd suspect a Client could be involved in more than one Investigation, hence the need to store InvID and LocationID

0
 

Author Comment

by:Hillyman
Comment Utility
Thank you very much LSM.  I can hear the hinges on the iron door starting to squeak as the door leading into the room of understanding opens up ever so slowly.  :) Since we've come this far, can you please take a look at my diagram as I explain it a little?  I'll start from the top left and move down:

We have companies (Customers), who in turn have employees (Requestors), who fax us Requests for Investigations.  The form tied to Requests is a sketch of the info they send.  The target is usually a Doctor (who often have one or two aliases, as well as one or two license numbers), but is also sometimes an Entity (business/company/whatev).  In addition, on the request form the specify the counties to search in.  Each county can have either zero or many results, as you can see from the table tied to Results.  Also, every result has costs that must be logged (one that we charge them and the actual cost of doing the search for us).

With that in mind, do you have any thoughts as to the titles / relationships of the tables?

I've gotta walk someone to her car real quick and I'll be back to post a list of tables I've been thinking about, working on.

Really LSM, you don't know how much I appreciate the help.  I really need to just sit down with someone who has experience in this.  I really want to keep this contract and do it right so they can take on more customers and make a profit.

process.jpg
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
Well ... a Company actually makes this Request. A Requestor would simply work for a Company (that is, you wouldn't expect payment for your services from the Requestor, but instead from the Company). So you'd need a Company table, and a Requestor table:

tCompany
------------------
lCompanyID
sCompanyName
etc etc

tRequestor (or tEmployee, or whatever)
----------------------
lRequestorID
lCompanyId
sFirstName
sLastName
etc etc

As to Requests, a Company will send you a Request for an Investigation. This Investigation will entail a Subject, which may be a Company or Individual:

tInvestigation
------------------
lInvestigationID
lCompanyID
sInvDescription
dStartDate
etc etc

tLocations
------------------
lLocationID
sLocDesc
etc etc

tInvestigationLocations
----------------------------------
lInvLocId
lInvID
lLocationID

You'll also need to store info on the person/business you're investigating. This is where the difficulty could come in, since you're dealing with different "entities". You need to store different information based on the "type" of subject you're dealing with. You can do this several ways; here's one method you could use, which is a "master/detail" sort of setup, where you store details in a "name:value" sort of setup ... for example, if this were a Doctor, you might store their various aliases in the Detail table, along with their license numbers, etc.

tSubjects
----------------------------
lSubjectID
sSubjecttype (person, business, etc)
sSubjectName

tSubjectDetails
-------------------------------
lSubDetailsID
lSubID
sDetailType
sDetailData
sDetailComments

tInvSubjects
-------------------------------
lInvSubID
lInvID
lSubID

As far as results:

lResults
-------------------
lResultID
lInvID
lLocationID (if you need to tie the results to a specific location)
sDescription
etc etc


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now