I am designing a database for a riding competition where one jockey may ride one or more horses in one or more of 6 classes.
The entries are made in advance of the day of the competition using an interactive Adobe Acrobat form with the rider entering his or her name, the horse's name and ticking one or more of the six class boxes which are then uploaded into the database.
I therefore have a CompetitorT table with a CompID field, a rider field, a Horse field, and six yes/no fields, one for each class. I also have a ClassT table with a ClassID field and Class text field describing the six classes.
I link the CompID to the ClassID using a many to many relationship with a CompXClassT table having just a CompXClassID and the CompID and the ClassID. So far so good.
The problem I have is filling the CompXClassT table. I can easily do it with 6 Append Queries, one for each Class, but would like to do it with just one Append Query or in vba.
The Class fields are called Cl1, Cl2, ... Cl6.
Can it be accomplished in just one query?
Alternatively, can it be accomplished behind the scenes in vba? If so, how?
Many thanks,
David