Multiselect Listbox passing to a field

[PolarBear]

OK - I have seen the other posts where individuals are trying to select multiple items from a list box and have a field populate with the selections. I have not seen a clear explanation defining if this is possible.

Essentially, I want to be able to query on the field and search for multiple selections within that field. Any recommendations as to how this can be achieved?

My next question, is if the selections in a multiple instance field are separated by a comma or some other character what is the best method to query for multile responses. For example, if the following data is in the field 1,2,3 and I want to query on 1 or 2?

Regards,
PolarBear

 

[Pat Hartman]

OK - I have seen the other posts where individuals are trying to select multiple items from a list box and have a field populate with the selections. I have not seen a clear explanation defining if this is possible.

I'm not sure what is not clear. They all tell you, as does help, that list boxes set to multi-select are not bound. That means that YOU would need to write any code necessary to persist their values. This behavior is not supported by Access because it is specifically prohibited by the specifications that define relational databases. In a relational database, each column must be atomic. That means that it cannot be further subdivided. So "Pat Hartman" and "Christine, Mikayla, Alexis" are both violations of that rule. "Pat Hartman" contains two separate attributed - first and last name and "Christine, Mikayla, Alexis" violates because it contains multiple values for the same attribute (children). Your situation is analogous to the second situation. This is generally referred to a repeating group and is actually a 1-many relationship. 1 person may have many children. The many-side of a relationship is properly stored as rows in a related table.

Essentially, I want to be able to query on the field and search for multiple selections within that field. Any recommendations as to how this can be achieved?

Use the proper database construct and create a separate table.

My next question, is if the selections in a multiple instance field are separated by a comma or some other character what is the best method to query for multiple responses. For example, if the following data is in the field 1,2,3 and I want to query on 1 or 2?

The "atomic" rule is not arbitrary. If you violate it, you will not be able to use simple queries to analyze your data. You will need to write code to parse the string and search the items yourself.

Do yourself a big favor and spend some time reading on normalization before you continue.

 

[PolarBear]

Thanks for the reply. I have an appreciation for normalization. I guess I was searching for a solid answer which you provided. I also make use of SQL server and we have a front end progam that allows us to create multiselect fields. This is very useful when we run queries and search for multiple responses within a field. For example, an individual responds to a survery asking what summer activities do you like? The individual can respond and indicate activities A,B,D,F. This data is then stored in a field called Activities.
Then when we run queries, even using Access against the SQL database we can search for multiple responses in Activites field. For example, I may query on everyone that is interested Activity A AND D adn set the criteria accordingly.

Any additional comments would be appreciated.

Regards

Polar Bear

 

[Pat Hartman]

Using a non-normalized structure is your perogative. It just means that you will be writing code to support your choice. You will need to write your own functions to store the data and to populate the listbox when the data is displayed.

Try to write a single query that counts the number of participants in each activity and produce a list similar to the following to see what I ment by analysis.

ActivityA 34
ActivityB 45
ActivityC 22
ActivityD 89

With a normalized structure the query is:
Select Activity, Count(*) As ActivityCount
From YourTable;