Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (2002 SP3)

    I am using a lookup field to provide three choices of categories to customers. Each customer can choose up to three categories. Does anyone know how I can create a query which merges the categories into one field? I will then run a report grouped by the Categories listing each of the customers under each category.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (2002 SP3)

    Select tbl_Misc.* from tbl_Misc (Obviously use your own table(s) and fields here)
    where tbl_Misc.Category In('ThisOne', 'That One', 'The Other Thing');

    I prefer the In() function for a somewhat tidier SQL statement (as opposed to linking OR statements)

    Are you intending to provide a listbox with multiple selections possible?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query (2002 SP3)

    Are the three Categories for a Customer stored in three separate fields? or are they stored in a separate table?

    It sounds like they are in separate fields. Creating queries or reports where you have multiple similar fields is very messy.

    Instead you should have a CustomerCategories table with a CustomerID and a Category. Either make these two a joint key, or add an extra autonumber field as a key.
    This data is then entered via a subform on your customer form.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •