Need To Create A Master Record

[Mark]

I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the exception of a few records missing zip. A
person may be in one to five records in the database. If a person is in multiple
records, the other fields in the table in each record for that person may or may
not contain data. I have two problems:

1. I need to determine which people are in more than one record in the database

2. I need to build a master record for each person who has multiple records in
the database where the master record contains all the data in the database for
that person. This means merging the multiple records into one record. Upon
observation, there is no pattern as to which records contain which data. For
example, the social security number is not always in the second record. So my
conclusion is that the merge has to occur on a field by field basis after
determining which records belong to the same person.

Having a Charles Wilson and a Chuck Wilson is not an issue although they may be
the same person. However, I don't want to have more than one Charles Wilson or
more than one Chuck Wilson unless there are two different people named Charles
Wilson or two different people named Chuck Wilson.

I will appreciate all suggestions on how to clean up this database.

Mark

 

[Fletcher Arnold]

I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the exception of a few records missing zip. A
person may be in one to five records in the database. If a person is in multiple
records, the other fields in the table in each record for that person may or may
not contain data. I have two problems:

1. I need to determine which people are in more than one record in the database

2. I need to build a master record for each person who has multiple records in
the database where the master record contains all the data in the database for
that person. This means merging the multiple records into one record. Upon
observation, there is no pattern as to which records contain which data. For
example, the social security number is not always in the second record. So my
conclusion is that the merge has to occur on a field by field basis after
determining which records belong to the same person.

Having a Charles Wilson and a Chuck Wilson is not an issue although they may be
the same person. However, I don't want to have more than one Charles Wilson or
more than one Chuck Wilson unless there are two different people named Charles
Wilson or two different people named Chuck Wilson.

I will appreciate all suggestions on how to clean up this database.

Mark

On the assumption that you do not want to write your own queries using SQL,
nor write your own code using VBA, a sensible start would be this:

Create a new field named "ID" = Autonumber = Primary Key. You do not have
to pay it any further attention, but you should have that primary key. Next
use the find-duplicates query wizard to find all records where the first
name and the last name are the same. You can then cut, paste and delete
duplicate rows.

If you are looking for, and seriously expect, a more automated solution to
be possible then let us know, but typically these situations need human
interaction. You may have, say 3 people called Chuck Wilson with almost
identical addresses which the human eye can match up immediately and make a
sensible decision about what to keep and what to discard, but getting the
computer to decide may be next to impossible. If one has a social security
number and the other doesn't, then it might be clear what to do, but what if
they conflict.

How many records are we talking about? Run the duplicates query wizard to
find how many first/last name combinations occur more than once and how many
times. This may give you (and us) an idea of the scale of the task - 300
records with 25 duplicate first/last name combinations may be manageable by
hand but 650,000 records with 9000 duplicates is another story.

Fletcher

 

[Fletcher Arnold]

I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the exception of a few records missing zip. A
person may be in one to five records in the database. If a person is in multiple
records, the other fields in the table in each record for that person may or may
not contain data. I have two problems:

1. I need to determine which people are in more than one record in the database

2. I need to build a master record for each person who has multiple records in
the database where the master record contains all the data in the database for
that person. This means merging the multiple records into one record. Upon
observation, there is no pattern as to which records contain which data. For
example, the social security number is not always in the second record. So my
conclusion is that the merge has to occur on a field by field basis after
determining which records belong to the same person.

Having a Charles Wilson and a Chuck Wilson is not an issue although they may be
the same person. However, I don't want to have more than one Charles Wilson or
more than one Chuck Wilson unless there are two different people named Charles
Wilson or two different people named Chuck Wilson.

I will appreciate all suggestions on how to clean up this database.

Mark

On the assumption that you do not want to write your own queries using SQL,
nor write your own code using VBA, a sensible start would be this:

Create a new field named "ID" = Autonumber = Primary Key. You do not have
to pay it any further attention, but you should have that primary key. Next
use the find-duplicates query wizard to find all records where the first
name and the last name are the same. You can then cut, paste and delete
duplicate rows.

If you are looking for, and seriously expect, a more automated solution to
be possible then let us know, but typically these situations need human
interaction. You may have, say 3 people called Chuck Wilson with almost
identical addresses which the human eye can match up immediately and make a
sensible decision about what to keep and what to discard, but getting the
computer to decide may be next to impossible. If one has a social security
number and the other doesn't, then it might be clear what to do, but what if
they conflict.

