Merging data from two separate records into a single record.

[Jerry Call]

One of the programs in the office where I worked is an Access database. I use Access in my own work, but I have no permission or ability to make any changes in this database. So I'm not able to send you a copy of it.
But an issue has come up for which I think you can provide some guidance, which I could then pass on to whomever we can find to come in and work with the problem.


Our database contains records for a large number of clients for whom our company provides home care, senior transportation, meals on wheels, and other services for seniors over 60.
Each client record contains a large number of fields.

By mistake we have managed to enter two different records for the same client. One is under the name of Alberto Gonzales, the other under the name of Alberto Gonzalez Sr. Thus, the two differ in that the surname of one
ends with an "s," the other with a "z", and the second record also adds "Sr."

The "s" record includes earlier information on the client, while the "z Sr" record has later information.

We would like to consolidate these two records into a single record. Quick Books lets folks do this kind of thing in its interface, but I'm in doubt how it would be done with Access.
I think I can see the basic strategy: Make a query with SELECT ALL for all records whose first name is Alberto and whose last name is Gonzales.
Then SELECT ALL records where first name is Alberto and last name is Alberto and whose last name is Gonzalez Sr.
Then do a UNION, right?

OK, that would give us the desired data in query form. But then how to pour that data into a single new record whose first name is Alberto and last name = Gonzalez Sr.

Remember, I won't be able to actually do any of this at the office, but if I understand how to make it happen I may be able to help whatever technician we bring in to help us.
Plus, of course, I would learn some new secrets of the Access kingdom that could help me in some of my own Access apps.

AccessForums has proved to be a terrific help to me in the past. Thanks for your patience with novices. And I promise not to respond: "That doesn't work."

[ranman256]

use 2 queries 1 for each record, (joined on some field if needed), then run an update/append

[Bulzie]

Few questions.
1. How do you know those are the same person(many folks have same or similar names)?
2. Do you have a Client ID for each?
3. Is that Client table linked to other tables for the other services?
4. Are you planning on combining those records manually or looking for some automated process?

If you do not have a unique ClientID field to link those records, then might be better to manually update the record so you are sure of the data. So maybe just sort the table by Last Name, First Name, DOB, and eyeball the duplicates?

[Jerry Call]

1. No question about it: Alberto Gonsales and Alberto Gonzalez Sr. are the very same person.
2. Each has a different client ID.
3. I think the Client table is linked to other tables, but can't be sure, since I can't get into the code.
4. Since there's just this one instance, I'd prefer to just do this job manually; no need for a macro.

[Jerry Call]

Give me a SELECT statement for the process. Remember, there's only one table involved here, so I envisioned doing a UNION between two SELECT ALL statements,
one for Gonzalez Sr. and the other for Gonzales. The resulting query would presumably include all the data for both of our phantom doubles
But I don't uncerstand how those query results for the two combined records are to be merged into a single record in the name of Alberto Gonzalez, Sr.
which includes all the data in both records. The program interface does not permit us to delete any records, but it would be easy enough to insert a note
in the head of the "bad" Gonzales record that says "Don't use."

[CJ_London]

For a combined view you just need a simple select query

Select •
From mytable
Where firstname=‘Alberto’ and ((clientname=‘gonzalez’) or (clientname=‘Gonzales’))

But simpler to use id’s if you have them


To copy records to same table but different name, use an append query, selecting the records from the client and appending them to the same table- don’t forget to change the name to the correct one

Again batter to use Id

Surprised this is only one table, in my experience with health care there are normally several associated tables -care notes, medication, carers and excluded carers to name a few. These will all be managed in separate linked tables

Personally I would leave the job to the technician because if there are these others tables, they will need to be managed with care, the order in which you do things is very important

[Jerry Call]

There are unique numerical IDs for each client, so that will probably simplify the work.

Our services are all non-medical, e.g. cleaning, meal preparation, take out trash, vacuum floors, go shopping, etc.
We also have Meals on Wheels clients and Senior Transportation clients, but Mr. Gonzalez Sr. is only
involved with the home care items. However, there are indeed many fields that we have to fill out
for home care clients; they're accessed via several forms.

I realize you're limited in what you can tell me without looking at the app in your shop.
Let's close this thread off now. I believe I have a good start on how to work with the
technician when we finally get one.

Unfortunately, I still can't figure out how I mark a thread as "solved." There must be a button somewhere that can be
pushed, but I can't find it. Why don't you put a "Solved" button in the row at the top of this screen?

[June7]

Thread Tools dropdown above first post. Done.