[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Union Query help

Posted on 2016-08-19
2
Medium Priority
?
70 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 52

Accepted Solution

by:
Gustav Brock earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

649 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