How many records are we talking about? Run the duplicates query wizard to
find how many first/last name combinations occur more than once and how many
times. This may give you (and us) an idea of the scale of the task - 300
records with 25 duplicate first/last name combinations may be manageable by
hand but 650,000 records with 9000 duplicates is another story.

Fletcher

 

[Mark]

Fletcher,

Thank you for responding!

I ran a duplicates query on the first and last name combination as you
suggested. There are 9653 records in the table. The query returned 2063
duplicate records where there was a mix of 2 to 5 records for the same person.
The query is not updateable so there is nothing you can do directly in the
query.

What do you suggest doing from here?

I appreciate your help!

Mark

 

[John Vinson]

Fletcher,

Thank you for responding!

I ran a duplicates query on the first and last name combination as you
suggested. There are 9653 records in the table. The query returned 2063
duplicate records where there was a mix of 2 to 5 records for the same person.
The query is not updateable so there is nothing you can do directly in the
query.

What do you suggest doing from here?

Use the Unique Values property of the Query to select only one record
for each person. (This assumes - a risky assumption! - that you don't
have two people who happen to have the same first and last name).
Change this query to an Append query and append to a new person table.
If the Person table has an Autonumber ID, you'll get a unique key for
each person.

Then create a second query joining this person table to the 9653
record table, joining by first name to first name, and last name to
last name. Append the ID from the person table and the accessory data
from the big table to a new table, related one to many to the person
table.

 

[Mark]

OK, I did what you suggested and I now have a table of the people from the
original "big table" who had multiple records. The people in this new table
still have multiple records but each record now has an ID which is the same in
all records for the same person.

So now, how do I merge the multiple records for each person into one master
record that contains all the data in the database for that person?

Thanks for your help, John!

Mark

 

[John Vinson]

OK, I did what you suggested and I now have a table of the people from the
original "big table" who had multiple records. The people in this new table
still have multiple records but each record now has an ID which is the same in
all records for the same person.

So now, how do I merge the multiple records for each person into one master
record that contains all the data in the database for that person?

YOU DON'T.

You should be using Access relationally! You'll have *two* tables; one
of them will have data only about the person (no transaction data at
all), one record per person. The other table will have only data about
transactions - no names, no personal data, just a Long Integer foreign
key to link to the Autonumber in the first table.

You would then create a Query by adding these two tables to the query
grid, joining the ID fields. This lets you *display* the data without
storing any data redundantly.

You can also use a Form based on the people table with a Subform based
on the transactions, or a Report/Subreport for printing.

 

[Mark]

John,

I understand the one-to-many relationship that appears to exist here but what I
am trying to achieve is to build a lookup table where there is ne record for
each person. This is part of a conference registration application. The plan is
that when a registration form is received, the registration person first looks
in the database to see if the person is there and if so clicks n the name in the
lookup list and autoenters the majority of the registration information. The
"big table" was compiled from five sources so a person may be in the big table
once or even five times.

Mark

 

[John Vinson]

OK, I did what you suggested and I now have a table of the people from the
original "big table" who had multiple records.

Mark,

My newsreader was unable to download your reply to my "YOU DON'T!"
message. Could you please repost?

 

[Mark]

Sure, John, here it is ----

John,

I understand the one-to-many relationship that appears to exist here but what I
am trying to achieve is to build a lookup table where there is ne record for
each person. This is part of a conference registration application. The plan is
that when a registration form is received, the registration person first looks
in the database to see if the person is there and if so clicks n the name in the
lookup list and autoenters the majority of the registration information. The
"big table" was compiled from five sources so a person may be in the big table
once or even five times.

Mark

 

[John Vinson]

I understand the one-to-many relationship that appears to exist here but what I
am trying to achieve is to build a lookup table where there is ne record for
each person. This is part of a conference registration application. The plan is
that when a registration form is received, the registration person first looks
in the database to see if the person is there and if so clicks n the name in the
lookup list and autoenters the majority of the registration information. The
"big table" was compiled from five sources so a person may be in the big table
once or even five times.

If you have a list of people (in one table or in five) why not fill
all the people into a People table, and use that on your Form (NOT in
a Table Lookup field - a misfeature which should be used *only* by
people who have published at least one book on Access, if then!).

You can use the NotInList event of a form Combo Box to detect a new
name. I'm not at all sure what this query is intended to accomplish
given your explanation!