Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010

    Unanswered: add values of fields from two different tables

    Hello, I've been trying to resolve this problem without any good result, I would really thank your help.

    Having table A and table B with fields "Project" and "Euros" in each table,

    I'm trying to make a query that will show as a result tree four fields:

    Project - will show the name of the Project.
    Total Euros A - will add all the values of the field Euros in Table A.
    Total Euros B - will add all the values of the field Euros in Table B.
    Total Project - will add Total Euros A + Total Euros B

    * Consider that:
    - most of the Projects will have one or various values in both tables A and B
    - but that others Projects may have many values in one of the tables and none in the other table.

    Thank you for your help!!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    You can use the SQL SUM function in a query:
    1) Query1:
    SELECT SUM (<Field1>) AS Total1
    FROM <Table1>
    WHERE <Condition>
    2) Query2:
    SELECT SUM (<Field1>) AS Total2
    FROM <Table2>
    WHERE <Condition>
    You can create several queries then combine them together in an INSERT INTO query:
    INSERT INTO <Table3> (<FieldA>, <FieldB>)
    SELECT Total1, Total2
    FROM Query1, Query2
    WHERE <Condition>
    It's also possible to process this kind of operation in a single query that combines the SELECT SUM()'s and the INSERT INTO, but Access does not always accept that ("Expression too complex", or something like that).
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts