Multivalued fields in Access 2003

[Marcus]

Hi!
I´m working on a database that should give an overview of a companys
different distributors. The distributors are located in different countries.
So, I have two tables: one that contains a list of the countries (and some
info about the countries) and one that contains the different distributors.
In the distributor table I want to link the distributor to the country they
are located in. The problem is that one country can have more than one
distributor, AND one distributor can be responsible for more than one
country. I´ve read that in Access 2007 you can easily solve this by using a
listbox with a multivalue functions, a functions that is not available in
Access 2003. So, how should I do? Do I have to create a third table that does
the linking part, and in that case; how should I design that table?

//Marcus

 

[Allen Browne]

You need a 3rd table.

Presumably you already have:
- a Company table with a CompanyID
- a Country table, with a CountryID

The 3rd table will have fields:
- CompanyID relates to an entry in the Company table.
- CountryID relates to an entry in the Country table.

When you create a form for entering your companies, include a subform in
Continuous Form view. The subform will have a combo box for selecting the
countries where the company operates, one per line.

Quite honesty, that's the way I would design it in A2007 anyway.
Multi-valued fields are a serious hindrance if you know what you are doing.