Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL Help

Discussion in 'Information Technology' started by merc, Jul 28, 2006.

Tags:
  1. merc

    merc
    Expand Collapse
    Guest

    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    UNION ALL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL

    etc will give four rows

    another trick

    SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

    will give the four in one row

    HTH

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:Xnuxg.17425$9d4.199@fe2.news.blueyonder.co.uk...
    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    UNION ALL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL

    etc will give four rows

    another trick

    SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

    will give the four in one row

    HTH

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:Xnuxg.17425$9d4..199@fe2.news.blueyonder.co.uk...
    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick

    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. merc

    merc
    Expand Collapse
    Guest

    HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

    ANY SUGGESTIONS WHERE I AM GOING WRONG


    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
    GROUP BY MRP_CODE.CELL
    UNION ALL;

    NICK

    "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> wrote in message news:O$18y%23HsGHA.4752@TK2MSFTNGP03.phx.gbl...
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    UNION ALL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL

    etc will give four rows

    another trick

    SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

    will give the four in one row

    HTH

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:Xnuxg.17425$9d4.199@fe2.news.blueyonder.co.uk...
    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Extra UNION ALL at the end

    Pieter
    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:V7Nxg.20848$9d4..13432@fe2.news.blueyonder.co.uk...
    HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

    ANY SUGGESTIONS WHERE I AM GOING WRONG


    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
    GROUP BY MRP_CODE.CELL
    UNION ALL;

    NICK

    "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> wrote in message news:O$18y%23HsGHA.4752@TK2MSFTNGP03.phx.gbl...
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE..MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    UNION ALL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE..MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL

    etc will give four rows

    another trick

    SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 .....

    will give the four in one row

    HTH

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:Xnuxg.17425$9d4.199@fe2.news.blueyonder.co.uk...
    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick

    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  7. merc

    merc
    Expand Collapse
    Guest

    Pieter

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
    GROUP BY MRP_CODE.CELL;



    works but it puts all info into one Field only UPTO_24


    CELL UPTO_24
    Cell D 4
    Cell E 53
    Cell E 109
    Cell E 118
    Cell E 196
    Cell F 67
    Cell F 72


    thanks Nick
















    "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> wrote in message news:OQxIxEPsGHA.5100@TK2MSFTNGP06.phx.gbl...
    Extra UNION ALL at the end

    Pieter
    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:V7Nxg.20848$9d4.13432@fe2.news.blueyonder.co.uk...
    HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

    ANY SUGGESTIONS WHERE I AM GOING WRONG


    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
    GROUP BY MRP_CODE.CELL
    UNION ALL;

    NICK

    "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> wrote in message news:O$18y%23HsGHA.4752@TK2MSFTNGP03.phx.gbl...
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    UNION ALL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL

    etc will give four rows

    another trick

    SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

    will give the four in one row

    HTH

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:Xnuxg.17425$9d4.199@fe2.news.blueyonder.co.uk...
    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick


    ------------------------------------------------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Try SPAMfighter for free now!
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    you have to use my seccond version to get them accross or use the below code if you want them on seperate rows
    Note that the column headings are determined by the first select

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CNT, "14 To 24" AS DAYSSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS SomeThing , "25 To 42" AS Disregarded
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70, "43 To 70" AS DAYSSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71, "71=>" AS DAYSSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM >=71
    GROUP BY MRP_CODE.CELL;

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:wOQxg.21087$9d4.8881@fe2.news.blueyonder.co.uk...
    Pieter

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
    GROUP BY MRP_CODE.CELL;



    works but it puts all info into one Field only UPTO_24


    CELL UPTO_24
    Cell D 4
    Cell E 53
    Cell E 109
    Cell E 118
    Cell E 196
    Cell F 67
    Cell F 72


    thanks Nick
















    "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> wrote in message news:OQxIxEPsGHA.5100@TK2MSFTNGP06.phx.gbl...
    Extra UNION ALL at the end

    Pieter
    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:V7Nxg.20848$9d4.13432@fe2.news.blueyonder.co.uk...
    HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

    ANY SUGGESTIONS WHERE I AM GOING WRONG


    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
    GROUP BY MRP_CODE.CELL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
    GROUP BY MRP_CODE.CELL
    UNION ALL;

    NICK

    "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway> wrote in message news:O$18y%23HsGHA.4752@TK2MSFTNGP03.phx.gbl...
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
    GROUP BY MRP_CODE.CELL
    UNION ALL
    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
    GROUP BY MRP_CODE.CELL

    etc will give four rows

    another trick

    SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

    will give the four in one row

    HTH

    Pieter

    "merc" <thbr27724@blueyonder.co.uk> wrote in message news:Xnuxg.17425$9d4.199@fe2.news.blueyonder.co.uk...
    hi i have a query

    SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
    FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
    GROUP BY MRP_CODE.CELL;

    is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

    1. count between >=14<=24
    2. count between >=25<=42
    3. count between >=43<=70
    4. count between >=71

    rather than just one count

    Many Thanks

    Nick


    ----------------------------------------------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Try SPAMfighter for free now!
     

Share This Page