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!!

 

[skywriter]

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!!

Sounds more like a one to many. You say it is a one to one, but the structure sounds more like you would have more than one record. Last Test, Score ? You only want one record of these, that being the current record and not a set of historical records?

 

[pixelkicker]

Sounds more like a one to many. You say it is a one to one, but the structure sounds more like you would have more than one record. Last Test, Score ? You only want one record of these, that being the current record and not a set of historical records?

Thanks for taking the time to reply, I see your point. However, we are only concerned with the most recent test score in this database. It is for a supervisor's "heads up" information only. The actual test scores are logged and stored in an external web database that we have no administrative control over. This will be a one-to-one because it is one personnel record to one fitness record. How should I handle the data entry? What about the relationships? I'm new to correlating data like this. Thanks!

 

[Joe4]

Might there be any records in tblPersonnel without matching records in tblFitness?
If not, so that both tables have EXACTLY the same number of records (one for each PID in both tables), you could get away with putting everything in one table, and then you don't even need to worry about relationships.

 

[pixelkicker]

Might there be any records in tblPersonnel without matching records in tblFitness?
If not, so that both tables have EXACTLY the same number of records (one for each PID in both tables), you could get away with putting everything in one table, and then you don't even need to worry about relationships.

Unfortunately the person managing the fitness table cannot have access to the tblPersonnel. That is why we have seperated them. There is also only some records that will have fitness records. Thanks for the response though.

 

[Joe4]

Unfortunately the person managing the fitness table cannot have access to the tblPersonnel. That is why we have seperated them. There is also only some records that will have fitness records.

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!!

OK. Firstly, I would make the PID key Indexed, and not allowing duplicates in either table. That will prevent a user from accidentally creating a record for a person that already exists.

Then, I would probably approach this with a Form-SubForm perspective.
First, make a Form from your tblPersonnel table, showing ONLY the fields you want the person updating the Fitness data to see (maybe only the PID field and name fields?).
Then, create a SubForm of your Fitness data, linking it on the Form to tblPersonnel on your PID field.

You can include a Combo Box on your Form to search for the record you want to update.
There is even a YouTube tutorial for it here: Access 2010: Find a record on a form by selecting a value from a list - YouTube

 

[pixelkicker]

OK. Firstly, I would make the PID key Indexed, and not allowing duplicates in either table. That will prevent a user from accidentally creating a record for a person that already exists.

Then, I would probably approach this with a Form-SubForm perspective.
First, make a Form from your tblPersonnel table, showing ONLY the fields you want the person updating the Fitness data to see (maybe only the PID field and name fields?).
Then, create a SubForm of your Fitness data, linking it on the Form to tblPersonnel on your PID field.

You can include a Combo Box on your Form to search for the record you want to update.
There is even a YouTube tutorial for it here: Access 2010: Find a record on a form by selecting a value from a list - YouTube

Sir, you are a gentleman and a scholar. Thank you! I will look into this now. I've never done a form/subform. Sounds fun

 

[Joe4]

Your welcome!

There is some good info out there on Subforms too, like this one for instance (don't worry that it mentions one-to-many, it should still apply for you): Create a form that contains a subform (a one-to-many form) - Access

 

[pixelkicker]

Your welcome!

There is some good info out there on Subforms too, like this one for instance (don't worry that it mentions one-to-many, it should still apply for you): Create a form that contains a subform (a one-to-many form) - Access

Well I must be missing something. I tried to setup the subform using the tutorial you sent but it doesn't ask me if I want to make it sub-forum (I don't get that prompt because it's not a one-many relationship).

I've tried hacking it together by creating a form for the personnel table and adding fitness fields and it doesn't seem to update. I also tried it the other way around and it's not working. Should I be pre-populating the fitness tbl with all the PIDs and Names first? That doesn't seem clean...

I'm lost. Thanks for any guidance.

 

[strive4peace]

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.