MultiValued Fields

Joker2

Board Regular
Joined
Mar 22, 2006
Messages
117
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?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,487
Messages
5,596,450
Members
414,068
Latest member
FAH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top