Multi Valued field cannot be used in a group by clause

[Groovy Chick]

Hi, can someone please help me? I have an access 2010 database query. I have check box fields to tick to use for my controlled document linked references. I have added these fields and their .value fields to my query. I have added criteria to each of the vale fields (7 fields in all) so that when I run the query I can add in the document reference that I'm looking for and the query will return all records that have that document checked as relevant. The query runs but brings back multiple lines of the same document. When I try to use the group button I get an error message saying multi valued field cannot be used in a group clause. How can I do this? Many thanks.

 

[mole999]

can you post the query

 

[Groovy Chick]

Hi, sorry that I don't know how to post the query. This is the criteria that I have used

Like "*" & [Choose Document CPR Level 0 Reference] & "*" And Like "*" & [Choose Document CPR Level 0 Reference] & "*"

.
I have the linked document field and the .value field as well for each document type. For example for Level 0 linked documents I've got the Linked Level 0 Document field and the .value field.

 

[Joe4]

Multi-valued fields can be problematic and really should be avoided altogether.
They actually do not follow rules of normalization, and other more robust database programs (like SQL) do not allow them.

 

[Groovy Chick]

Thanks. So what do you suggest that I use instead?

 

[Joe4]

You would enter multiple records into your table, instead of multi-value records.
So, if you had a record that had 4 values in the multi-value field, you should have 4 separate records instead.