Solved

Union Query help

Posted on 2016-08-19
2
66 Views
Last Modified: 2016-08-20
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
0
Comment
Question by:pdvsa
2 Comments
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41763442
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
0
 

Author Closing Comment

by:pdvsa
ID: 41763460
nice.  very nice.  dummy column gotcha again.  SA is interesting.  Not much to do so all i do is work.  :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question