Realating Tables

[Brian T]

I am trying to set up a database to tracking awards earned by personnel.
Many people my earn many awards and the awards can be earned by many people.
I have a table set up with the personnel and table with the awards. How do I
get the two working together?

 

[scubadiver]

tble_personnel

personnelID (PK)
Personnel_Name

tble_award
awardID (PK)
Award_Name

tble_junction
PersonnelID
awardID

in the relationships view show the three table and drag the ID primary key
from the main table to the junction table and make sure you have referential
integrity.

Use the form creation wizard to create a form and subform. Then for each
personnel you can choose any number of awards.

 

[Brian T]

I'll give it a try. Thanks

tble_personnel

personnelID (PK)
Personnel_Name

tble_award
awardID (PK)
Award_Name

tble_junction
PersonnelID
awardID

in the relationships view show the three table and drag the ID primary key
from the main table to the junction table and make sure you have referential
integrity.

Use the form creation wizard to create a form and subform. Then for each
personnel you can choose any number of awards.

 

[Brian T]

What do I use for a subform? I created a form with all of the awards
available with checkbox if they earned it and text box for the number of
times it was earned. Can I use that as a subform. If so how do I tie it to
the table with the awards?

 

[Evi]

You may have this back to front. Using Brian's proposed structure, your
WonIt checkbox should be in TblJunction.
That's where the person and the award are combined.
Your Main Form should be the Single form based on either TblPersonnel or
TblAward.

Lets say you base it on TblAward.
Open it in Design View and drag the TblJunction into the Details area of the
form.
The wizard will probably activate now and turn the table into a form, linked
by AwardID

You now need a way of having the PersonnelName instead of just seeing
PersonnelID.
Open your new subform in design View.
Add a Combo Box to the Details area of the form.
Base it on TblPersonnel.
Choose first PersonnelID then PersonnelName
Let the wizard hide PersonnelID (or hide it yourself by closing that column
up)
This means that the combo will have the Value of PersonnelID but will show
PersonnelName.
Choose Store the Value in This field and choose PersonnelID

If you want to add all the Awards and All the Personnel to the table and
then tick your WonIt box when they win that award, first, create a Unique
Index in TblJunction for AwardID and PersonnelID so that you don't list a
person with the same award twice. (details available if you need them)
Create the main form as before but this time, base the subform on a query to
which you will add all the fields from TblJunction and the PersonnelName
fields from TblPersonnel. (you won't need any fields from TblAward because
they are in your Main Form)

Create a query. Add to the query grid, AwardID from TblAwards and
PersonnelID from TblPersonnel

If there is a link between them, select this line and delete it. This will
ensure that all possible combinations of awards and personnel will be
listed.

Turn the query into an Append query.

Append the whole list to your TblJunction

If this is the method you are choosing, you won't need the combo box.
Everytime you add another person to your Person table or another award, you
just run the Append query again. Because of the Unique Index, previous
combinations of People and Awards wont' be added again, only the new
combinations.
Evi




You can now choose the person who has got an award using the combo.