Results 1 to 3 of 3
  1. #1
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35

    Exclamation count and switch function in same select query


    My Main table looks like this



    FlowerID Red Yellow Orange Violet
    01 ---------1-----0-------0-------0--
    02 ---------1-----0-------1-------0--
    03 ---------0-----0-------1-------0--
    04 ---------1-----1-------1-------0--
    05 ---------0-----0-------0-------1--

    I need to retrive some thing like this, by a select query

    FlowerID Colors NumberOfColors
    01--------Red----------------1
    02--------Red, Orange--------2
    03--------Orange-------------1
    04--------Red, Yellow---------2
    05--------Violet-------------- 1

    In this select query both Switch function and count function should be used to gether. But I dont know how to do it. Pls help,
    Or is there any other ways?
    I'm new 2 querues, Need you kind help,

    Thanx 4 reading my post.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your data table is not normalized, which will make this more difficult. What are you going to do when you get a new color? Everything in your application will have to be changed. Count() works across records, not fields, and I'm not visualizing how Switch() would help you. In any case, I'd probably create functions to return your values. Your alternative is probably a bunch of IIf() functions:

    IIf(Red = 1, "Red,", "") & IIf(Yellow = 1, "Yellow,", "") &...

    Similar thing with the count but adding instead of concatenating.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Thanxalot 4 quick reply, very useful advice

    yes, my table is not properly normalized. Also I hav similar
    tables 4 Fruit color, Leaf color.What about using all
    colors n same table like;

    Red=1
    Orange=2
    Yellow=3
    Green=4
    Blue=5

    ID ------FlowerCol--FruitCol--LeafCol
    01 ---------12--------Null-------4
    02 ---------5---------3---------34
    03 ---------2---------2---------4
    04 ---------13--------1---------4

    I think wild cards can b use here without commas to
    separate numbers like (1,2)
    Now I can count the number of colors in each flower,
    fruit, flower and also flowers with mix colors (more than 1 color)

    But dont know how to retrieve a result like this

    ID ------FlowerCol----FruitCol----LeafCol
    01 ----Red, Orange-----No--------Green
    02 ------Blue---------Yellow----Green,Yellow

    I have a separate Decode table, so that I can add new colors.
    I make sure each color represent by only 1 letter (1,2,3,A,B,C)

    word---Code
    Red--------1
    Orange-----2
    Yellow------3
    Green------4
    Blue--------5

    pls help,
    Last edited by sandlucky; 04-08-2011 at 11:19 PM. Reason: error

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using SQL switch function MS Access
    By sandlucky in forum Queries
    Replies: 18
    Last Post: 03-31-2011, 08:49 AM
  2. Select Query in Switch Function
    By sandlucky in forum Queries
    Replies: 0
    Last Post: 03-30-2011, 04:54 AM
  3. Switch function query to combine tables
    By sandlucky in forum Queries
    Replies: 2
    Last Post: 03-29-2011, 09:46 PM
  4. Count function on query
    By yousillygoose in forum Queries
    Replies: 1
    Last Post: 02-15-2010, 09:58 PM
  5. Query using count function
    By wasim_sono in forum Queries
    Replies: 0
    Last Post: 11-28-2007, 03:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums