Results 1 to 10 of 10
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: Select across columns based on data

    In Access, have a table with products and several columns for profit during a month, which may or may not be a positive number. For example:

    productID Jan Feb Mar Apr
    Widget1 42.50 -12.10 -1.35 34.23
    Widget2 45.00 12.23 -23.10 18.55

    Trying to create a query that will return productID for ONLY those that have more than one month in negative numbers. So, above, would return Widget1 but not Widget2.

    Probably a GroupBy issue. Thank you in advance.

    Carlco

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Code:
    with cte as
    (
       select ID,
               case when m1 < 0 then 1 else 0 end +
               case when m2 < 0 then 1 else 0 end +
               case when m3 < 0 then 1 else 0 end +
               case when m4 < 0 then 1 else 0 end as numNeg
         from t3 
    )
    Select t.ID 
      From t3 t
      left join cte c on c.ID = t.ID
      where c.numNeg > 1
      Order by t.ID
    Of course you will need to add the remaining months, m5 through m12, to the cte.
    Last edited by LinksUp; 05-03-13 at 00:53.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @LinksUp: That is SQL Server syntax, it does not work with MS Access to which this forum is dedicated.
    Have a nice day!

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Sinndho View Post
    @LinksUp: That is SQL Server syntax, it does not work with MS Access to which this forum is dedicated.
    Sorry about that. Then just move the cte into the join.

    Code:
    Select t.ID 
      From t3 t
        left join 
        (
          select ID,
              case when m1 < 0 then 1 else 0 end +
              case when m2 < 0 then 1 else 0 end +
              case when m3 < 0 then 1 else 0 end +
              case when m4 < 0 then 1 else 0 end as numNeg
            from t3 
         ) c 
         on c.ID = t.ID
      where c.numNeg > 1
      Order by t.ID
    Last edited by LinksUp; 05-03-13 at 17:49.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Access SQL supports subqueries but not the CASE instruction as well as many others. Have a look at: List of reserved words in Jet 4.0
    Have a nice day!

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Sinndho View Post
    Access SQL supports subqueries but not the CASE instruction as well as many others. Have a look at: List of reserved words in Jet 4.0
    The link you provided shows that CASE is supported!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's a reserved word but it only works when your database is built on the ADP model, which is connected to a SQL server. About ADP, see: Access ADP no longer supported in Access 2013, here are some tips. | accessexperts.com
    Have a nice day!

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Sinndho View Post
    It's a reserved word but it only works when your database is built on the ADP model, which is connected to a SQL server.
    OK, how about replacing the case with iif.

  9. #9
    Join Date
    May 2013
    Posts
    2

    Thanks everyone!

    At least this gives me a starting point. Seems like a tricky one.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is your requirement for two rows with negative numbers, or two sequential rows with negative numbers?
    Code:
    SELECT *
       FROM TableNameGoesHere
       WHERE 2 <= Iif(jan<1, 1, 0) + Iif(feb < 1, 1, 0) + Iif(mar < 1, 1, 0) + Iif(apr < 1, 1, 0)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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