none
Listbox multiple values RRS feed

  • Question

  • In one of my form I have a listbox which allows to store multiple values. These values are stored in single cell (see below)  delimited by | pipe. Now, I want to create reports out of it. How can I query this column?


    Let say I want count of city "JK"

    Tuesday, June 14, 2016 12:43 PM

Answers

  • Thanks for answering. Could you give me a reference link which provides detailed information for the way you suggested or any sample copy. 

    Here's a link with an example:

        http://datagnostics.com/dtips/multiselect.html


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, June 14, 2016 3:03 PM
  • This is a bad table design as the table is not normalized to First Normal Form (1NF), whose formal definition is:

    First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

    Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).

    What you have here is a binary many-to-many relationship type between whatever entity type the table is modelling and cities.  A many-to-many relationship type is modelled by a table which resolves the relationship type into two one-to-many relationship types.  You'll find an example as StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates this sort of basic many-to-many relationship type, and shows various ways of representing it in forms, including the use of a multi-select list box.  I would strongly recommend the use of a conventional form/subform, however, which is very easy to implement and code-free.

    Once you have the data in a set of correctly normalized tables, building a query for use as the RecordSource of a report is a trivial task.

    Ken Sheridan, Stafford, England

    Tuesday, June 14, 2016 4:08 PM

All replies

  • It is a pain.  Really one of the traps almost in what I suppose is a well intentioned feature.

    You can query the City field just like any unstructured string where you need to find a value within the string - typically surrounding it with wild cards.  So to query RN  your criteria would be: Like "*RN*"

    But if you are in the early stages of your database design I would strongly recommend using a normal linked table rather than a multi value list box.  It might be somewhat more challenging in terms of the form's user interface - but it is a lot more straight forward to manage in terms of the data structure in the long run.  Just my opinion.

    Tuesday, June 14, 2016 1:07 PM
  • Thanks for answering. Could you give me a reference link which provides detailed information for the way you suggested or any sample copy. 

    Tuesday, June 14, 2016 2:07 PM
  • I agree with msdnPublicIdentity.

    You need to read up on Relationships, in your case you want a one-to-many relationship so that you can have many City fields for the same ID. Google is full of info, here's one link:

    https://support.office.com/en-us/article/Guide-to-table-relationships-30446197-4fbe-457b-b992-2f6fb812b58f


    Best regards, George

    Tuesday, June 14, 2016 2:25 PM
  • I  understand the table relationship and the link you provided is irrelevant. Thank you for your time.
    Tuesday, June 14, 2016 2:49 PM
  • Thanks for answering. Could you give me a reference link which provides detailed information for the way you suggested or any sample copy. 

    Here's a link with an example:

        http://datagnostics.com/dtips/multiselect.html


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, June 14, 2016 3:03 PM
  • This is a bad table design as the table is not normalized to First Normal Form (1NF), whose formal definition is:

    First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

    Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).

    What you have here is a binary many-to-many relationship type between whatever entity type the table is modelling and cities.  A many-to-many relationship type is modelled by a table which resolves the relationship type into two one-to-many relationship types.  You'll find an example as StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates this sort of basic many-to-many relationship type, and shows various ways of representing it in forms, including the use of a multi-select list box.  I would strongly recommend the use of a conventional form/subform, however, which is very easy to implement and code-free.

    Once you have the data in a set of correctly normalized tables, building a query for use as the RecordSource of a report is a trivial task.

    Ken Sheridan, Stafford, England

    Tuesday, June 14, 2016 4:08 PM
  • Not irrelevant, on the contrary, highly so according to the comments here. Thanks for understanding.

    Best regards, George

    Tuesday, June 14, 2016 4:52 PM
  • Hi Santosh Vi,

    here I would like to recommend you to check the suggestion given by the Ken Sheridan and Dirk Goldgar MVP.

    I think the example given by him can help you to solve your issue.

    so please check it and let us know that your issue is solved or not.

    if you think the suggestion can helped you then I would recommend you to mark the suggestion which helped you to solve your issue as an Answer.

    if not we will try to provide you further help.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 15, 2016 2:44 AM
    Moderator
  • Hi Santosh Vi,

    is your issue solved? did you got solution for your issue?

    you did not gave any response for our last reply.

    if your issue is solved then would you like to share the solution with our community so that if any other member have same issue like you also get solution from your post.

    if your issue did not resolved till now please try to give a response and let us know so that we can try to give you further suggestions.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 21, 2016 7:49 AM
    Moderator