Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Help with a Count Query

    i am tring to carry out a query, that will display the number of occurances that a specific event happens, sorted by machine.

    i have attached and excel spreadshead to show how i am trying to get a report to end up looking like.

    in the table, i the columns labelled:

    minorstop
    majorstop
    breakdown
    cip
    productchange
    maintenance

    refer to time lost through each stoppage.

    i am trying to find out how many time each machine has a fault that falls into a specific stoppage time, eg, minor stop etc..

    hope this makes some sense.

    Please help

    Andy
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    If you redesign your table then the query can be written as:

    select EventCode, stopType, sum(value)
    from tblEvents
    group by EventCode, stopType;

    Your problem can be solved with your current table structure by using:

    select EventCode, 'MinorStop' As "Stop Type", sum(MinorStop)
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'MajorStop' As "Stop Type", sum(MajorStop)
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'BreakDowns' As "Stop Type", sum(BreakDowns)
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'CIP' As "Stop Type", sum(CIP)
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'ProductChange' As "Stop Type", sum(ProductChange)
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'Maintenance' As "Stop Type", sum(Maintenance)
    from tblEvents
    group by EventCode;
    Last edited by r123456; 03-02-04 at 09:24.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    thanks, i see how that works, but i am now given data that repeats itself throughout the 6 different event types.

    EventCode """Stop Type""" Expr1002
    A101 BreakDowns 932
    A101 CIP 932
    A101 Maintenance 932
    A101 MajorStop 932
    A101 MinorStop 932
    A101 ProductChange 932
    A102 BreakDowns 1635
    A102 CIP 1635
    A102 Maintenance 1635
    A102 MajorStop 1635
    A102 MinorStop 1635
    A102 ProductChange 1635
    A103 BreakDowns 1929
    A103 CIP 1929
    A103 Maintenance 1929
    A103 MajorStop 1929
    A103 MinorStop 1929
    A103 ProductChange 1929

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select IIF(NOT Stop_Type = 'BreakDowns', null, EventCode),Stop_Type, Total
    from
    (select EventCode, 'MinorStop' As Stop_Type, sum(MinorStop) As Total
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'MajorStop' As Stop_Type, sum(MajorStop) As Total
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'BreakDowns' As Stop_Type, sum(BreakDowns) As Total
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'CIP' As Stop_Type, sum(CIP) As Total
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'ProductChange' As Stop_Type, sum(ProductChange) As Total
    from tblEvents
    group by EventCode
    UNION
    select EventCode, 'Maintenance' As Stop_Type, sum(Maintenance) As Total
    from tblEvents
    group by EventCode
    ORDER BY EventCode, stop_type)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Mar 2003
    Posts
    225
    cheers but i get a

    Syntax error in FROM clause

Posting Permissions

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