• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

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;
0
K_Deutsch
Asked:
K_Deutsch
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now