Great Crosby Great Crosby - 5 months ago 11
SQL Question

HAVING Clause in Group Query doesn’t work

In my database I have two tables: tblReceipts and tblDisclosure. (A) Sometimes I receive money but don’t have a disclosure to apply it to. (B) Sometimes I process a disclosure before I receive payment. The following query is intended to return all instances of (A) and (B), but it doesn’t. I get no records and yet I know one such instance exists. When I run the query I get a request to input data in parameter ‘Test’. If I remove the HAVING clause the query correctly returns all records. How can I apply a filter as needed?

SELECT Sum([DBSFee]+[MyFee]) AS Fee, [Amount]+[Adjustment] AS Rec, tblReceipts.ID, [Fee]-[Rec] AS Test, tblClient.ClientName, tblClient.ClientID, tblReceipts.Notes
FROM (tblDisclosure INNER JOIN tblReceipts ON tblDisclosure.ReceiptsLookup = tblReceipts.ID) INNER JOIN tblClient ON tblReceipts.ClientLookup = tblClient.ClientID
GROUP BY [Amount]+[Adjustment], tblReceipts.ID, tblClient.ClientName, tblClient.ClientID, tblReceipts.Notes
HAVING Test<>0;

Answer

Try

Select Fee, Rec, ID, Test, ClientName, ClientID, Notes FROM (
SELECT Sum([DBSFee]+[MyFee]) AS Fee, [Amount]+[Adjustment] AS Rec, tblReceipts.ID, [Fee]-[Rec] AS Test, tblClient.ClientName, tblClient.ClientID, tblReceipts.Notes
FROM (tblDisclosure INNER JOIN tblReceipts ON tblDisclosure.ReceiptsLookup = tblReceipts.ID) INNER JOIN tblClient ON tblReceipts.ClientLookup = tblClient.ClientID
GROUP BY [Amount]+[Adjustment], tblReceipts.ID, tblClient.ClientName, tblClient.ClientID, tblReceipts.Notes) as T
WHERE Test<>0;
Comments