I don't know if "link" is the appropriate word to use here but, I have a roster table with all personal data of people. I want to create a physical fitness table with the scores of a PT test. Now I want to make the PT test scores to the right person, do i just put their name in the fitness table so when a query happens I can join the tables in the query? Is there a better way to make the table?
Can I link Tables?
- Thread starter Valentine
- Start date
- Local time
- Today, 09:28
- Joined
- Feb 28, 2001
- Messages
- 30,920
You are describing the establishment of a relation between two tables. The USUAL way this is done is that one table is a "parent" or "independent" table and another is a "child" or "dependent" table. The parent table usually has an ID number. So your roster table (of people) would probably have people in it even if they hadn't gotten a PT score yet. It would have a person ID, often just like a customer number or patient number. Then in your scores, you would copy the person's ID number to a field in the score table. If there are multiple PT tests for the same person, that person's ID would link the individual scores back to the person. This way, the selections in the dependent table depend on the ID from the independent table. When you have this, the person ID is often referred to as the PRIMARY KEY (or PK) of the parent table and is a FOREIGN KEY (or FK) of the child table. Access supports this interpretation directly.
This is an example of NORMALIZATION and would be a good topic to research before going too far. Access can EASILY do this for you but it helps for you to have an idea of what you are doing and why. If you use the forum SEARCH, you can look for NORMALIZATION because this is a database forum. If you did a web search, look specifically for DATABASE NORMALIZATION because other disciplines also use the word NORMALIZATION to describe conditions, actions, or situation - e.g. diplomatic, mathematical, chemical, medicinal, etc. If you do that web search, start your reading with web sites in the .EDU domain at first - they are LESS likely to want to sell you anything. However, there are some good .COM (commercial) sites with tutorials and/or detailed explanations.
This is an example of NORMALIZATION and would be a good topic to research before going too far. Access can EASILY do this for you but it helps for you to have an idea of what you are doing and why. If you use the forum SEARCH, you can look for NORMALIZATION because this is a database forum. If you did a web search, look specifically for DATABASE NORMALIZATION because other disciplines also use the word NORMALIZATION to describe conditions, actions, or situation - e.g. diplomatic, mathematical, chemical, medicinal, etc. If you do that web search, start your reading with web sites in the .EDU domain at first - they are LESS likely to want to sell you anything. However, there are some good .COM (commercial) sites with tutorials and/or detailed explanations.
GPGeorge
George Hepworth
- Local time
- Today, 07:28
- Joined
- Nov 25, 2004
- Messages
- 3,214
Re-read Post #4, please.
Ok so to make a joined table called AcftToRoster Does it have to include everything? I attached the 2 tables I created. I made a subform for the PT test using PT test table as the record source. When I input the scores and save the form, the PT test table brings in the ID from the roster table instead of SID.
Attachments
GPGeorge
George Hepworth
- Local time
- Today, 07:28
- Joined
- Nov 25, 2004
- Messages
- 3,214
The most frequently used term is "Junction Table", inasmuch as ALL tables in a relationship are technically "joined" to one another.
That said, as Pay pointed out, you strictly need only two fields in a Junction Table, the Foreign Keys for the Primary Keys in the other two tables. In this case, the PK for PT Test and the PK for Soldiers.
That said, usually you will need to include additional fields, such as the date and time the soldier took that test and the test score earned by that soldier on that PT test on that date.
That said, as Pay pointed out, you strictly need only two fields in a Junction Table, the Foreign Keys for the Primary Keys in the other two tables. In this case, the PK for PT Test and the PK for Soldiers.
That said, usually you will need to include additional fields, such as the date and time the soldier took that test and the test score earned by that soldier on that PT test on that date.
mike60smart
Registered User.
- Local time
- Today, 15:28
- Joined
- Aug 6, 2017
- Messages
- 2,241
Your PTTest table contains all of the Activities together with the Scores for each Activity.
This structure is all wrong.
You should create a table that lists all of the Activities.
tblActivities
-ActivityID - PK - Autonumber
-Activity - Text
tblPTTest
-PTTestID - PK - Autonumber
-SoldierID - FK - LongInterger - Number (Linked to tblSolderDetails on PK)
-ActivityID - LongInteger - Number - FK (Linked to PK from tblActivities)
-Score
Your other table for Soldier Details needs to be normalised.
It should only contain field that relate to the Soldiers Details.
Fields like the following should be in related tables:-
Rank - you need a table to record the history of Promotions
WorkLocation - you need a table to record a history of Postings
