Results 1 to 3 of 3

Thread: Query help!

  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Query help!

    I am using the following table:

    Product.......Per1......Per2......Per3......Per4.. ...Per5........Per6
    IBM..............0...........1..........28........ 34.........4..........0
    APPLE...........0...........4...........22.......1 1.........0..........0

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

    based on totaling the number of Per# fields that have a number greater than 0.

    I've tried so many different things (mostly using the aggregate functions) but can't seem to get good results.

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by norm801
    I am using the following table:

    Product.......Per1......Per2......Per3......Per4.. ...Per5........Per6
    IBM..............0...........1..........28........ 34.........4..........0
    APPLE...........0...........4...........22.......1 1.........0..........0

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

    based on totaling the number of Per# fields that have a number greater than 0.

    I've tried so many different things (mostly using the aggregate functions) but can't seem to get good results.

    Thanks!
    SELECT Product, SUM(IIf(Per1>0,1,0)+IIf(Per2>0,1,0)+IIf(Per3>0,1,0 )+...+IIf(PerN>0,1,0)) AS Total FROM SomeTable;

    And your next question is????
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2004
    Posts
    100
    Thanks for the response. Big help....as always!

Posting Permissions

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