How should I manage this data with relationships?

[pixelkicker]

Hello everyone, thanks for taking the time to read my question!

Scenario (access 2010):

Two tables, tblPersonnel and tblFitness.

tblPersonnel contains basic personnel information. The fields of concern are:

PID (unique personnel ID, primary key)
Last Name
First Name
Rank
Squadron
​

tblFitness is managed by a different user who only needs to know about fitness tracking of the personnel which is one reason we have seperated this data into two tables. The fields are:

PID (should correspond to their personnel record)
Last Test
Due Date
Score
Current (check box)
Exempt (yes/no)
Fitness Level
​

So my question is wouldn't you do a one-to-one relationship here since there is only one personnel record for each person (PID) and only one fitness record for each person? Also, I'm unsure how I should technically DO this. Besides setting the relationship how would the table be updated from a form? Would the fitness monitor be able to select a name from a drop down and add fitness data that way? Would I start by "pulling" over the data for each PID into the empty fitness tbl? Any guidance is appreciated! Thanks!!

 

[strive4peace]

btw, a great way to manage security, especially since you have sensitive fields in their own table(s) is to put them in their own back-end ... in a directory where only the group that should see the data has network privileges to see it. Network security works great with Access. When users open a form they don't have rights to see data for, it is blank.

you will, of course, have to ensure referential integrity yourself as only simple relationships, which are really just visual lines for the user, can be created on tables in different files

 

[pixelkicker]

make a subform the same way you would make a regular form

keep in mind which fields you will use to link and make sure they are IN controls on the form. Your link field is PID. You can set Visible = No

once this form is done, save it and close it.

Now open the form to be the mainform in design view.

Drag the (sub)form you just made from the navigation pane onto the (main)form. Ensure that LinkChildFields and LinkMasterFields (DATA tab of Property Sheet) are set to PID for the subform control.

First click on a subform control selects the container for the form, not the form itself. this would be where it is, how big it is, etc

Second click on a subform control gets you INTO the control ... to the form it contains.

This worked!! Thank you so much

This board has been so nice and helpful! Ya'll rock!

 

[strive4peace]

you're welcome happy to help