Hi,
Iam new to the forum, definitely looking for answers. I have been struggling with this query for three weeks now. I have four tables , voucher , Account and Report and Employee. I want to count the number of forms entered by each employee on to the other 3 tables ; voucher, Account and report. Here is the design of my tables;
Account table:
AccountID |
CustomerName |
BranchNumber |
AccountNumber |
AccountType |
UserCode |
2 |
Moses Kausa |
77 |
01234599999 |
Current Account |
7777 |
3 |
Moses Kausa |
77 |
01234588877 |
Joint Account |
7777 |
4 |
John Chisha |
44 |
01234566607 |
Savings Account |
6666 |
Voucher table:
VoucherID |
VoucherDate |
BranchNumber |
UserName |
UserNumber |
TerminalNumber |
Barcode |
UserCode |
8 |
2/6/2012 |
10 |
Friday001 |
25 |
30 |
50080099 |
5555 |
9 |
1/9/2012 |
10 |
Friday001 |
25 |
30 |
60080077 |
5555 |
10 |
3/18/2012 |
10 |
Friday001 |
25 |
30 |
40030066 |
5555 |
Report table:
ReportID |
ReportDate |
BranchNumber |
UserName |
UserNumber |
TerminalNumber |
Barcode |
UserCode |
1 |
3/13/2012 |
10 |
Moses |
Moses002 |
99 |
88890077 |
7777 |
2 |
3/28/2012 |
5 |
Moses |
Moses002 |
99 |
89999008 |
7777 |
3 |
2/15/2012 |
6 |
Moses |
Moses002 |
99 |
89990077 |
7777 |
4 |
2/13/2012 |
6 |
Moses |
Moses002 |
99 |
78665545 |
7777 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The other table is Employee and look as below
EmpID |
FirstName |
LastName |
UserCode |
2 |
Moses |
Kausa |
7777 |
3 |
Peter |
Barnes |
5555 |
4 |
Rocsina |
Rita |
8888 |
5 |
Louis |
Chaze |
9999 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The problem is that The queries doubles counts on one employee and I don't know why . Please help , so that the count can show exactly what each employee has captured per form type . Here is the Query.
Code:
SELECT e.FirstName, Count(a.BranchNumber) AS ["Number of Accounts forms Captured"], Count(v.BranchNumber) AS ["Number of Vouchers Captured"], Count(r.BranchNumber) AS ["Number of Reports Capttured"]
FROM ((Employee AS e LEFT JOIN Account AS a ON e.UserCode=a.UserCode) LEFT JOIN Voucher AS v ON e.UserCode=v.UserCode) LEFT JOIN Reports AS r ON e.UserCode=r.UserCode
GROUP BY e.FirstName
ORDER BY e.FirstName;
This code is expected to produce the following results:
FirstName |
"Number of Accounts forms Captured" |
"Number of Vouchers Captured" |
"Number of Reports Capttured" |
Moses |
2 |
0 |
4 |
Friday |
0 |
3 |
0 |
Louis |
0 |
0 |
0 |
Rocsina |
0 |
0 |
0 |
|
|
|
|
But the query does not ,
Thank you .