Results 1 to 4 of 4
  1. #1
    pthomp52 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7

    Combining results from 2 tables

    Hi,


    I have 2 tables and 2 queries from which I want to combine summary fields into one report
    I have table costs, and table payment Schedules. These contain all of the costs allocated to a specific job for which I have built a query and report which shows all of the summary costs of each cost type for one of the many jobs we have. The second is a table of all of the payments either scheduled or paid for one job and I have built a report and query to show the amount to be paid or actually paid. What I want is to combine the Summary of costs and the summary of payments actually paid to see the instant profitability of any job. So I want to set up the query, I assume a cross tab query, to interrogate both tables and make a calculated field (2 actually) to show the profit on a job and the % profit on teh turnover.
    Is that possible? I can't seem to figure it out.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds more like a UNION query is needed to pull together the data from the 2 different tables (or queries).

    SELECT Field1, Field2
    FROM Table1
    UNION ALL
    SELECT Field1, Field2
    FROM Table2

    https://support.microsoft.com/en-us/...0-ad0a75541c6e
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pthomp52 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7
    That seems to append the resulting table with the results from both tables with the amount paid in the same column as the costs. However as I need to make a calculated field that doesn't work in this case. Can I do 2 simple queries with a sum and then do a cross tab query from them both maybe?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I guess it depends on how you want to present the results. You could sum the results of the UNION query to get a net amount per job. If you want to present the costs and payments separately, you could join your 2 queries in a third query that included a list of jobs (in case a job had no costs or payments yet).
    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. Combining 2 Query Results
    By JeffGeorge in forum Queries
    Replies: 1
    Last Post: 06-24-2014, 06:01 PM
  2. Combining results of table
    By jenyfer49507 in forum Access
    Replies: 2
    Last Post: 06-12-2012, 05:23 AM
  3. Combining Results of Cross Tab Queries
    By kkyork in forum Queries
    Replies: 4
    Last Post: 06-06-2012, 09:10 AM
  4. Combining results from unrelated tables
    By jwreding in forum Queries
    Replies: 7
    Last Post: 08-12-2011, 01:19 PM
  5. Combining results
    By LANCE in forum Queries
    Replies: 0
    Last Post: 06-11-2009, 07:38 PM

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