Adding fields to a database with data from another

[samdahlr]

Hi,



I have one large customer database and 3 other smaller customer databases with different fields but a lot of the same contacts. I want to add 2 fields from each of the 3 databases to the large database and pull the data for those fields from the smaller databases, but it needs to be the correct data with respect to the contacts in the larger database. Being that the same contacts are in the smaller databases, is there a query I can run that looks up the contacts from the large database in the smaller database and pulls up the data for the fields I need and then adds them into the large database?

Thank you.

[JoeM]

It may not be necessary to do that. You can link other databases (and tables/queries from other databases) into your large database. They will then be treated like any other table/query in that database, even though the data doesn't physically reside in that database. So then you can use those tables/queries in other queries. So you can link them together and pull what you need.

So there may not be any reason to actually physically import the data to your main table in your large database, since you can access the data you need through linked queries/tables and return what you need.

[samdahlr]

The problem is that I need all the data physically in one large database so I can export it to excel in a csv file. I need it to load into a marketing software for my company.

I am also an 18 year old intern with absolutely no MS Access experience and taught myself the basics of it in a week. So all expertise is much appreciated .

[JoeM]

The problem is that I need all the data physically in one large database so I can export it to excel in a csv file

Actually, you do not. You can export data from a query just as easily as you can from a table.

So all you need to do it:
1. Link the tables from the other databases you need into your main database
2. Create a query linking your different tables and linked tables that returns all the information you need.
3. Export the results of the query to CSV

Physically writing the records to a table first is unnecessary, and increases the risk of error (i.e. if you forget to update the data first, or if the data changes and you forget to run all the updates).

That is the beauty of queries. They are dynamic, so it changes along with your data without you having to physically tell it to.

[samdahlr]

Okay, I am going to try this using a union query. So I will ask it to select all the fields I need from each table and union the data together. This should work correct?

[June7]

That you are an 18-yr old intern who learned enough Access in 1 week to already know about UNION query is really impressive.

Yes, the UNION should accomplish that.

UNION will not display duplicate records. Use UNION ALL if you must allow duplicate records.

[samdahlr]

Lots of online tutorials because I was directed by a co-worker to learn SQL. Thank you for your help!

[June7]

All the more remarkable. You must have a natural aptitude for programming and logical problem solving and quickly grasped relational database concepts. I expect you will do well.

[samdahlr]

Thank you June7!