?
Solved

Importing excel data - sum amounts & tables are not related

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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

850 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