Multiple value field is not valid in query (1 Viewer)

rc-alex · May 17, 2011

Hello,

I have a table in a web database in which one customer can have multiple locations.

In the web query, I collect Customer ID and Customer Name from the customer table, and Location names from the locations table. However, I am told that the multiple-value field for locations is not valid in the join query...

Any ideas?

Thanks,
Alex

rc-alex · May 18, 2011

Please does anyone have any insight into this?

On my form, I need to be able to filter the Customer drop-down to only show customers from certain locations. But I don't know how I can do this without a list of customers tied to each location??

Thank You!!

boblarson · May 18, 2011

This is one reason why using a normalized version of your database instead of using multi-valued fields makes things so much easier. You would normally have a junction table which stores the customerID and the LocationID and then you would use a subform to add the location to each. Yes, I know the checkboxes on the combo of the MVF is pretty and neat but it is a pain to get the data back out somewhat.

Here's a guide on using MVF's in a query:
http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx#BM7

rc-alex · May 18, 2011

Bob,
So what you're saying (I think) is that I should have a table "Customer-Locations" which stores the Customer ID with each locationID.

So I could have this:

CustID LocID
CustA. Loc1
CustA. Loc2
CustB. Loc5
CustC. Loc6
CustC. Loc1
CustC. Loc3
CustD. Loc3

I think...

boblarson · May 18, 2011

yes, that is how it would normally be done by most developers.

Multiple value field is not valid in query (1 Viewer)

rc-alex

Registered User.

rc-alex

Registered User.

boblarson

Smeghead

rc-alex

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread