Lookup field to retrieve multiple values per record

[ShannonM]

I am attempting to get a lookup field to input multiple values for a single record.

I have a main table that contains information about species (each record represents a single species). Each species has a distribution. The distribution is selected from a Country field (which is a series of checkboxes).

I have another table (Country table) that lists all the possible country names and their abbreviations. The main table & country table are linked by a unique country ID#.

I want to have a lookup field in the main table that will have the country abbrevations (data from Country table) in it based on the countries selected in the main table. Everything is working fine - except only one abbreviation is showing in lookup field, despite the fact that several countries are selected for a single record.

How do I get the multiple abbreviations to appear in that lookup field?

To summarize:

Main Table:

CountryID

Country Name (Check-box set based on a value list, with values from Country::Country Name)

Country Abbreviation (Lookup field based on relationship between CountryID)

Species Name

Country Table:

CountryID

Country Name

Country Abbreviation

Any help would be much appreciated, I am really stumped. Would using an auto-enter calc field for the Country abbreviation in the Main table be a better idea? If yes, how would I go about that?

Thanks in advance, Shannon

[comment]

Isn't this the same as here?

http://fmforums.com/forum/showtopic.php?tid/187863

In any case, you cannot lookup multiple values into a single field. You need either an unstored calculation field, or a text field with auto-entered calculation (triggered by the checkbox field).

[Søren Dyhr]

Further more isn't national ID's likely to fluctate or change over time, so it can easily be referenced data instead ... only when a snapshot of a certain say pricelevel needs to get tracked in an invoice is the cause justified ... otherwise are lookups relationally unhealthy!

--sd