help for creating relationship between two tables

[zhining]

hi, all, i am access beginner, the question i am asking may be simple and silly, but please help me out.
i created two tables, but i don't know what kind of relationship i should create.
in the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do? thanks a lot.

 

[Evon]

For just two tables, a one to many relationship is always the best option, at least initially. In fact, you need to ask yourself if you really need two tables. Which begs the question: when are two tables required?

The answer should lie in knowing what we really want to achieve. Then, the tables required to achieve this will become more apparent.

So, do you want one test to have many different ways of setting up? (One test to many setups). Or one setup can have many different tests (also one to many) but that should help you decide which side to put the one and which to put the many. In other words which will be your main table no matter how many more tables you might add as time goes on.

To get both tables to work together, you could use one of two options:
1. Make one a sub-form of the other. In other words the many table would be the sub form. So for every record of the One side you could have many records in the sub form.So you could see how many different setups was done for one test over many days, months, years for eg.
2. You could use them both in a query and make one table from that query.

 

[Pat Hartman]

Welcome Aboard
I can't tell from your description which is the 1-side table and which is the many-side table so you need to clarify that first. Once you do, you would put the PK of the 1-side table into a FK field on the many-side. For example -People and autos.
tblPeople
PersonID (autonumber primary key)
FirstName
LastName
etc.
tblAutos
AutoID (autonumber primary key)
PersonID (foreign key to tblPeople)
Make
Model
etc.

Then your interface would use a main form with a subform with tblPeople being the RecordSource for the main form and tblAutos being the RecordSource for the subform. As long as the master/child links are set correctly, Access will automatically populate PersonID in tblAutos with the ID of the parent record.

An alternative is to use two separate main forms. The first will be used to maintain the people data. The second will be to maintain the auto data. In this case, you would have a combobox that would be used to choose the person you wanted to associate this auto with and that is how the association would be made. For my example, you would use the second scenario if it were possible for autos to be in your inventory without being assigned to a person.

 

[Evon]

Another possibility working with two forms is that it is possible to open the second form from within the first by using a button.

 

[zhining]

thanks for you guys' clear explanation. i got part of what you guys said.
let me list the tables i created.
tbl_Patient
FName
LName
MRN#
tbl_Test
Test1 (just example, i did not list the name of test)
Test2
Test3
tbl_testsetting
stimsite
recsite
one patient can have many different tests, one test can be used for many patients, so tbl_patient and tbl_test is many-to-many relationship, then i need junction table to join these two tables.
my confusion falls on tbl_test and tbl_setting
each patient can have many tests, but for certain test, patient can only have one setting, namely, the setting parameter won't be change during the whole test in order to keep the test accurate. then how should i create relationship between them?
eventually i would like to see which patient has which test, and what setting about that test.
i hope i explain my purpose clearly, my question is i don't know what kind of relationship i should create either between tbl_patient and tbl_setting, or tbl_patient and tbl_setting.
thanks again.

 

[zhining]

Welcome Aboard
I can't tell from your description which is the 1-side table and which is the many-side table so you need to clarify that first. Once you do, you would put the PK of the 1-side table into a FK field on the many-side. For example -People and autos.
tblPeople
PersonID (autonumber primary key)
FirstName
LastName
etc.
tblAutos
AutoID (autonumber primary key)
PersonID (foreign key to tblPeople)
Make
Model
etc.

Then your interface would use a main form with a subform with tblPeople being the RecordSource for the main form and tblAutos being the RecordSource for the subform. As long as the master/child links are set correctly, Access will automatically populate PersonID in tblAutos with the ID of the parent record.

An alternative is to use two separate main forms. The first will be used to maintain the people data. The second will be to maintain the auto data. In this case, you would have a combobox that would be used to choose the person you wanted to associate this auto with and that is how the association would be made. For my example, you would use the second scenario if it were possible for autos to be in your inventory without being assigned to a person.

regarding to the combobox you mentioned above, i know it will show all patients in the tbl_patient, is there any way to do the combox that only shows the patients who have test on current date? say, i have 5 pt in for test, i only want to list thest patients. thanks a lot.

 

[Evon]

You could add a time/date field in the patients table. In that way each patients record would have a date associated with the record. It should then be easy to filter the records that feed the combo by using the function: =Date(Now)

Regarding the relationship issue, it seems to me that everything that happens and that will happen will revolve around the Patient. Therefore if I was building a database to cater to patients, then all the other tables would be on the many side, if related directly to the patient. In that way I am confident that I am building a very sturdy reliable database.