Solved

Importing excel data - sum amounts & tables are not related

Posted on 2016-08-19
3
30 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

729 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