Basic design issue...

J

Jason

Hi everyone,
First the background
First time poster and relatively new to Access (have taken one class in
it and I'm a psychology researcher with SOME computer experience, but
I'm a far cry from a programmer/data manager type) so forgive the
potentially stupid question I'm about to ask.

I've been asked to design a database for a medical study I'm working on
and Access is what we have to use.

The primary problem I have right now is with the actual basic design of
it - most of the technical aspects of setting up tables/forms/etc. I
think I can do.

Essentially what we want is a single GIANT table (300 participants with
about 2500 variables each). Obviously that has to be broken down into
many smaller tables, which is fine.

The PROBLEM that I'm having is that each participant will have a single
ID# and I want that number to be the same across every table. For
forms, I'd like to have people select the participant by # at the top
of the form, and then be able to click a button below to bring up a
form allowing them to edit that record.

I know that probably sounds confusing, but an example would be, there
is say participant 003 and their contact info entered into the table.
I want to enter their weekly questionnaires in - but the problem is
when I scroll to participant 003 at the top, it only sees it as a part
of the contact info table. How can I "trick" Access into essentially
only having one primary key for records across 50-100 tables?

I use an update query so that when the info is entered for the first
time, it enters "placeholder" IDs in all the tables that match the ID
at the top. However Access does not like having one ID for multiple
tables. Perhaps I'm missing something obvious (I probably am), but how
can I have them select an ID at the top and then pull up the record
with a matching ID value in another table?

Thanks so much for the help and if that didn't make any sense, just let
me know and I'm happy to clarify. Like I said, I'm very new to Access
databases (I'm used to just keeping all data in SPSS) - so there could
be a simple solution I don't know about.
 
R

Roger Carlson

You haven't told us what the database is designed to track. The business
rules are an essential component of database design.

I suggest getting the book: "Database Design for Mere Mortals" by Michael
Hernandez. It will walk you through a simple process to designing any
database. Once you've done that, I have some tutorials that show you how to
implement his process. You can find them here:
http://www.rogersaccesslibrary.com/Tutorials.html

But get the book! If you're a researcher, you shouldn't mind doing some
research to do it right ;-)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
R

Roger Carlson

Oh, I forgot to mention:

You say something about a weekly questionaires. Duane Hookom has an
application available called "AtYourSurvey.mdb" which you might be able to
use for your application. It not only helps you design questionaires, but
you can enter the data into the database and report from it too. Give it a
look at:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
J

Jason

Hi Roger,
Thank you for the quick reply!
The database is tracking a wide variety of study variables and the
contact info associated with those participants - hence the reason it
is essentially one very large table rather than several. It is a
smoking cessation program so we are tracking things like withdrawl and
craving each morning, weekly depressive symptoms, motivation to quit,
impulsivity, etc.

Didn't mention that because I didn't think it was relavent, but it
appears I was wrong! Hope that helps.

I'll be checking out that book as soon as I can - thanks again!
 
J

John Vinson

Hi Roger,
Thank you for the quick reply!
The database is tracking a wide variety of study variables and the
contact info associated with those participants - hence the reason it
is essentially one very large table rather than several. It is a
smoking cessation program so we are tracking things like withdrawl and
craving each morning, weekly depressive symptoms, motivation to quit,
impulsivity, etc.

Didn't mention that because I didn't think it was relavent, but it
appears I was wrong! Hope that helps.

I'll be checking out that book as soon as I can - thanks again!

One big wide table is a VERY BAD IDEA (you're limited to 255 fields
just for starters).

Instead, consider the logical structure of your data: you have many
(2000?) "measurements" or "parameters" - e.g. "Craving each morning",
"impulsivity", and you have many Participants. I presume you'll be
collecting these measurements over time in repeated sessions, right? A
better design would be a "tall thin" design like:

Participants
ParticipantID
LastName
FirstName
<other bio/contact data>

Sessions
SessionID
SessionDate
ParticipantID

Measurements
MeasurementID
Measurement <<<< this table will have ~2000 rows with values
like "craving each morning"

Results
SessionID <<< link to Sessions and thence to Participants
MeasurementID <<< what is being measured
Result <<< what were the results

No table has more than a few fields; each table has as many records as
needed to record the data.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top