Querying on field with multi-values

  • We have an application that allows a user to manage events. Each event has an associated region or regions. The application has a list box option in which the user can select one or multiple regions associated with the event. The database field will store multiple regions if selected. Therefore a record could look like...

    Event 1 Region 1

    Event 2 Region 1, Region2

    Would need to show Event 1 and Event 2 for Region 1 and Event 2 for Region 2 in a query. How could I do this?

    Thanks.

  • You basically need to parse or split the string to get the unique values out of it. You can do this in a number of ways. The tally table article in my signature gives a step by step example of one of those ways.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • The following schema would make your job easier in the long run. Especially if your next question is going to be how to identify which events are going to take place in which regions. I would seriously consider changing to this.

    Event Region

    ------- -------

    Event1|Region1

    Event1|Region2

    Event2|Region2

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • I would like to maintain that schema, but it requires a new record for the event each time the region changes.

  • If you have control over the schema design, please take Bob's advice. And read up on database normalization.



    --Jonathan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply