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

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:45
Joined
Feb 19, 2002
Messages
30,078
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

New member
Local time
Tomorrow, 01:15
Joined
Sep 4, 2020
Messages
18
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

I’m here to help
Staff member
Local time
Today, 11:45
Joined
Oct 29, 2018
Messages
13,197
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.
 

Users who are viewing this thread

Top Bottom