multiple unrelated tables

[thrombogirl]

Newbie question. I am looking for an efficient way to enter data in a
database that has one main reference table and multiple other tables that are
linked to that reference table, but not to each other:

Main reference table:
Patient ID = primary key
Age
Gender

Bloodwork Tables
Patient ID
Date of Test
Results

I have tried to make a form based on a query, but that doesn't seem to work
because the tables aren't all related (I am assuming that is the problem) and
I don't seem to be able to create multiple subforms for the main reference
table.

I would appreciate any advice about what I am doing wrong. Thank-you

 

[BruceM]

There should be just one Bloodwork table. I would give it its own primary
key, but you have the general idea in terms of structure. I expect you need
a main form based on tblPatient, which should contain Patient information,
which presumably would include FirstName, LastName, Phone, etc. Then you
make another form based on tblBloodwork. Make it a continuous form.

With the main form open in design view, open the toolbox and add a subform
control. You can use the wizard to automate the process, or you can add the
control, then set its Source Object to the name of the Bloodwork form. Its
Link Master and Link Child properties will be set to PatientID. If you
click the three dots next to either one Access will probably suggest the
correct choice.

Since you are a beginner you may find the following tutorial helpful:
http://allenbrowne.com/casu-22.html
There are also lots of links, including to some tutorials, here:
http://allenbrowne.com/links.html

 

[BruceM]

On another point, when you are having trouble with something you need to be
specific. That it "doesn't seem to work" is probably not enough to elicit a
specific, targeted response.

 

[thrombogirl]

Thank-you for your reply, Bruce. And you are right, I should have been more
specific. The reason I didn't put all of my different bloodwork tests in the
same table is because not every test was done for every patient on everyday
and in some cases, multiple different blood tests were done on the same
patient multiple times:

For example: John Smith may have had two platelet counts on one day, but
none of the other tests done while James Jones had no platelet counts, but a
creatinine and an INR done on two different days.

If I put all of the blood tests in the same table, I thought that would mean
I would be saving space in the table for an awful lot of zeros so I separated
them into tables according to category (Hematology, Chemistry, Coagulation,
etc).

Does that change your answer at all or have I missed your point?

 

[John Spencer]

Your structure should be more like

Main
PatientID
Age (Age is relative and changes over time, you would be better of
storing date of birth in most cases)
Gender
-- Other fields in this table would be specific to the patient such as
name, next of kin, etc.

PatientTests
PatientID
TestDate
TestTypeID
Result
SchedulingPhysicianID
-- Other fields in this table would pertain to this test for this patient

Tests
TestTypeID
TestName
-- Other fields in this table that pertain to the test type.

You would have one record in PatientTests for each test that a patient
had. So if the Patient had two platelet counts and no other tests there
would be two records in patientTests.





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[BruceM]

I will refer you to John Spencer's part of the thread, and will post further
replies there in order to avoid parallel threads.. He suggested the sort of
thing I would have suggested.

 

[BruceM]

To the OP, the idea with the Tests table, as I understand John's suggested
structure, is that it contains a listing of all available tests. Jumping
ahead a bit from the structure to the interface, the Patient record is the
main form, and PatientTests is a subform. There can be many tests for each
patient. There is a combo box or list box on the form, bound to the
TestTypeID field. The combo box Row Source is the Tests table. The idea is
that you would select the test (platelet, INR, etc.) from the combo box,
then move to another record for the next test result. These data can be
arranged in any way you choose if they are properly related to each other in
the first place.

Remember that a table contains information about one type of entity. The
Patient table contains Patient information, but it would not contain, say,
test information. Address is an attribute of a patient; platelet count is
not.
Also, a table contains all of the information about an entity. If it is a
PatientTests table, it contains information about all Patient Tests.

There is a caveat here in that one type of test may be so different from
another that it does not make sense to keep them together. You may have a
bloodwork table, a respiratory table, etc. Each one would be related
separately to the Patient table.

However, if this is a Bloodwork database, at least for now, you could call
the PatientTests table the Bloodwork table. Once that is working you could
expand the database if a future need arises by adding a Respiratory table
(or whatever). There are a lot of options, depending on your needs. A
medical database could be a fairly complex project.

I encourage you to check the links I sent in my first response.

 

[thrombogirl]

John and Bruce,

Thank-you very much! I had been trying to figure it out using a manual I
bought, but was not finding the answers I needed. I really appreciate your
help.