Merging data in Access

Hi,

I have 2 revenue tables in Access:

The fields for Table1 are:
Month
Country
Division
RevenueA

The fields for Table2 are:
Month
Country
Division
RevenueB

Each table has separate divisions.  There is 1 division (DivA) per country in Table1, and either 1 or 2 divisions (DivB and/or DivC) per country in Table 2.  This means that there are up to 7 possible combinations of Divisions for each country:  DivA, DivB, DivC, DivA & DivB, DivA & DivC, DivB & DivC, and DivA & DivB & DivC.

Some countries are represented in both tables, but some are just on Table1 and some are just on Table2. .

I am looking for a query that combines the data from the 2 Tables in the format shown on the attached file.

Thank you.
 
EE-Rev-question.xls
GIStewartAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Sorry, my first example has wrong logic for countries with one division.
Example with correct logic is included.

GrayL: Table2 is in same Excel sheet as Table1
DBMerge.zip
0
 
als315Commented:
Can you upload sample data?
It is not clear for me - is in Division field in both tables always only one division or can be combination?
0
 
GIStewartAuthor Commented:
I hope this helps.  I kept everything in excel for simplicity.
EE-Rev-question-v2.xls
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GRayLCommented:
Can you post the values in both Table1 and Table2 to achieve the desired result in the spreadsheet.
0
 
GRayLCommented:
OK, I think I am seeing the light.  For any given month and country, table1 can only have 1 record, however, table2 may have 1 or 2 records, depending on whether there are 1 or 2 divs in that country - right?  
0
 
GIStewartAuthor Commented:
Yes, that is correct
0
 
als315Commented:
Check this sample. Table1 and Table2- source tables. Divisions - Table with possible combinations of Divisions was created manually (D_CODE field is binary combination of division bit: DivA - 1, DivB - 2, DivC - 4, DivB_DivC- 6, DivA_DivB_DivC - 7; Order - sorting order).
Query6 - result

DBMerge.zip
0
 
GRayLCommented:
Any way you can provide examples of Table1 and Table1 - with dummy data if necessary?  
0
 
GRayLCommented:
GIStewart:  If your tables hold data spanning two calendar years, how do you distinguish same months in different years.  I suspect you original data has a date field.  Could you show us a sample of your original data in both tables,  showing a multi-month sample - especially if there is a month missing somewhere.  Ideally, can you pare the mdb down to the minimum required to support the question, using dummy data if appropriate, do a compact & repair, and then upload it here using File below?
0
 
GIStewartAuthor Commented:
als315 - thank you, this solution worked perfectly
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.