Multiple Fields Index Problem

[Guest]

I use Access 2000.

Each year we have an Annual Trade Show. I have a Meetings Table and Form.
Each Exhibitor can have up to 3 booths. These are some of the fields I have:
MeetingYear
Booth1
Booth2
Booth3
None of the values for the 3 booths should be duplicated within the same year:
Record #1 2007 105 105 or

Record #2 2007 107 106 107 or

Record #3 2007 106
These sets of data should be prohibited.

Howeever these values should be allowed:
Record #1 2004 105
Record #75 2005 105
Record #193 2007 105

I followed the directions for making a Unique Index for a combination of
fields but did not get the results I desired. Please advise me how I might
accomplish this. Thanks!

 

[Allen Browne]

Whenever you see repeating fields such as Booth1, Booth2, ..., it *always*
means you are creating a spreadsheet and you do not have a relational
design. In a relational database, you would use multiple records in a
related table instead of multiple fields in the one table.

You will need at least these 3 tables:
Exhibitor table (one record for each exhibitor), with fields:
ExhibitorID primary key
ExhibitorName Text
...

Booth table (one record for each booth), with fields:
BoothID primary key
...

Assignment table (one record for each booth assigned to an exhibitor in a
year), with fields:
AssignmentID primary key
TheYear Number (which year this entry is for)
BoothID Relates to Booth.BoothID (which booth this entry is
for)
ExhibitorID Relates to Exhibitor.ExhibitorID (who this booth is
assigned to in this year.)

If an exhibitor has 3 booths for 2006, they will have 3 records in this
table.

Now, to create the compound index to ensure the booth is not assigned 2ce in
the same year:
1. In table design view, click the Indexes button on the toolbar.
Access opens the Indexes dialog.

2. On a new row in the dialog, enter a name for this index, and select the
first field (The Year.) In the lower pane of the dialog, set the Unique
property to Yes.

3. On the next row, leave the index name blank, and enter the 2nd field
name. The blank name indicates this is part of the same index.

The dialog will now look something like this:
Index Name Field Name Sort Order
========= ========= ========
PrimaryKey AssignmentID Ascending
TheYearBoothID TheYear Ascending
BoothID Ascending

 

[Allen Browne]

Hi Dan

There are 2 stages to getting this right:
a) Setting up a normalized design.
b) Building a suitable interface.

It is not really possible to advise you on (b) before (a) is done, but in
general, you want to choose one table (such as Exhibitor) as the main form,
and place a related table (such as Assignment) in the subform.

The interface you are suggesting probably needs to be a form and subform (or
possibly several forms and subforms.) But before you can get that far, you
again have repeating fields (Seminar1, Seminar2, ...) That suggests you need
to go back to the design stage (a).

I may not have understood your needs propertly, but I take it that:
- one person can attend multiple seminars,
- each seminar is held in a booth at a particular date and time,
- you want to record who is enrolled for each seminar.

This would mean tables like this:

Person table (one record for each attender):
PersonID AutoNumber primary key (pk)
LastName Text
FirstName Text
BadgeName Text

Seminar table (one record for each seminar in a booth):
SeminarID AutoNumber pk
BoothID Foreign key (fk) to Booth.BoothID
StartTime Date/Time date and time when this seminar
starts.
Duration Number length of seminar in minutes

Enrol table (one record for each time a person enrols for a seminar)
PersonID fk to Person.PersonID
SeminarID fk to Seminar.SeminarID

Typically, to interface that you would have a main form for the Person, with
a subform for their enrollments. The subform would contains a combo box for
selecting the seminar to attend. It is in continuous view, so the person can
select multiple seminars to attend (one per row.)

There are probably lots of other factors involved here, but the core issue
is using related tables with many records instead of repeating fields in the
one table.