?
Solved

Importing excel data - sum amounts & tables are not related

Posted on 2016-08-19
3
Medium Priority
?
35 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 2000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

801 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