Queries is fetching undesired output from two tables

[Vinod9111]

Hi there,

I have three tables i.e branch master , Disbursement and outstanding. Branch code is common among all three tables hence i have created a relation with both tables using branch code from branch master to branch code of respective tables.

I need to report a data in format which will populate branch code wise disbursement and outstanding in single report. When i run the query taking two tables either i.e branch master and disbursement or branch master and outstanding output comes correct but the moment i take all the three tables , output comes out weird. Not able to figure out what would be the issue.

Any suggestion or help will be appreciated.

regards
Vinod Krishna

 

[theDBguy]

Hi. It's all in the way query joins are processed. Are you getting more or less records than you expected?

 

[Vinod9111]

Hi. It's all in the way query joins are processed. Are you getting more or less records than you expected?

Hi,
I am getting more records than actual, I have a feeling it is fetching from other table too for which I have not considered in my formula.
Regards
Vinod Krishna

 

[theDBguy]

Hi,
I am getting more records than actual, I have a feeling it is fetching from other table too for which I have not considered in my formula.
Regards
Vinod Krishna

Can you post a sample db with test data?

 

[The_Doc_Man]

@Vinod9111 - typically, when you are getting MORE records than you think you should, it is because you are getting combinations of some things that aren't unique. Think back to your statistics classes and combinatorial math that talks about how many combinations of things you can have from a population of X taken Y at a time. What is going on is similar to that.

It most OFTEN occurs when your selection criteria (your WHERE clause) doesn't completely exclude certain combinations. And that is the important thing to remember. The Jet or ACE database engine is going to give you all combinations of records that meet your criteria. If, as you say, that third table gets thrown into the mix, something about it is providing the extra combinations that become your extra records. You have to also add some constraints for that table as well.

As theDBguy suggests, we are limited in what we might be able to do without something specific to look at.

 

[arnelgp]

if you included [Date field] on both tables, it will create a Cartesian query, doubling (even triple) the result.

 

[Vinod9111]

if you included [Date field] on both tables, it will create a Cartesian query, doubling (even triple) the result.

Hi arnelgp,

Am not using date but you are right output is incredibly more , am very naïve with regard to access not sure how to address it. I randomly checked for one branch it was 112 repetitive records been culled out. Any ideas or tips to mitigate Cartesian query.

regards.

Vinod Krishna

 

[Vinod9111]

Can you post a sample db with test data?

Hi theDbguy,

The data is in lying in my vpn login and unable to copy data to local drive in order to paste on this forum due to IT restrictions.

regards
Vinod Krishna

 

[arnelgp]

If you can show your SQL statement.

 

[theDBguy]

Hi theDbguy,

The data is in lying in my vpn login and unable to copy data to local drive in order to paste on this forum due to IT restrictions.

regards
Vinod Krishna

Can you create a sample db with test data?

 

[Vinod9111]

Hi arnelgp,


SELECT Branchmaster.[State code], Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="SC",[disb]![Sum of Loan Number of Accounts],0)) AS [Sc disb], Round(Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="SC",[disb]![Sum of Loans Limit (In lacs)],0)),0) AS [sc amt], Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="ST",[disb]![Sum of Loan Number of Accounts],0)) AS [st disb], Round(Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="ST",[disb]![Sum of Loans Limit (In lacs)],0)),0) AS [st disb amt], Branchmaster.[Branch code]
FROM (Branchmaster INNER JOIN disb ON Branchmaster.[Branch code] = disb.[Final Branch Code]) INNER JOIN outstanding ON Branchmaster.[Branch code] = outstanding.[Final Branch Code]
GROUP BY Branchmaster.[State code], Branchmaster.[Branch code]
HAVING (((Branchmaster.[Branch code])=2984));

Above is the sql statement, if I don't add outstanding table, output comes properly and shows 1 count but once I add outstanding table the output shows 113 count. The format of the report states I have to report branch wise SC and ST disbursement and outstanding hence I have to add outstanding table too for generation of the reports. Hope I making sense.

regards

Vinod Krishna

 

[Vinod9111]

Can you create a sample db with test data?

