Importing excel data - sum amounts & tables are not related

pdvsa
pdvsa used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
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
pdvsaProject finance

Author

Commented:
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!
pdvsaProject finance

Author

Commented:
Rey,  I need to modify a bit and am about to post another question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial