Solved

Importing excel data - sum amounts & tables are not related

Posted on 2016-08-19
3
29 Views
Last Modified: 2016-08-19
I have 2 data imports from excel.
I am trying to figure out a way to combine these two data sets and show a SUM for each MONTH
but the tables have no relation between them (other than date fields)

How could i combine these two data sets and show a balance for each month?  

I have attached a file with the 2 separate imports.  I am looking for better ideas than the one I have.  Your help is appreciated.

thank you
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
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41762914
try this query

Select A.YearMon, Sum(A.SumAmount) As Total
From
(
SELECT Format([Net due dt],"yyyy mm") AS YearMon, Sum(ImportAP.[Amount in local cur]) AS SumAmount
FROM ImportAP
GROUP BY Format([Net due dt],"yyyy mm")
Union ALL
SELECT Format([BegOfWeekDate],"yyyy mm") AS YearMon, Sum(ImportAR.ExpectedAmtRec) AS SumAmount
FROM ImportAR
GROUP BY Format([BegOfWeekDate],"yyyy mm")
) As A
Group by A.YearMon
0
 

Author Closing Comment

by:pdvsa
ID: 41762948
Very nice as usual.  I checked the numbers and it was spot on.   I thought the solution might have been a union.   I need to learn more about them.  thank you sir!
0
 

Author Comment

by:pdvsa
ID: 41763372
Rey,  I need to modify a bit and am about to post another question.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Form performance table vs. query 27 67
access vba 5 55
Want my table columns to autofit. 7 59
Access 2003, find all instances of database ODBC 3 46
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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