need help linking tables

[Ali8781]

I am trying to populate many tables with information from one table. Our lab
uses Access 2007. We have an accession table with patient info (first and
last name, DOB, testes ordered, other misc info). We then have tables (and
forms) for each of our tests. I need to populate each of our test tables
with patient info: first and last name, DOB, date sample arrived. I know I
need to use the primary key from our accession table in the other tables but
am confused about where to go from there. I am learning access while trying
to do this, so please excuse me if I haven't used the correct terminology.

 

[KARL DEWEY]

Sounds like you need a Patients table with primary key, Tests table also
having a primary key. Then a junction table with a primary key and two
foreign keys. Foreign keys are matches to a primary key in another table.
You would set a one-to-many relationship from Patients to junction table.
Also set a one-to-many relationship from Tests to junction table. In creating
these relationships select options Referential Integerity and Cascade Update.
Then have a form/subform to show Patient to their test, using the Patient
primary key and junction table foreign key for the Master/Child links. The
subform would have a combo box to select the test from Tests table.
You may want to also have a form/subform for Tests/Patients.
The junction table would have additional fields for SampleDate, Results, etc.

 

[John W. Vinson]

I am trying to populate many tables with information from one table. Our lab
uses Access 2007. We have an accession table with patient info (first and
last name, DOB, testes ordered, other misc info). We then have tables (and
forms) for each of our tests.

I need to populate each of our test tables
with patient info: first and last name, DOB, date sample arrived.

No, you emphatically do NOT want to do so.

Relational databases use the "Grandmother's Pantry Principle" - or in this
case, we should say the "Nurses' Station Crash Cart Principle": "A place - ONE
place! - for everything, everything in its place." The patient name, DOB, etc.
should exist once, and once only, in the Patient table - AND NOPLACE ELSE.

If you need to see that information in conjunction with (say) test result
information, you would use a Query joining the tables, to pull the patient
last name from the patient table, and that patient's test result from the test
results table.

You will need more tables than you describe. Not knowing the nature of the
data you're managing I can't say offhand what tables, but just for example, to
store test results for patients you need at least FOUR tables:

Patients
PatientID
LastName
FirstName
<other biographical data>

Tests
TestID
TestName
<other information about the test as a thing in itself>

TestAdministered
TestAdminID <primary key>
TestID <what test was administered>
PatientID <to whom>
TestDate <when>
<other information about this specific administration of a test, e.g. who
analyzed the results>

TestResult
TestResultID <Primary Key>
TestAdminID <what test>
TestParameter <e.g. "Serum LDL", "Serum HDL", ...>
Units <"mg/dl", "mm Hg", "cm">
Result <numeric result>

I know I
need to use the primary key from our accession table in the other tables but
am confused about where to go from there. I am learning access while trying
to do this, so please excuse me if I haven't used the correct terminology.

You might want to look at some of the tutorials and other resources at:

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html