Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2012
    Posts
    5

    Unanswered: MS Access 2003 Query Help

    Hi,

    I am new to the forum and novice with access.

    I am trying to gather all the records from the 2 tables below and if the record is on both tables meaning (id and udf2) are equal then I want to have 1 record and total the contributions from both. Otherwise just report the record from either table.

    for example if the tables include:

    Load:
    ErAssignedID UDF2 total contribution
    12345 001 250.00
    12346 010 100.00

    IA Load
    12345 001 100.00
    12345 010 100.00
    12348 001 100.00

    Result =
    12345 001 350.00
    12345 010 100.00
    12346 010 100.00
    12348 001 100.00

    Any help is appreciated.

    Thank you,
    Louis

    here is what I have so far:


    SELECT ERAssignedID, UDF2, Total_Contribution
    FROM [Load 11 1 12]
    UNION ALL SELECT ERAssignedID, UDF2, Total_Contribution
    FROM [IA Load]
    IF ERAssignedID, UDF2, Total_Contribution [Load] = ERAssignedID, UDF2 [IA Load]
    SUM Total_Contribution [Load] + Total_Contribution [IA Load]
    ORDER BY ERAssignedID;

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Two queries I think would be easiest. First the union:

    SELECT ERAssignedID, UDF2, Total_Contribution
    FROM [Load 11 1 12]
    UNION ALL
    SELECT ERAssignedID, UDF2, Total_Contribution
    FROM [IA Load]

    Then sum them up:

    SELECT ERAssignedID, UDF2, Sum(Total_Contribution) As TotalContribution
    FROM UnionQueryName
    GROUP BY ERAssignedID, UDF2
    Paul

  3. #3
    Join Date
    Nov 2012
    Posts
    5
    Thank you Paul so much for the quick response.

    I have the union working and will test it but am figuring how to run the sum portion.

    _______________
    Louis

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That would be a second query, that uses the union as its base. You would just run the second.
    Paul

  5. #5
    Join Date
    Nov 2012
    Posts
    5
    thank you again... I run the 2nd query and get a syntax error in from clause

    I tried a few things and know it is probably an easy fix..

    I ran the first union then ran the 2nd query seaprately

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Hard to debug a syntax error without seeing the syntax.
    Paul

  7. #7
    Join Date
    Nov 2012
    Posts
    5
    SELECT ERAssignedID, UDF2, Sum(Total_Contribution) AS TotalContribution
    FROM Sum Total Unions
    GROUP BY ERAssignedID, UDF2


    where Sum Total Unions is the union query name

    thanks

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You would have to bracket the query name due the inadvisable spaces in the name, or get rid of them.
    Paul

  9. #9
    Join Date
    Nov 2012
    Posts
    5
    Awesome that worked I and I learned a lot thank you so much.......

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help.
    Paul

Posting Permissions

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