I would like advice on the best way I can merge two ACCESS databases together. I have attached database_1 and database_2 each with 200 records, so when they are merged they should be 400 records. You will have to hold down shift while you're opening to access the developer view.
The problem is that I was (and still am) a beginner at designing ACCESS databases and I made a design flaw so that it is difficult to merge the two databases together. The design flaw is that although each database has a " Livelihood ID" to identify each household, I can't merge the databases together because they are exact copies of one another except with different data obviously.
So, for example, database_1 and database_2 have the same exact structure and tables, but the data represents different geographic regions of my country of study. Thus, the "unique" Livelihood ID in database_1 is 1-200 and the Livelihood ID is also 1-200 in database_2. Thus, ACCESS won't allow me to merge because there's a primary key conflict.
I have to give the database to groups in different areas and they have limited internet access so ACCESS is really my best solution in case you're wondering why I have ended up with two databases.
As a solution to my merging problem, I have just run the queries on each database and then copied and pasted the results in EXCEL. However, I would like to know if it is possible to merge the databases together, and how do I avoid such problems in the future. For example, if I have different groups entering data in different places based on the same survey , what is the best way to design the database.