Solved

Union Query help

Posted on 2016-08-19
2
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format a text field as percent 12 56
ListData refresh onClick 5 47
Email question 19 89
MS Access vba Print  PDF First page only 16 31
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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