Queries is fetching undesired output from two tables (1 Viewer)

Vinod9111

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

I’m here to help
Staff member
Local time
Yesterday, 16:46
Joined
Oct 29, 2018
Messages
13,202
Hi. It's all in the way query joins are processed. Are you getting more or less records than you expected?
 

Vinod9111

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

I’m here to help
Staff member
Local time
Yesterday, 16:46
Joined
Oct 29, 2018
Messages
13,202
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

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 18:46
Joined
Feb 28, 2001
Messages
18,344
@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

error reading drive A:
Local time
Today, 08:46
Joined
May 7, 2009
Messages
10,858
if you included [Date field] on both tables, it will create a Cartesian query, doubling (even triple) the result.
 

Vinod9111

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

error reading drive A:
Local time
Today, 08:46
Joined
May 7, 2009
Messages
10,858
If you can show your SQL statement.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:46
Joined
Oct 29, 2018
Messages
13,202
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

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

I’m here to help
Staff member
Local time
Yesterday, 16:46
Joined
Oct 29, 2018
Messages
13,202
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:46
Joined
Feb 19, 2002
Messages
30,090
The three tables do not have a hierarchical relationship so you are ending up with a Cartesian product.
branch master is related to Disbursement
and branch master is related to outstanding

There is no relationship between Disbursement and Outstanding despite the fact that they contain a field on which you can join. When you join Disbursement to Outstanding the results appear to multiply because every row in Disbursement is joined to every matching row in Outstanding. Therefore if you have 3 rows in Disbursement for ID of Branch 4 and 5 rows in Outstanding for ID of Branch 4, the resultset will be 3 * 5 or 15 rows.

Summarizing as DBGuy suggested will eliminate the problem because that will reduce the number of rows in Disbursement to ONE for each Branch and also in Outstanding. Therefore, you end up with 1 * 1 * 1 so there is no "duplication.

If your report needs to show details rather than a summary, you will need to use a main report to show branch data and two subreports. One each for Disbursement and Outstanding.
 

arnelgp

error reading drive A:
Local time
Today, 08:46
Joined
May 7, 2009
Messages
10,858
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

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

I’m here to help
Staff member
Local time
Yesterday, 16:46
Joined
Oct 29, 2018
Messages
13,202
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

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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

I’m here to help
Staff member
Local time
Yesterday, 16:46
Joined
Oct 29, 2018
Messages
13,202
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

New member
Local time
Today, 06:16
Joined
Sep 4, 2020
Messages
18
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
 

Users who are viewing this thread

Top Bottom