Query returning 0 (2002)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote