- Local time
- Yesterday, 23:18
- Joined
- Feb 19, 2002
- Messages
- 43,266
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.
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.