Hi theDBguy,

Below is the dummy records of the table

Branch master table
branch code state code district code
1 02 101
2 02 104
3 04 121
4 03 115

disbursement table summary
branch code LBR category segment count amount
1 SC weaker 4 25
1 ST weaker 10 30
1 agri advances 25 100
2 SC weaker 3 10

outstanding summary table
branch code LBR category segment count amount
1 SC weaker 200 1500
1 ST weaker 150 3000
1 agri advances 400 40000
1 msme advances 500 340000
2 SC weaker 150 2000



The below is the output which I expect to return if I run the query design basis the state code :02 , lbr code : SC, segment : weaker
disbursement outstanding
state code lbr segment count amount count amount
02 SC weaker 7 35 350 3500

It give proper result as long as I take two tables branch code and disbursement or branch code and outstanding, The moment I also add third table to the query some weird output comes out.

Hope I was able to explain my problem, I have also pasted the sql version above in my earlier post .

regards

Vinod Krishna

 

[theDBguy]

Hi theDBguy,

Below is the dummy records of the table

Branch master table
branch code state code district code
1 02 101
2 02 104
3 04 121
4 03 115

disbursement table summary
branch code LBR category segment count amount
1 SC weaker 4 25
1 ST weaker 10 30
1 agri advances 25 100
2 SC weaker 3 10

outstanding summary table
branch code LBR category segment count amount
1 SC weaker 200 1500
1 ST weaker 150 3000
1 agri advances 400 40000
1 msme advances 500 340000
2 SC weaker 150 2000



The below is the output which I expect to return if I run the query design basis the state code :02 , lbr code : SC, segment : weaker
disbursement outstanding
state code lbr segment count amount count amount
02 SC weaker 7 35 350 3500

It give proper result as long as I take two tables branch code and disbursement or branch code and outstanding, The moment I also add third table to the query some weird output comes out.

Hope I was able to explain my problem, I have also pasted the sql version above in my earlier post .

regards

Vinod Krishna

Hi. Thanks for providing some data. All you have to do is to a separate Totals query on the disbursement and outstanding tables first. Then, create a third query to join those two Totals query to your branch master table.

 

[arnelgp]

it does not make sense. you added table [Outstanding Summary] on the Query but for what?
it is not involved in computation nor any of its field being involved or ouputted from the query.

 

[Vinod9111]

Hi. Thanks for providing some data. All you have to do is to a separate Totals query on the disbursement and outstanding tables first. Then, create a third query to join those two Totals query to your branch master table.

Thanks theDBguy,Pat Hartman, arnelgp for your replies.

I have created separate queries for disbursement and outstanding , not sure how to link both to get the output of disbursement and outstanding in one report. Forgive my ignorance of access knowledge.

regards
Vinod Krishna

 

[theDBguy]

Thanks theDBguy,Pat Hartman, arnelgp for your replies.

I have created separate queries for disbursement and outstanding , not sure how to link both to get the output of disbursement and outstanding in one report. Forgive my ignorance of access knowledge.

regards
Vinod Krishna

If you can post a sample db, we can show you how.

 

[Vinod9111]

Hi theDBguy,

Query 1 : SCSTDisb
State code - sc no disb - sc disb amt - ST no disb- ST disb amt
001 - 2 - 200 - 4 - 300
002 - 3 -300 - 6 - 250

Query 2 : SCSTOS
State code - sc no OS - sc OS amt - ST no OS - ST OS
001 - 22 - 2200 - 44 - 1300
002 - 33 -3380 - 46 - 2 250


Output required of merging both queries
State code - sc no disb - sc disb amt - Sc no os -SC os amt - ST no disb- ST disb amt - ST no OS -ST Os
001 - 2 - 200 - 22 - 2200 - 4 - 300 - 44 - 1300
002 - 3 -300 - 33 - 3380 - 6 - 250 - 46 - 2250

I inserted hypen between value to keep them separate.

regards
Vinod krishna

 

[theDBguy]

Hi theDBguy,

