Can I link Tables? (1 Viewer)

[Valentine]

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?

 

[The_Doc_Man]

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.

 

[Valentine]

Thank you, yeah i tried to search but i was using link or words like that for my search and didn't get the answer i as looking for.

 

[Pat Hartman]

What you need to implement is a many-to-many relationship and this requires a third table. You have a person table and a PT test type table. The third table is often called a "junction" table because it joins two (sometimes more) other tables. This table would be PersonPTScores. It is the intersection of person and a test and it holds the score and the date of the test and possibly other data such as comments. Here is a sample that shows how a m-m relationship works. Keep in mind that the "junction" table is always the "child" in the relationship. You can look at the relationship from the perspective of the person where you would have a subform showing all the tests taken by that person or from the perspective of the test where you would have a subform showing all the people who took a particular test. In either case, the "subform" could be a popup form if that made more sense. The sample shows one view with a subform and the other with a popup. Both are correct. Use whichever makes sense to you.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Valentine]

Is there a one to many style. I have read that 1 to 1 is not proper its best to keep it all in the same table. This will be 1 soldier to many different attempts at the PT test with the soldiers ID being the linking field.

 

[GPGeorge]

Is there a one to many style. I have read that 1 to 1 is not proper its best to keep it all in the same table. This will be 1 soldier to many different attempts at the PT test with the soldiers ID being the linking field.

Re-read Post #4, please.

 

[Pat Hartman]

Thanks George.
@Valentine , perhaps you are thinking of only ONE type of PT test rather than many. You have "many" soldiers and I think you have "many" types of PT test. Perhaps you have different courses or different levels of a single course. Even if today, you have one and only one type of test and ONE training course, I would implement this as a m-m anyway. Think of it as defensive programming.

So the junction table contains many instances of the same test but it might also contain results from different tests. Usually, the junction table uses an autonumber as the PK and a unique index that includes the FK to tbl1 (soldier) and the FK to tbl2 (test) but in this case, you have the same person taking the same test multiple times so you need a third field in the unique index and that would be TestDate.

 

[Valentine]

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.

 

[GPGeorge]

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.

 

[mike60smart]

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.

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

 

[Pat Hartman]

Given all we know at this time, Mike's suggestion of how to implement the activities will be fine. However, if you typically do these as a set, you might want an additional table that groups the activities so you can model the activities separately or as a set like a triathlon. The hard-coded list you have currently is the worst solution since it is totally inflexible. Splitting out individual steps will make reporting easier and adding different tests also. But, having the activities totally independent could make unnecessary work since you would have to choose 10 separate items for each soldier rather than one "set". Using the "set" concept, when you chose course1, your code runs an append query that appends the 10 items for course1 to the PTResults table but if you choose course2, a different group of items gets added. Maybe the second group has a 5 mile run and a 2 mile swim.