Building form

[F]

I'm new to Access and am struggling a little.

I am building a database which is to be used for event management. It
will store details of delegates in a 'Delegates' table and events in an
'Events' table.

The delegates table stores the usual surname, address, etc. and the
events form stores event names, dates, etc.

Both tables have associated forms.

The Delegates Form asks for a delegate's details and has a list box with
the MultiSelect property set to simple for choosing events (taken from
the events table) which (s)he wishes to attend. Alongside each event in
the list I need to be able to provide an input for how many tickets a
delegate wishes to buy for that event.

Is there an easy/uncomplicated way to achieve this and have each of the
events directly linked to its own 'number of tickets' input. The
solution would need to be able to cope with changes in the number of
events as the year progresses.

TIA

 

[Allen Browne]

You need a 3rd table, with fields like this:
DelegateID foreign key to Delegates.DelegateID
EventID foreign key to Events.EventID
Quantity number of tickets this delegate wants for this event
There could be other fields as well, such as DateBooked.

On your events form, create a subform bound to this 3rd table.
In the subform, you will have a combo box with Delegates as RowSource.
You can now enter as many rows as needed in the subform for all the
delegates, and sum the quantity for the event.

The 3rd table is known as a junction table, and this is the standard way to
resolve a many-to-many relationship into a pair of one-to-many's.