Query 1 : SCSTDisb
State code - sc no disb - sc disb amt - ST no disb- ST disb amt
001 - 2 - 200 - 4 - 300
002 - 3 -300 - 6 - 250

Query 2 : SCSTOS
State code - sc no OS - sc OS amt - ST no OS - ST OS
001 - 22 - 2200 - 44 - 1300
002 - 33 -3380 - 46 - 2 250


Output required of merging both queries
State code - sc no disb - sc disb amt - Sc no os -SC os amt - ST no disb- ST disb amt - ST no OS -ST Os
001 - 2 - 200 - 22 - 2200 - 4 - 300 - 44 - 1300
002 - 3 -300 - 33 - 3380 - 6 - 250 - 46 - 2250

I inserted hypen between value to keep them separate.

regards
Vinod krishna

Hi. Are you not able to post a sample db? How about posting the SQL statements of your queries, at least?

 

[Vinod9111]

Hi. Are you not able to post a sample db? How about posting the SQL statements of your queries, at least?

Hi theDBguy,

Please find the SQL statements of both queries


SELECT Branchmaster.[State code], Branchmaster.[District code], Branchmaster.[Block code], Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="SC",[disb]![Sum of Loan Number of Accounts],0)) AS [Sc disb], Round(Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="SC",[disb]![Sum of Loans Limit (In lacs)],0)),0) AS [sc amt], Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="ST",[disb]![Sum of Loan Number of Accounts],0)) AS [st disb], Round(Sum(IIf([disb]![Segment]="weaker" And [disb]![FINAL - SLBC SEGMENT]="ST",[disb]![Sum of Loans Limit (In lacs)],0)),0) AS [st disb amt]
FROM Branchmaster INNER JOIN disb ON Branchmaster.[Branch code] = disb.[Final Branch Code]
GROUP BY Branchmaster.[State code], Branchmaster.[District code], Branchmaster.[Block code];


SELECT Branchmaster.[State code], Branchmaster.[District code], Branchmaster.[Block code], Sum(IIf([outstanding]![Segment]="weaker" And [outstanding]![FINAL - LBR SEGMENT]="Sc",[outstanding]![Loan Number of Accounts],0)) AS [sc no], Round(Sum(IIf([outstanding]![Segment]="weaker" And [outstanding]![FINAL - LBR SEGMENT]="Sc",[outstanding]![amt in lacs],0)),0) AS [sc amount], Sum(IIf([outstanding]![Segment]="weaker" And [outstanding]![FINAL - LBR SEGMENT]="ST",[outstanding]![Loan Number of Accounts],0)) AS [ST no], Round(Sum(IIf([outstanding]![Segment]="weaker" And [outstanding]![FINAL - LBR SEGMENT]="ST",[outstanding]![amt in lacs],0)),0) AS [St amt os]
FROM Branchmaster INNER JOIN outstanding ON Branchmaster.[Branch code] = outstanding.[Final Branch Code]
GROUP BY Branchmaster.[State code], Branchmaster.[District code], Branchmaster.[Block code];

regards
Vinod Krishna

 

[Vinod9111]

You need three queries.
q1
Summarize disbursements
q2
Summarize Outstanding
q3
join branch to q1 and join branch to q2 Use left joins in both cases.

If you have disbursements but not outstanding for a particular branch, the outstanding fields will be null and if you have outstanding for a branch but not disbursements, the disbursement fields will be null.

To do this using the QBE,
1. Create new query.
2. drag the branch table to the grid.
3. drag q1 to the grid
4. drag q2 to the grid
5. Draw join line between q1 and branch on branchID. Change the join type to Left
6. Draw join line between q2 and branch on branchID. Change the join type to Left.
7. Select the columns you want from each table.
8. Save and run.

Notice that the joins are branch to q1 and branch to q2. Do NOT draw a join line between q1 and q2. That would ONLY work if there were ALWAYs BOTH disbursement and outstanding rows for EVERY branch and that is not likely to be the case.

Thanks Pat Hartman so much for providing the solutions step by step in so much detail, it worked perfectly and the output is matching with the actual figures.

regards,

Vinod Krishna