Merging Access Databases

Carlynne
Carlynne used Ask the Experts™
on
Hi,

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.
 

thanks!
carlynne
Database-1.accdb
Database-2.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
There is no builtin utility to do this, if that's what you're asking. Older versions of Access could use Replication, which would do what you're asking, but Replication was not included in 2007. You can build queries that can do some of this work for you, but in the end if you have duplicate Primary Key values you'll most likely end up doing much of this by hand.

To start, I'd link one database to the other, which gives you a single container to work with. You can then write queries which will show you any differences between the two. Access has a query wizard that will build a query showing you Duplicates; this might be helpful in working with this.

You ask how to avoid this in the future - that would depend on your requirements. Do your offices share the same pool of data? Do they then update that pool, or add to it? If you can explain a bit more about what is going on, we can assist further.

Author

Commented:
Hi LSMConsulting,

Thanks for your response.  Because I have already finished the data analysis for this year, let's focus on how I can design my database more efficiently in the future.   We have time to think about this, and I am grateful for your advice.


BACKGROUND
In the past, my company used EXCEL spreadsheets as a "data entry template" that was basically a soft version of the hard copy survey.  You can imagine the headache that this caused when I had a question that had multiple possible responses as each possible response had to have its own column and was coded simply as a yes or no.  As a result, the  entire survey represented in an EXCEL spreadsheet was about a mile long. The other problems I had were with data analysis on a spreadsheet with sometimes thousands of records was quite tedious, not to mention all the data entry errors that people made entering  the data into such a long template.

So, I did a little research decided that it would be more efficient to move to a real database. I thought ACCESS would be good as it didn't seem as complicated as MySQL or another database that requires a lot of programming skill. So, I've just been learning as I've been going and it's been challenging but the amount of time it took me to do the data analysis (even with the problems I had) was considerably reduced compared to doing everything in EXCEL. (And my mouse arm was not as sore).

SITUATION/REQUIREMENTS

My company works with 6 organizations that collect data in the field in a rural impoverished area of the world by conducting an annual survey consisting of approximately 1,200 households. Each organization represents a different geographic region of the country of interest, and each organization conducts about 200 household interviews a year in their respective areas and languages.  

The surveys are then brought back to each field office and the data is entered into an ACCESS database that I designed using forms.  I train the data entry staff on how to enter the data.  I try to keep it as simple as possible as the formal education levels are generally low, and English is not their first language. So, it's important that all data values have numbers associated with them. (eg. 1 = Farm Machinery ;  2= Tractor/Truck) so they can just match the code with the value, rather than understanding the English. The two databases that I sent already represent two of the six total  databases for this year's survey.  

When each group finishes entering the data in the ACCESS database, they email it  to me.  As such, I end up with 6 different databases from each group. The same pool of data is not really shared between offices.  However, I do share the final results of the survey enumerated by geographic region (i.e., township, state) so the different organizations can see the result of their work.  The internet connection in these six offices is not reliable, so an online database with concurrency is not really appropriate.

Also, I don't really want the data entry staff to have too much control over the database because I'm concerned they may inadvertently change the codes or mess up the database. I had this experience with the EXCEL spreadsheets as people figured out how to go in and change the data values in the drop down list, even though I explained why this was problematic. This is one of the reasons I thought the idea of forms would be good.

So far, the data has not really been updated into one large database because previously we just did everything in EXCEL so each year's data was kept  as separate spreadsheets. However, now that we have an actual database, it would be nice to compare 2010 data with 2011 data for the same questions next year.  The survey questions are the same each year, although some changes are made as we get feedback.

I have attached the survey here.

Again, many thanks for your help!

carlynne

SURVEY-FORM.docx
Most Valuable Expert 2012
Top Expert 2014

Commented:
So you're not really "merging" data. To me, "merging" data means that you take the data from Office1.Table1 and compare it to Office2.Table1, and combine the records into one. So if Office1.Table1 has a record with an ID value of 100, you would compare that to a record in Office2.Table1 with the same ID value and combine those values into a SINGLE record in a "master" table.

That doesn't sound like what you want to do. Instead you wish to take the values generated by each office and combine them into a single large table.

You have several options:

1) Define a "starting point" for each office. For example, Office1 would "own" the ID values from 0 to 5000. Office2 would "own" the ID values from 5001 - 10000, and so on. You can "seed" an Autonumber when you ship out the new survey databases so this would be transparent to the end users.

2) You can include a number in the tables that indicates from which office the records originate. For example, in all your tables include a field named "lOfficeID" or something like that. Add a DefaultValue to that field that will flag all those records to a specific office - for example, in the database you ship to Office1, set the Default Value of that field to 1.

Once you do that, you can then combine all those records into one large table in your "master" database, and you'll still be able to maintain uniqueness.

Or perhaps I've completely misunderstood you ;)
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi,

Thanks for your response. I don't know how ACCESS calls it, but I know in SPSS it would be called a "merge" when you add records from one file into another file. So, for example, file_1 has a table with four columns: Age, Sex, Ethnicity, State and then file_2 has the same table with the same four columns but each file has different data, so I want to put all the data into one place so that I can analyze it at once rather than having to analyze the data separately. And so that I can have one large database of all tables and records.

I hope this makes sense.

Carlynne,

I looked into the structure of your db. Yeap, it can be merged but it is so tedious to do it and the output is not worth of the time you spent in doing it. My point here is: it is not only one table we will do the merging but we must do it on all the tables you have in order for the existing queries to work. As I understand you have still other 2 databases that you want to include, so the burden has been added.

Personally, I will not recommend to do it.

Let us find another approach and that is to create a new structure which would just integrate smoothly other db.

Sincerely,
Ed

Author

Commented:
Hi Ed,

Your comments make sense, and this is exactly why I decided to abandon my attempt to merge the data bases because I was working with a short deadline. So, I just decided to do individual queries on each table and then run that query on all the databases. However, as I am not that experienced with ACCESS, I didn't know if I had made the correct decision.  

As I have mentioned, I have completed the analysis this year so I don't have an urgent need to merge them. Now that I have some time to think about this, I was just curious to know if it could have been done.  I just want to know how I can design this better next year so I don't run into the same problem. It will be basically the same exact survey next year.

Best,
Carlynne
"I just want to know how I can design this better next year so I don't run into the same problem."

Don't worry, I find time to design a sample db for this purpose.

Ed

Author

Commented:
You're the best.

carlynne
Carlynne,

This week would be a good time for me to look into your issue.

I started looking how each table of your db relate to one another. I had confusions but if you can provide more info on it, then it would be easier for me design the table relationships.

Can you explain further the importance of each table in your db? I am asking this for we might be able to combine fields of those minor tables into one.

Sincerely,
Ed

Author

Commented:
Hi Ed,

Each table in the database relates to one question in the survey. When there are multiple possible responses to the question, I made a separate table. I am really not stuck on this design. I just did it that way because I didn't know a better way.

My only concern really is that I be able to show or link each response to each question by household, so every person that is interviewed must have a unique id. Last year, I called this the livelihood id.

I think the design has too many tables and could be simplified. I am just not sure how best to do that. So, any insight is greatly appreciated!

Please let me know if you have any more questions!

best,
carlynne
TracyVBA Developer

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial