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.