Choose multiple items

[RustyRick]

What direction do I go. I need to be able to choose multiple items from a look up, into one field.

I'll want to analyze a count of the items regularly.

[CJ_London]

with so little information provided, you will get little response. Best I can suggest is perhaps a multiselect listbox on a form to populate a criteria in a group by query, perhaps use the form timer event to refresh on a minute by minute or hour by hour basis.

If you want a more detailed response - relevant tables/relationships and their fields, what does 'regularly' mean? etc

[Bulzie]

I recently created a DB that had multiselect combo boxes and they seem to work great, users love them. I had to keep the BE data MS Access, SQL did not support multiselect data. Also you run into some tricky stuff when putting those fields in queries. If you want to show the one field that contains all the values that is fine but to break them out you have to do a little more work.

[RustyRick]

I recently created a DB that had multiselect combo boxes and they seem to work great, users love them. I had to keep the BE data MS Access, SQL did not support multiselect data. Also you run into some tricky stuff when putting those fields in queries. If you want to show the one field that contains all the values that is fine but to break them out you have to do a little more work.

That's what I wondered. Through time I'll want to query and split the items out and graph totals. But 1 field is better than 5 fields.

[Bulzie]

Might be a better way but to break out each value in the multifield on the record, i created a query for each option in the list that includes the key ID field and the multifield. Then I link those all to the main table by the ID field and instead of adding the multifield to the query, I add all the separate query fields so when you run it all the separate values are on one line. If an option from the list was not selected it shows blank.

Table = Main1, Fields = ID(key) and Color(multiselect of Blue, Red)
Query1 Fields = ID, Color_Blue: Color with criteria of "Blue" (so only pulls records with Blue as Color)
Query2 Fields = ID, Color_Red: Color with Criteria of "Red" (so only pulls records with Red as Color)

Query3: Link Outer Left Join Main1 to Query1 on ID and to Query 2 on ID, select fields ID from Main1, Color_Blue from Query1, Color_Red from Query2.

As I said might be easier way to do it, just what I came up with at the time.

[ssanfu]

I recently created a DB that had multiselect combo boxes <snip>

I was about to say that combo boxes can have only one selected value...... then I realized you were talking about multi-value fields.

So here is my $0.02:

Most experiences programmers avoid the use of Look up FIELDS and Multi value fields (a type of lookup field)

From http://www.techrepublic.com/blog/mic...ess-2007-2010/
"Because such a field violates normalization rules, you might ignore it. The fact is, Access doesn't store the values in a single field. To you, it might look that way, but under the hood, Access stores the data in normalized, but hidden, tables - Access is handling normalization for you, by separating and storing the data and then pulling it all together in the user interface. Technically, the multivalue field is actually a many-to-many relationship, but you don't have to do any of the work to create it and you won't actually see that relationship at work."

From http://answers.microsoft.com/en-us/o...99724f5?auth=1
Marshall Barton wrote:
"There are ways of doing that, but because a lot of how multi value fields wotk is hidden behind the the scenes where we can not get to it, their use makes it difficult. For that reason, I have never used a multi value field and stick to the basic three table way of representing a many to many relationship.
Multi value fields are deceptive in the way Access makes it look like you can violate a fundamental rule of databases that state a field in a table can have one and only one value. You are then lead down the garden path until you need to do something with the table structure that Access has made unavailable to us in the name of convenience only for a very few simple situations.

By the way, Lookup fields are pretty much in the same category and should also be avoided."


To me, MVFs (and lookup fields) have more problems than they are worth. I never let users see the raw tables and don't use datasheet view.
But as long as you understand the problems with look up FIELDS in general and MVF specifically, you might use them.