Two step query into one

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?

Query AAA
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
HAVING (((MASTER_DSC.DSC_CUR_BAL_21)>0));

Query BBB
SELECT AAA.FirstOfCIFH_HSHLD_NBR, Sum(AAA.DSC_CUR_BAL_21) AS SumOfDSC_CUR_BAL_21
FROM AAA
GROUP BY AAA.FirstOfCIFH_HSHLD_NBR;
K_DeutschAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Try this one:
SELECT SUM(MASTER_DSC.DSC_CUR_BAL_21)  AS SumOfDSC_CUR_BAL_21, CIFHSHLD.CIFH_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
WHERE MASTER_DSC.DSC_CUR_BAL_21>0
GROUP BY CIFHSHLD.CIFH_HSHLD_NBR;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K_DeutschAuthor Commented:
Number of households correct but household sums are overstated.

How about looking at my query again (below) because I removed a superfluous table and it might make the solution easier. So now the query involves just three tables. MASTER_DSC  table consists of unique account numbers + their balances. I am sure the overstated sums per household are related to the fact that-

CIFRELTN table (whose records define relationships between an account and a customer ID)  includes duplicates of the CIFR_REL_ACCT field (which is the same as account number from MASTER_DSC table)  because accounts can have multiple relationships; for example a husband and wife have a joint account and each of them have a unique customer ID.

CIFMASTR table (which consists of unique customer ID and the associated household number) has duplicates of the CIFM_HSHLD_NBR  field because as single CIFM_HSHLD_NBR households can be associated with multiple customer IDs.

So here is the simplified version of the query in my original post

SELECT CIFRELTN.CIFR_REL_ACCT, MASTER_DSC.DSC_CUR_BAL_21, First(CIFMASTR.CIFM_HSHLD_NBR) AS FirstOfCIFM_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
GROUP BY CIFRELTN.CIFR_REL_ACCT, MASTER_DSC.DSC_CUR_BAL_21
HAVING (((MASTER_DSC.DSC_CUR_BAL_21)>0));
0
ThomasMcA2Commented:
When debugging queries, sometimes it helps to create a test copy and remove the "group by" logic from the test. If that shows you too much data, select a specific element (such as a customer #, item #, order #, etc.) that will limit your data to a handful of rows.

If your source tables are supposed to have a one-to-many relationship, then your join criteria must include enough fields to duplicate that one-to-many relationship.
0
K_DeutschAuthor Commented:
Still has duplicates but much better than where I started.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.