Problems with linking/table relationships in Access 97

[Steve]

First off, I'm working on my first Access database, so I'm
obviously new to all of this. Here's the situation: I'm
making a database to hold some records for a hospital at
which I volunteer. The database will store patient
vitals, medications, etc. I tried to enter all of the
necessary fields into one table, but there were too many.
So, I decided to split the fields up - one table for
vitals, another for medications, and so on. I'm trying to
get it so that if you search for a patient by social
security number, you can get the full gammit of their data
(everything from each different table). Put another way,
I'm trying to get it so that the series of tables act as
one. I've tried using one to one relationships (putting
the patient social as the primary key in each table),
using separate forms and having the expression builder
just copy the social security number from a separate
table... the list goes on. I know I could just have the
user enter in the patient social number each time, but
that is obviously impractical, and I'm thinking there's a
way to avoid having to do that. Anyhow, that's what I'm
trying to do, so if anyone can just tell me how to do it,
I'd be incredibly grateful! Thanks!

 

[Larry Daugherty]

To get to the benefits of using Access you have to ascend a short but
steep learning curve. You are already wrestling with part of it. If you
plan to become productive with Access I suggest that you get your hands on
the book "Access [YourVersion] Step by Step". I also recommend that you
start lurking the microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted newsgroups.

You haven't detailed the complete purpose of your application. Are you
gathering info for statistical purposes, to print, provide a means for
hospital staff to record data, ??

Given what you've already described, you probably need three main tables
to begin, probably more later as you continue to extend your application.

The first table is Patient. tblPatient will have a unique Primary Key
(I almost always use an Autonumber for a PK) and will contain relatively
persistent information about this patient: SS or hospital assigned ID number
(people don't have to give their ss# to anyone but the SS Administration!),
date of birth, doctor name, notes and what ever else makes sense in your
application.

The second table would have to do with Vitals, maybe even all "medical
metrics". tblMetrics would have a Primary Key (autonumber), a long integer
Foreign Key, which is the primary key of this record in the parent table,
tblPatient [that's how the one-to-many relationship is linked]. It would
also have fields for the metric name, it's current value and a date/time
field for the date and time of the reading and a field for notes about this
reading at this time.

Note that the names of the various metric points could well go into a
special use table called a lookup table. Some entries in tblMetric might
be: BP Systolic, BP Diastolic, Pulse, temperature, etc. Don't confuse a
lookup table (a good thing) with a lookup field (a bad thing). Never use
lookup fields in your table designs. Do use lookup tables in your designs
to ease the burden of data entry, minimize data entry errors and to speed
data entry.

The third main table will be about Medications. tblMedication will be
similar to tblMetric in that it will have an autonumber primary key, a long
integer foreign key which is the primary key of the parent record in
tblPatient, Medication name (you'll probably also want a lookup table,
tblPharma or some such of your choosing), dosage, scheduled administration
times or what ever else makes sense to you. I almost always include a field
for notes about the current instance of what ever it is that this record is
about. Again, I don't know if you're trying to list medications prescribed
and the dose and schedule or the event of administering the medication. You
may want to do both or ??

Whew!

As you see, you don't want a one-to-one relationship but a couple of
one-to-many relationships.

The next big hurdle, of course, will be for you to create data entry
forms to manage your data. I won't go into that now because you've already
got a lot to digest. If you get your hands on the Step by Step book and its
CD you'll find the answers there.

Don't turn your application over to users if it would mean that they
have to get into the tables directly. If that's your plan you'd be kinder
to them to do the thing in Excel.

Don't ever let your users see the content of Autonumber fields. When
you do get around to designing forms and reports, either delete them from
the forms and reports or make them invisible. You don't care about the
value of an autonumber field either. If it makes a difference to you what
the value happens to be then you shouldn't be using an autonumber.

Trying to manage the above one-to-many relationships without forms and
subforms can be ugly so get into your reading as quickly as you can.

Lurk the newsgroups, read the books and post back when you have
questions.

hth