Union Query help

I have 2 data imports from excel.
I have a union query that correctly sums the data  [ImportAP].[Amount in Local Curr] and [ImportAR].[ExpectedAmontAR] (its groups and shows the balance between these two fields) but now I need to add a separate sum that shows the sum for [ImportAP].[Amount in Local Curr] and  [ImportAR].[ExpectedAmontAR] ...basically showing the amounts the query uses to arrive at the sum balance.  I would need to extra columns to show the separate amounts of ImportAP].[Amount in Local Curr] and  [ImportAR].[ExpectedAmontAR] and I do have these columns in the query I made but the amounts dont appear.  I think you will see where I went wrong once you open the query entitled "doesnt work".  

I have attached a file with the 2 separate imports from excel and the query that correctly sums and the one I have made to sum the separate amounts as described above.  

thank you for your help.
ExcelImport.accdb
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You went half the way as you need dummy values for the "missing" column in both selects in the union query. So try this:
SELECT 
    A.YearMon, 
    Sum(A.SumAmount) AS Total, 
    Sum(A.SumAP) AS SumAP, 
    Sum(A.SumAR) AS SumAR
FROM 
    (SELECT 
        Format([Net due dt],"yyyy mm") AS YearMon, 
        Sum(ImportAP.[Amount in local cur]) AS SumAmount, 
        Sum(ImportAP.[Amount in local cur]) as SumAP, 
        0 As SumAR
    FROM 
        ImportAP
    GROUP BY 
        Format([Net due dt],"yyyy mm")
    Union ALL
    SELECT 
        Format([BegOfWeekDate],"yyyy mm") AS YearMon, 
        Sum(ImportAR.ExpectedAmtRec) AS SumAmount, 
        0 As SumAP, 
        Sum(ImportAR.ExpectedAmtRec) as SumAR
    FROM 
        ImportAR
    GROUP BY 
        Format([BegOfWeekDate],"yyyy mm")
     )  AS A
GROUP BY 
       A.YearMon;

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
nice.  very nice.  dummy column gotcha again.  SA is interesting.  Not much to do so all i do is work.  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.