Add field of data to database based off data from another

[samdahlr]

Hi,

I have two customer databases. They have all similar fields and lots of similar contacts but they are not exactly the same. One database has the field "EvenTitle" and the other does not. I want to add "EventTitle" to the other database. Since there are not all of the same contacts I just want the field to contain the "EventTitle's" for each specific contact in the database that does not have that field already. So, I need to lookup everyone in the database that is lacking the field in the database that has the field and then get the event title's for those common contacts into the database without that field. Is there any easy way to do this?

Thank you

[burrina]

Are the databases linked?

[samdahlr]

I am not sure how to link them. What do you mean by that?

[burrina]

I don't know if you want or need to link them, it's your db! You can of course call the db from code and then run your criteria or IF the db's NEED to be linked then do so.
Have you Goggled this? What I mean is if the db's need to share data, then it may be advisable to link the db's to each other. Only you can determine this.

[samdahlr]

How do I link the databases together? I am new to MS access.

Thank you

[June7]

You have two database files or two tables in same file?

If you have two files, then one file can set links to tables in the other file. This is a split database. On the ribbon > External Data > Access > choose Link on the wizard.

[samdahlr]

It is two tables in the same file.

[June7]

That prompts the question of why are there 2 tables and not 1?

Build a query that joins the two tables. Make it an UPDATE query.

[samdahlr]

I cannot join the two tables as then there will be duplicates because there are a lot of common contacts. I want to keep one table as the main table and take data from only one of the second tables fields and add the field to the main table. Or can I do that with an UPDATE query?

[June7]

An UPDATE query is one way to populate a field with data from another table. That's what I was suggesting. One way starts with a join of the tables.

Are you saying contacts are not unique in each table?

[samdahlr]

Some are unique and some are common. I can only fill the field with data for the common contacts. I will post a sample WB.

[samdahlr]

Here is the sample. One problem is that not all of the fields are the same so the data is not easy to match up. If there is a way to tell access to look up the common contacts based off of one field then that field would be the "NPI" field as it is the most consistent for commonalities. CAMS is my main database and I want the Cvent C_EventTitle field to populate the CAMS C_EventTitle field for as money of the CAMS contacts that are in Cvent as possible.

I tried running this query but nothing populates.

UPDATE CAMS
SET C_EventTitle = (select C_EventTitle from Cvent where CAMS.NPI = Cvent.NPI);



Thank you.