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

[Pat Hartman]

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.

 

[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

 

[theDBguy]

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

Hi. Glad to hear you got it sorted out. Good luck with your project.