Results 1 to 4 of 4

Thread: Query Help!

  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Query Help!

    (My last request got a bit more complicated.....maybe not?)

    I am using the following table:

    Product....Type.....EMP...Per1......Per2......Per3 ......Per4.....Per5........Per6
    IBM............A........Full....0...........0..... .....28........34.........0..........0
    IBM............A........Part...0...........1...... ....28........34.........4..........0
    APPLE.........B........Full....0...........4...... .....22.......0...........0..........0
    APPLE.........B........Part...0...........4....... ....0.........11.........0..........0

    I am trying to get the following results:
    Product.....Type.....Total
    IBM.............A.........4
    APPLE..........B.........3

    Period 3, for example:

    If the total of Product IBM Type A for period 3 is greater than 0 then count it as a 1 (28+28=56>1 therefore a 1)

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Product
         , Type
         , case when sum(Per1) > 1 then 1 else 0 end 
         + case when sum(Per2) > 1 then 1 else 0 end 
         + case when sum(Per3) > 1 then 1 else 0 end 
         + case when sum(Per4) > 1 then 1 else 0 end 
         + case when sum(Per5) > 1 then 1 else 0 end 
         + case when sum(Per6) > 1 then 1 else 0 end as Total 
      from yourtable
    group
        by Product
         , Type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    100
    I don't seem to be able to get the CASE statement to work in SQL using Access

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmmm, i wonder why

    this being the SQL Server forum, i naturally gave you SQL Server syntax

    in Access you say

    iif(isnull(sum(Per1)),1,0) + ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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