Three tables: one for the scouts (1 row for each scout), one for the badges
(1 row for each badge) and an intersection table that resolves the
many-to-many between Scouts and Badges. The Primary Key for the intersection
table would be the combination of the primary key for the Scouts table and
the primary key for the Badges table. You could also put additional fields
such as Date Passed and Date Received in the intersection table.
The easiest way to maintain this information is through a form/subform
combination. Use a query based on the Scouts table as the recordsource for
the parent form. Use a query based on the intersection table as the
recordsource for the sub form, but rather than simply have the BadgeId as a
text box on that form, bind it to a combo box that uses a query based on the
Badges table as its RowSource. Use the LinkChildFields and LinkMasterFields
to connect the parent form to the sub form via the ScoutId.
