How to Merge Tables From Multiple Sources Into One Master Database in Microsoft Access

In this video, I will show you how to merge the customer tables from two different offices of the same company. They have been using their own copies of the same Access database for years, but now want to merge them together. How do you keep the records unique?

Douglas from Ann Arbor, Michigan (a Platinum Member) asks: I built my company's database using your lessons over the past several years. Each of our three offices uses my Access database, but they all have their own local copy with the data only for that office. Now we want to merge them all together and move the data up to SQL Server Online so we're all working with the same information. How can we do that and keep our customers unique for each office? Should I have Customer1T, Customer2T, and Customer3T? How would that work with forms and reports?

Silver Members and up get access to an Extended Cut of this video. Members will learn how to handle the relationship issues that come up with merging multiple parent tables together. We'll need to pull some additional tricks out of the hat using Union Queries, Double Joins, and lots more.

MEMBERS VIDEO:

BECOME A MEMBER:

LEARN MORE:

PRE-REQUISITES:

RECOMMENDED COURSES:

LINKS:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Ways To Merge Tables In Access Database, How do I combine multiple tables in Access, Can you merge Access databases, How do you merge two tables in Access, How to merge two Access databases, Combine Data from Two Tables, How to Merge two Databases, merge two companies, merge two offices

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[akimumbangoiemax]

From DRC i'm listening and this is the best teacher have had.

[flaminggirl]

I have been waiting for this tutorial for a long time.

[tonyaginter]

Hi Richard. Thank you so much for your excellent tutorials over the years. I cannot tell you how much your instruction has helped me to not only learn the basics of Access, but to build a very impressive database for my office. You have been invaluable. I listed to your instruction on merging tables today, as I'm trying to consolidate a number of tables across our programs that I should have put in one table when I first developed my database. I was successful in following your instruction, but now I'm at a loss as to how to link the new Autonumber ID to my other tables. I think you mentioned that you would go over that in the Extended Cut? What membership would you suggest so that I can ensure I can move forward with the changes I've already made. I tried to run an update query to update the old ID with the new Autonumber, but that didn't work and simply joining the tables via a join relationship doesn't work either. I'm at a loss after multiple attempts. I'm happy to sign up for the recommended membership. Please let me know. Thank You!

[azizullahgullbahar]

Hi. Azizullah your Student from Afghanistan.
I made a Database and nowadays there are many users that are working with it. But there were some problems and bugs they told me. Now I want to update that project and the question is how to update it and inform those who are using my database. The users should download it and use it without losing their data?

[Thomaticus]

Can i mege tables and include matched and unmatched records.

[Stache]

It would have been easier to make a composite field for customer number, location xxx‐customer yyyy forming xxx-yyyy internally

[zmareyshahi]

OFCOURSE it won't work if you have other related tables that are connected to your main form costumer if

[timhavlusch]

I enjoy using access and I am mostly self taught though a lot of trial and error and unfortunately I just don't get enough time in my current position to focus on it as much as I would like. I can go months at a time without getting back to something like this which restarts the whole learning curve again. I am hoping you can point me in the right direction. Which one of videos would provide the best tutorial for what I currently need to track in Access. I have 300 employees that have health benefits in my organization each with a unique ID. Coverage levels range from Single, H&W, P&C and Family. There are 15 Different Subgroups (Groups 1 - 15) that an employee can be assigned based on either job classification (union/non union) and/or a co pay schedule and coverage level and/or based on contribution towards the payment for the benefit plan which best suits their individual needs. What I need to track and verify every month is that my records for all of these individuals for a given month matches the invoice I receive from our Health Benefit provider so I can reconcile it every month and apply the proper adjustments to the bill so it ties out. I have a master table that among other things contains the Unique ID, First & Last Name, the Sub Group assigned or chosen by the employee. I then have one table with all 15 subgroups and the title of each sub group. This table has automatically linked or filtered to the main table since whenever I click on the "plus" sign in front of the record I then see all of the individuals in that sub group. My next step is to create a master table for pricing which lists the subgroup (1-15) the level of coverage (S, H&W, P&C, FAM) and then a monthly premium for that level of coverage in that subgroup. At the end of the day if I have 40 people in one subgroup I want to verify the total bill if 8 are single, 12 are H&W, 5 are P&C and the remaining 15 are FAM. That's where I am missing the key relationship(s).

Thanks in advance.