Results 1 to 2 of 2
  1. #1
    Liv is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    1

    Exclamation Multiple Unrelated Tables w/Similar Field in Single Query

    I am having a problem as to how to approch this. I have 8 tables with different structures serving different purposes. Each table has a field named "Region". I need to track counts and sums for each table in a single report broken out by "Region". My current (tedious) workaround is creating a query for each region using the DCount and DSum function specifying the region there. Each query is a one line item which in theory I would like to have recorded in a single query.

    What I'm working with...

    Query 1
    Region: "SCAL"
    A Count: DCount(from tblA where "Region" equals "SCAL")
    B Count: DCount(from tblB where "Region" equals "SCAL")...
    A Sum: DSum(from tblA where "Region" equals "SCAL")
    B Sum: DSum(from tblB where "Region" equals "SCAL")...
    returns:
    Region***A Count***B Count***A Sum***B Sum
    SCAL****68*******191******32351***9451

    Query 2
    Region: "NCAL"
    A Count: DCount(from tblA where "Region" equals "NCAL")
    B Count: DCount(from tblB where "Region" equals "NCAL")...
    A Sum: DSum(from tblA where "Region" equals "NCAL")
    B Sum: DSum(from tblB where "Region" equals "NCAL")...
    returns:
    Region***A Count***B Count***A Sum***B Sum


    NCAL****77*******132******41258***7942

    What i would like returned...

    Region***A Count***B Count***A Sum***B Sum
    SCAL****68*******191******32351***9451
    NCAL****77*******132******41258***7942

    Please help on how to approach this. Thank you in advance.

  2. #2
    pdouglas is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Jun 2009
    Posts
    12
    Look at doing a "Union" query to bring things together. This will let you merge things together from several queries into one.
    You'll have to type it in using the SQL window of Query Designer.
    Look up "Union Query" in the Access Help. The Examples & Union Operation pages should help you get started.

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

Similar Threads

  1. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM
  2. Show two tables in single table
    By access in forum Forms
    Replies: 8
    Last Post: 06-11-2009, 11:57 AM
  3. Help reqd with Count of Similar Vals
    By AnthonyT in forum Access
    Replies: 1
    Last Post: 05-18-2009, 12:15 PM
  4. using COUNT for multiple tables
    By kwalt in forum Queries
    Replies: 0
    Last Post: 02-17-2009, 03:06 PM
  5. Replies: 1
    Last Post: 07-06-2007, 08:27 AM

Tags for this Thread

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