Question Multiple entries to one database field?

[Supersmithy]

Hi, I'm very new to both this Forum and the world of Databases and I think this may be a really roundabout way of asking how to do a very simple thing, but here goes...

Basically, I'm compiling a table of funding sources and whilst all the fields are relatively straight forward, one thing I can't get my head around is how best to approach the matter of where the funding is available. For example, Funding A is available in America only, Funding B is available in America, Canada and Europe and Funding C is available Internationally etc (a huge number of possible options and infinite different combinations). How's best for me to lay this out because there will be hundreds of funding sources all with different availability criteria? If the field name is 'Countries funding is available', can a data type input be multiple entries?

Ultimately I'll want to be able to query show all funding sources available internationally or show those available to America and Canada only etc...and when I query on another field or show all, I'll want to see where each of the funding sources are available, listed out

Any easy solutions I'm not seeing?

 

[CJ_London]

If you are using 2007 or later you can use the multi values property (in table design, click on lookup, select combo box and set allow multiple values to yes) however you will find that it does have issues and most serious developers would not recommend using it.

The standard way to do what you require is to have 3 tables - funding, countries and links along the following lines.

Funding
FundingID (autonumber PK)
FundingName Text
...
...
Other fields as required

Countries
CountryID (autonumber PK)
CountryName Text

Links
FundingID (Long FK)
CountryID (Long FK)