Can I use a field with the property "allows multiple value"as a Primarykey to enforce a relationship

[yellowgelb]

I am working on a database for wildlife data. I have a main table called tblbasicdata, which contains a lot of parameters (as date, age, location...) to each sample. Many of these parameters are actually extra tables connected to tblbasicdata as a combo box with a table source. They are all connected by one-to-many relationships. Now, I would like to enforce a relationship of the field BasHowSexed with the related table tblHowSexed. The field BasHowSexed has the property Allow Multiple Values set as yes, since the scientists use different options to sex one single animal.
But Access is not setting up a relationship and tells me that the tblbasicdata is violating the rules. I checked everything and can't find a bug. Is it possible that I can't use fields with the property "allow multiple values" as a primary key? If this is the case how could I fix the problem?

Thanks a lot for your help!!

[June7]

That is correct. Multi-value fields cannot be primary key. Why would you want to? I don't understand why you have this issue.

By the way, I NEVER use multi-value fields.

[yellowgelb]

Hi,
Thanks for the quick reply.
I will try to explain it. In the field BasHowSexed I need to have the option to select more than one type of sexing, because that's what they do when they work outside in the field. I have 12 option how they can sex the birds and more or less all combination are possible. In the database exist two forms. One for the data entry and one to filter the data. In my main database tblbasicdata I've got more than 10000 samples and about 20 rows with the different results and parameters (as age, howaged, species, location, sex and so on). One of them is the parameter HowSexed. If I would use a general primary key I don't have the possibility to choose more than one item (sexing parameter) for each sample, do I? The only other option which comes to my mind, if not using a multiple-value field, is to set up all possible combinations in the table tblHowSexed.

Do you understand now why wanted to use the multiple-value-field? Do you have any other recommendation how to deal with that?

[June7]

I understand multi-value field to save multiple sexing methods utilized for each sample. I don't understand why you want it to be a primary key. The multi-value field would be a foreign key field, not primary key. Is this field in tblbasicdata?

You said 20 rows, did you mean fields?

If you want to provide db for analysis, follow instructions at bottom of my post.

[yellowgelb]

You are right the multi-value field would be a foreign key field and yes 20 rows are actually the columns. And sure the field BasHowSexed is a field in the tblbasicdata, since I was trying to enforce a relationship between this the table tblbasicdata and tblHowSexed. But now I wondering if I actually need to enforce this relationship, because it is only a descriptive selection and I have set the properties in the tblbasicdata for this field "limited to list" to yes. I don't allow any editing.

I attached the database. Happy about any recommendations! Please be aware that the names in the database are slightly different, but I think it's self-explanatory. Thanks!

[June7]

I was able to establish relationship between tblHowSexed and tblBasicData.

[yellowgelb]

Hi,
the whole process was helpful in terms that I figured out the fault I made. Now I can set up a relationship between this both tables.
But actually I would rather avoid using a look up field for the field HowSexed. But I still need to have the possibility to combine the 11 different ways how people can sex the birds in one field. How can I do that? Because actually you shouldn't have two information in one field? I am a bit confused regarding this issue and could need a good advice.

Thanks a lot for your help!!

[June7]

Save the data in normalized structure then manipulate as needed for output. Review this article for method to display related data from multiple records as a single value http://allenbrowne.com/func-concat.html