MultiValued Fields

[Joker2]

I'm trying to use Multi Valued fields for the first time. It appears to work perfectly when adding data but now I'm having trouble when trying to create queries.

I have a table that lists parts & their cost. In another table the user would select the parts required in the multivalued field.

I now want to find out the cost of parts for each record but it appears to only take into account one value from the multivalued list. How can I make it sum up the costs for all parts selected?

 

[Norie]

I think you might have found one of the problems with multi-valued fields.

They can be useful but when you try to do anything 'complicated' with them they just seem to cause problems.

In this case I think you might need to use code to do what you want.

 

[xenou]

Maybe this will help? There is an example of using aggregate functions in the QBE grid and a SQL statement:

http://office.microsoft.com/en-us/a...valued-fields-in-queries-HA010149297.aspx#BM6

 

[SydneyGeek]

Unless you are connecting your database to Sharepoint, avoid multi-value fields.

They were set up specifically to handle the Sharepoint List features. Outside of that they should be avoided because (1) they are *not* backwards compatible and (2) you have data issues like the ones you are describing.

You're much better off creating a related table and entering the multiple items there, one per row, as many rows as you need.

Denis