Results 1 to 4 of 4
  1. #1
    SheikhMusa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    2

    Lightbulb Count with Left Join on four tables

    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 .

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I doubt this can be done in query that joins the tables. Do separate queries that perform the counts from the three tables then join the queries to the employees table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    SheikhMusa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    2
    I'm not sure how you mean , the query is supposed to run without user input ?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand your confusion. What user input? Build and save queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left Join not
    By j_a_monk in forum Queries
    Replies: 5
    Last Post: 08-07-2011, 09:47 AM
  2. inner, left or right join ?
    By toqilula in forum Access
    Replies: 0
    Last Post: 04-11-2011, 12:20 AM
  3. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  4. Left join problem
    By e.badin in forum Queries
    Replies: 5
    Last Post: 01-17-2011, 08:03 AM
  5. Update with LEFT JOIN
    By mcarthey in forum Access
    Replies: 1
    Last Post: 08-27-2008, 10:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums