locked
SQL Query Access with Column Summing in WHERE Condition RRS feed

  • Question

  • Hi,

    I am looking to run a query in Access 2003 VBA, where the condition of the query is comparing the sums of several fields.

    Here is the query i've come up with so far:

    SELECT History.*  WHERE 50.00 =  select [JK_DON]+[JK_M]+[MI_DON]+[MI_M] AS Total From History

    Anyone know the syntax I need?

    Thursday, June 7, 2012 3:22 AM

Answers

  • You need to place the sub query within brackets;

    SELECT History.*  
    WHERE 50.00 = (select [JK_DON]+[JK_M]+[MI_DON]+[MI_M] From History)


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for changing her words.


    • Edited by Ray Brack Thursday, June 7, 2012 4:51 AM
    • Marked as answer by M_Parker_1170 Friday, June 8, 2012 3:30 AM
    Thursday, June 7, 2012 4:50 AM
  • I suspect that what you want is:

    SELECT *
    FROM History
    WHERE 50 = jk_don  + jk_m + mi_don + mi_m

    I assume that none of the four fields can be NULL. If not, you have to convert those nulls to 0 (probably):

    SELECT *
    FROM History
    WHERE 50 = NZ( jk_don, 0) + NZ(jk_m, 0) + NZ(mi_don,0) + NZ(mi_m, 0)

    Note that it is preferable to have the WHERE clause after the FROM clause.

    Thursday, June 7, 2012 3:05 PM

All replies

  • You need to place the sub query within brackets;

    SELECT History.*  
    WHERE 50.00 = (select [JK_DON]+[JK_M]+[MI_DON]+[MI_M] From History)


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for changing her words.


    • Edited by Ray Brack Thursday, June 7, 2012 4:51 AM
    • Marked as answer by M_Parker_1170 Friday, June 8, 2012 3:30 AM
    Thursday, June 7, 2012 4:50 AM
  • I suspect that what you want is:

    SELECT *
    FROM History
    WHERE 50 = jk_don  + jk_m + mi_don + mi_m

    I assume that none of the four fields can be NULL. If not, you have to convert those nulls to 0 (probably):

    SELECT *
    FROM History
    WHERE 50 = NZ( jk_don, 0) + NZ(jk_m, 0) + NZ(mi_don,0) + NZ(mi_m, 0)

    Note that it is preferable to have the WHERE clause after the FROM clause.

    Thursday, June 7, 2012 3:05 PM
  • That could only work for cases where there is just one record in table History, since otherwise, (SELECT ... FROM history)  would return MULTIPLE rows, and    WHERE 50 = ( SELECT returning multiple rows )  would return an error.  The right syntax would be WHERE 50 IN( SELECT ... )  or, less frequent,  WHERE 50 = ANY (SELECT ... )

    Friday, June 8, 2012 12:58 PM