Data Entry Form Design

[Garnet]

Database is for workshop registration, one registrant can choose from
multiple workshops divided into Morning and Afternoon.

I've built 2 tables, Registrants and Workshops.

Registrant table includes Name, Address, City, phone, etc. I'm using Auto
Number to assign unique registrant's ID's and have designated these as my
Primary Key

Workshops table is constructed this way:

Registrant ID (foreign key)

Workshop_name_ 1_AM
(text; y/n)
workshop_name_2_ AM
(text; y/n)
Workshop_name_3_ AM
(text; y/n)
Workshop_name_4_PM
(text; y/n)
Workshop_name_5_PM
(text; y/n)

The workshop choice is built as a subform and I want to show the workshop
name then a y/n selection after each workshop name. In fact, if I could build
the form with a heading of AM with the AM choices available and PM with the
PM choices available, that would be ideal.

Any ideas would be appreciated

Thanks

 

[mscertified]

I would define the list of possible workshops in a Workshop table.
Then have a separate WorkshopsRegistered table to record the links between
the Registrants and the Workshops.
So you will show the workshop choices from the Workshop table but record the
selections in the WorkshopsRegistered table.

-Dorian

 

[Garnet]

Ok, a table that builds itself after the choices have been made? If that's so
I'm afraid I wouldn't know how to build a table like that. Could you describe?

Thanks.

 

[John W. Vinson]

Ok, a table that builds itself after the choices have been made? If that's so
I'm afraid I wouldn't know how to build a table like that. Could you describe?

No. You build the table FIRST.

A Registrant can attend many workshops - so you can't put a single
workshop field in the Registrants table.

A Workshop can be attended by many registrants - so you can't put a
single Registrant field in the Workshops table.

Like *ANY* many to many relationship, the solution is to create a
third table to record the fact that *this* registrant is registered
for *this* workshop. Rather than adding a field (yes/no or otherwise)
per registration, you add a NEW RECORD for each registration.

Registrants
RegistrantID <Primary Key>
LastName
FirstName
<other bio and contact data>

Workshops
WorkshopID <Primary Key>
WorkshopName
DateScheduled
<other info about the workshop>

Registration
RegistrantID <link to Registrants>
WorkshopID <link to Workshops>

The Form would be based on Registrants, with a Subform based on
Registration; on the subform you'ld have a combo box based on
Workshops, allowing the choice of any workshop. If a registrant wants
to sign up for several, you'ld just add several rows in the subform.

You can also use a Form based on Workshops, with a subform again based
on Registration; this time you'ld use a combo box based on Registrants
to select people to assign to the workshop, rather than workshops to
assign to a person. You might want both forms!

John W. Vinson [MVP]