Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query returning 0 (2002)

    Well talk about back to basics.
    I have a query that asks for a Sum of the Free Balance for a given account number. The criteria is set at >0. I'm getting accounts with 0 balance. Now why would it do that?
    This query addresses three tables. Free Balance is the only one from the SubBalance table.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returning 0 (2002)

    You will have to provide more information about the query. For example, post the SQL, or post a stripped down and zipped version of the database.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query returning 0 (2002)

    SELECT SubBalances.[Account Number], master.[account-expiration], master.[reporting-grant-dept], master.[reporting-grant-type], master.[reporting-grant], Sum(SubBalances.[free-balance-amt]) AS [SumOffree-balance-amt], [PSA Assignment].[DEPT LTR], [PSA Assignment].PSA_I, master.activity
    FROM [PSA Assignment] INNER JOIN (SubBalances INNER JOIN master ON SubBalances.[Account Number] = master.[account-nbr]) ON [PSA Assignment].[DEPT LTR] = master.[dept-div]
    GROUP BY SubBalances.[Account Number], master.[account-expiration], master.[reporting-grant-dept], master.[reporting-grant-type], master.[reporting-grant], [PSA Assignment].[DEPT LTR], [PSA Assignment].PSA_I, master.activity
    HAVING (((SubBalances.[Account Number]) Between "49000" And "49999") AND ((Sum(SubBalances.[free-balance-amt]))>0) AND ((master.activity)="1")) OR (((SubBalances.[Account Number]) Between "59000" And "59999") AND ((master.activity)="9"))
    ORDER BY SubBalances.[Account Number];

    This is it. Thanks in advance for your help. Already I think I see a problem, but I will let you confirm or tell me something else.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returning 0 (2002)

    As you can see from the SQL, you are selecting on

    Sum(SubBalances.[free-balance-amt])>0

    In other words, you are not testing whether free-balance-amt is > 0, but whether its sum is > 0. To correct this, add free-balance-amt another time to the query grid, set the Total option to Where (this will automatically clear the Show check box), and move the >0 criteria from the column that calculates the sum of free-balance-amt to this new column.

Posting Permissions

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