Query AAA by itself gives me a list of account numbers (if balance >0), the current balance, and then the household number the account belongs to. There's duplicate household numbers in this query because multiple accounts can belong to one household.
The result that I really want is a list of unique household numbers and the total balance of all accounts associated with the household. I've tried changing the query to get there but in the end I have to use Query BBB. I'd rather do this in a single query. Is there enough information here to advise, other than learn to live with two?
SELECT CIFRELTN.CIFR_REL_ACCT, MASTER_DSC.DSC_CUR_BAL_21, First(CIFHSHLD.CIFH_HSHLD_NBR) AS FirstOfCIFH_HSHLD_NBR
FROM ((CIFRELTN INNER JOIN MASTER_DSC ON CIFRELTN.CIFR_REL_ACCT = MASTER_DSC.ACCTNO) INNER JOIN CIFMASTR ON CIFRELTN.CIFR_NBR = CIFMASTR.CIFM_NBR) INNER JOIN CIFHSHLD ON CIFMASTR.CIFM_HSHLD_NBR = CIFHSHLD.CIFH_HSHLD_NBR
GROUP BY CIFRELTN.CIFR_REL_ACCT, MASTER_DSC.DSC_CUR_BAL_21
SELECT AAA.FirstOfCIFH_HSHLD_NBR, Sum(AAA.DSC_CUR_BAL_21) AS SumOfDSC_CUR_BAL_21
GROUP BY AAA.FirstOfCIFH_HSHLD_NBR;