Results 1 to 2 of 2
  1. #1
    New_2_Access is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    8

    Counting instances across multiple tables


    Hello,

    I think this is fairly easy, but I don't know what I'm doing. I have three different tables that track the progression of different users on different projects. I need to combine these three into 1 querry that will tell me the number of each status.

    Right now I have 5 tables in total

    1)Owners

    Owners

    Tom
    Bob
    Jill
    John

    2)Projects under 5M

    Owners Status
    Tom Incomplete
    Bob Complete
    Tom Complete

    3)Projects over 5M

    Owners Status
    Jill Incomplete
    John Complete
    Jill Complete

    3)Misc

    Owners Status
    Bob Incomplete
    Tom Complete
    Tom Complete

    I also created a table called status, but dont know if it's necessary

    5) Status

    Status
    Complete
    Incomplete
    In Progress

    I need a count of how many projects are complete, incomplete, in progress across all three tables

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I personally think it's a normalization mistake to have 3 tables. I'd have 1 with a "type" field or something. If you stick with this design, you'll need to use a UNION query to join them together:

    SELECT Field1, Field2
    FROM Table1
    UNION ALL
    SELECT Field1, Field2
    FROM Table2
    UNION ALL
    SELECT Field1, Field2
    FROM Table3
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 0
    Last Post: 07-19-2012, 02:25 PM
  2. Replies: 2
    Last Post: 08-17-2011, 06:55 AM
  3. Replies: 3
    Last Post: 06-22-2011, 08:51 AM
  4. Multiple Payment Instances
    By luckysarea in forum Queries
    Replies: 3
    Last Post: 04-21-2011, 03:29 PM
  5. Counting across multiple fields
    By shak2 in forum Programming
    Replies: 37
    Last Post: 10-20-2010, 10:20 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