Best structure

[Mary]

Hello Access Experts,
I have an Excel file client list (with client#'s) with the same client
having different interviews (3 types) at different dates. Though the
interview type varies, the questions are identical. My goal is to capture
the clients progress over time, based on the numeric answers to each
interview. There are five types of questions, 10 questions per type. How
should I design the database most efficiently? Thank you in advance. Happy
St. Patrick's Day,
mary

 

[Tom Ellison]

Dear Mary:

First, think of every "attribute" you have. An attribute is a name, a date,
a question, and answer. These must be collected into objects called tables.
Each table should have a set of attributes that are strongly connected to
one another in a one-to-one fashion. A client has a name, an address, a
home phone number, etc.

Each time a client is interviewed, the date and time of that interview,
along with whatever uniquely identifies the client would be recorded as an
instance of a Client/Interview. If multiple clients will be receive the
same interview, there would be a table defining the interview. Another
table would contain a list of the questions in the interview. Now,
Interview A/Question 5 has a specific definition. Another table would
record the answers. Interview A/Question 5/John Doe contains the answer of
one client to a specific question in a specific interview.

Do you have interviewers? Will you be recording them? Let's assume for a
moment you do. Where would you record which interviewer conducted an
interview? Well, an interview is identified as a specific set of questions
asked of a specific client, right? You will have a table with one row per
interview, keyed by client and a defined set of questions. Once you know
which identifying keys uniquely identify where an attribute is to be
recorded, then you know into which table this information is placed.

It's an art. Experience will refine your ability to make decisions on how
this is done.

You will probably have a surprisingly large number of tables when you do
this. Don't be dismayed. That is a good thing! I have never seen someone
who created too many tables for their problem. All to often, there are not
enough tables to properly model the application.

Tom Ellison

 

[Mary]

Thank you Tom,
No interviewers. So, just a client table, interview type table, 5 general
subject tables with the relevant questions in each? THe raw file is in
Excel. Once I have all these tables, how do I periodically pull Excel file in
each quarter? In other words, should there be a master list with all the
elements, and would that update the smaller tables after import? THANK YOU
M

 

[Duane Hookom]

You might want to check the sample survey as well as the employee evaluation
at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

These are normalize (not spreadsheets) so they provide the greatest
efficiency.

 

[Tom Ellison]

Dear Mary:

I used "interviewer" to promote the explanation of a principle.

Perhaps you should decide now which way you want to go.

1. You can make this a learning experience, and gain the skills needed to
do this yourself while you get some advice.

2. You can try to have someone do it for you without understanding it.

3. You may be able to use a generic canned format such as Duane suggested.

If you want to go with alternative #1, let me know. That's the one I'm
promoting.

If you go with one of the other two, you will not know initially whether it
is adequate for your needs. If you lack the skills to design a database
yourself, you almost certainly lack the skills to evaluate whether one is
sufficient for your needs. The two skills are pretty much the same.

I expect that making up your mind which is best will be the next step. Let
me know if you need some assistance with learning a rather significant new
skill. I will gladly review your attempts and suggest corrections and
alternatives along the way.

Tom Ellison