Wasteful fields

[hatcat]

I have a table of countries.

I have another table whose fields contain excluded countries. The
number of countries that can be excluded is arbitrary, which means that
I have a table that looks like this:

Visitor
Excluded Country 1
Excluded Country 2
Excluded Country 3
Excluded Country 4
Excluded Country 5
....
Excluded Country 25

I'm hoping I don't have to go above 25, but that isn't unreasonable.
Also since most visitors only have one or two exclusions, there seems
to be a lot of waste, and I need a null entry in the countries table.

I would rather have a single field with an arbitrarily sized set of
countries, but I want to ensure that the countries chosen are from the
countries table. Up to now I have been using pick-lists. Any ideas
how I can create a set of countries in a single field using a
pick-list?

Cheers,
hatcat

[Allen Browne]

So, one visitor can have many exclusions?
Sounds like a one-to-many relationship.

Country table (one record for each country)
CountryID Text Name of country. Primary key.

Person table (one record for each visitor)
PersonID AutoNumber primary key
Surname Text
Firstname Text
...

PersonCountry table (one record for each exclusion for each person):
PersonID Number Relates to Person.PersonID
CountryID Text Relates to Country.CountryID

So if Fred Jones has 3 exclusions, there will be 3 records for him in this
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hatcat" <gu**********@hatcat.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

>I have a table of countries.

I have another table whose fields contain excluded countries. The
number of countries that can be excluded is arbitrary, which means that
I have a table that looks like this:

Visitor
Excluded Country 1
Excluded Country 2
Excluded Country 3
Excluded Country 4
Excluded Country 5
...
Excluded Country 25

I'm hoping I don't have to go above 25, but that isn't unreasonable.
Also since most visitors only have one or two exclusions, there seems
to be a lot of waste, and I need a null entry in the countries table.

I would rather have a single field with an arbitrarily sized set of
countries, but I want to ensure that the countries chosen are from the
countries table. Up to now I have been using pick-lists. Any ideas
how I can create a set of countries in a single field using a
pick-list?

Cheers,
hatcat