A Query to Merge some tables... I think!

[stuwalsall]

I have a table in Access called contacts which has a whole bunch of names, email addresses, etc. Everyday I have to create a new table with contacts names and match them to the master table. Obviously there has to be an easier way with a query or something, maybe an append query?

The list I get daily has some of contacts from the master table but without the added details, like the emails, telephone, etc. I need to find a way to get those fields to match themselves with the master table and fill themselves out. (It'll cut hours off my workload) Any ideas on how to proceed?

 

[AncientOne]

Forgive me, but this is a rather garbled explanation. What exactly are you trying to do? Append new records to the master table or compare your daily list (let's call it the slave table) to the master table and fill out any missing fields by getting them from the master table?

Please clarify.

 

[stuwalsall]

The master table has 15000 records of names, emails, etc. Every day I get a list of just the names and have to spend the day phoning people and chasing data to get the email addresses and other missing data. At the end of every day the list is imported into a new Access table from Excel and then copied and pasted to the master table. Not the best way of doing it I suppose, but I can live with it.

The thing is I 've noticed that at times the details I'm chasing are already in the master table. so what I'd like to do is create an Access table at the start of the day with the list that just contains the names, use a query that searches the master table for matching names from the list, and adds the email address details to the list.

I figure it'll match around 15% of the list before I even start chasing details.

If you can help, you have my thanks in advance.

Stu

 

[neileg]

How about a find duplicates query? Build this using the new query wizard.

 

[AncientOne]

An update query would be the easiest way to solve this problem. I am not clear how a "find duplicates "query would allow you to update the fields in your daily table.

Anyway, both solutions , to be completely successful, require the presence of primary key fields in both tables.

Can you describe exactly what fields are available to you in each table please.

 

[stuwalsall]

Both tables have exactly the same fields, just the master table has 15000 complete records in it, and the "slave" has 200 incomplete records.

The primary key field, autonumber field, is called ID. The other fields in slave table with data already entered are called Company, Telephone, Fax.

The fields that are empty in slave, but complete in Master, are Position, Firstname, Surname, Email, Notes.

For every list of 200 that I get in the slave table, between 10 and 40 are already in the master list. But I don't know which ones and would prefer Access to just sort that itself.

Stu

 

[neileg]

Sorry, AncientOne, for a less than complete answer. Here is a more considered response.

As I see it, this task has two parts. First to append those that don't exist. Second to decide what to do if the records already exist in the table.

The way I would thing about doing it is to identify those records that don't exist, and add them. I would do this by building an unmatched records query using the wizard and then turning this into an update query to update the master table, and then to a delete query to delete these from the input table. This will leave the records that appear to match what is in the table.

The next step depends on the quality of the data. You could make these reords into an update query, if you were happy that all the changes would be appropriate. Or you could carry out a manual review of those records and decide whether to update the new data or discard it. I would use a find duplicates query to compare the new data with the existing.

 

[AncientOne]

If you have a primary Key field called ID in both tables, you have a very good chance of being able to do what you want with a simple update query.

What I am suggesting would fill in the missing fields in your slave table using data from the main table.

Put the two tables into a query and join them by ID. Drag the fields you want to update into the QBE grid from the slave table. Select "Update Query" from the "Query" menu. In the Update To line for the slave email field put [MasterTableName]![emailFieldName]and so on for each of the fields. THIS WILL UPDATE ALL THE EMAIL FIELDS. If you want to only update null fields, you'll have to put each field in a separate query.

Run the query and all the missing fields that are entered in the master table will appear in the slave. (we hope-run a backup first!)

 

[stuwalsall]

It worked!

I just tested it on a backup I have and it threw in 20 records into a list of 150. This is going to save me a lot of time in future. Thanks.




Stu

 

[neileg]

So which way did you do it?

 

[stuwalsall]

I opted to try the Update query as it was quicker. Because the data flies into an otherwise empty list, it's easy to just look at it and see if anything inappropriate from the master table turned up in it, otherwise I'd have taken the duplicate option, just to play careful.

Thanks tho guys,

Stu O' Walsall