473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Need To Create A Master Record

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
Nov 12 '05 #1
10 3029
"Mark" <mm*****@earthlink.net> wrote in message
news:Dn*****************@newsread1.news.atl.earthl ink.net...
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
Nov 12 '05 #2

"Mark" <mm*****@earthlink.net> wrote in message
news:Dn*****************@newsread1.news.atl.earthl ink.net...
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
Nov 12 '05 #3
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
"Fletcher Arnold" <fl****@home.com> wrote in message
news:c0**********@sparta.btinternet.com...
"Mark" <mm*****@earthlink.net> wrote in message
news:Dn*****************@newsread1.news.atl.earthl ink.net...
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

Nov 12 '05 #4
On Tue, 17 Feb 2004 02:35:56 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Nov 12 '05 #5
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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:23********************************@4ax.com...
On Tue, 17 Feb 2004 02:35:56 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Nov 12 '05 #6
On Tue, 17 Feb 2004 13:15:29 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Nov 12 '05 #7
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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:on********************************@4ax.com...
On Tue, 17 Feb 2004 13:15:29 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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 inall 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.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Nov 12 '05 #8
On Tue, 17 Feb 2004 13:15:29 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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?
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Nov 12 '05 #9
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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:8a********************************@4ax.com...
On Tue, 17 Feb 2004 13:15:29 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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?
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Nov 12 '05 #10
On Wed, 18 Feb 2004 00:25:52 GMT, "Mark" <mm*****@earthlink.net>
wrote:
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!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Joop | last post by:
Hello everyone, I'm new on this list and have been working for quite a while on a PHP app that will make an HTML frontend for a PostgreSQL dbms. Works fine so far but I'm having a little trouble...
7
by: Wolfgang Kreuzer | last post by:
Hello all, I have two tables - Projects and ProjectStruct Table Projects contains master records of the projects, ProjectStruct allows to define a project herarchie and contains the fields...
4
by: Jeremiah J. Burton | last post by:
I am trying to create a database (in Acces 2000) to track my fossil collection. I have a main table that has records for every specemen. I have a secondary table with information on localities...
3
by: CSDunn | last post by:
Hello, I have 14 fields on a report that hold integer values. The field names use the following naming convention: T1Number, T2Number ....T14Number. I need to get a 'sub total' of all fields as...
3
by: Mark C | last post by:
I have a unbound form with a tab control with four tabs in an Access 97 database. On each tab I have a sub form each form on the sub forms is bound to its own table. Each table has a field that can...
1
by: HS1 | last post by:
Hello all - I have a master and a details table presented in two datagrid - I also have some text boxes that present the values from any record in the master table. This also helps to enter new...
13
by: paquer | last post by:
Ok' I have 2 tables. The first being my main table where each record has a 'FigureID' txt box. (Indexed - No duplicates) The second being my sub table where reporting is entered for these...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
5
by: blakerrr | last post by:
Hi everyone, I have a form called Master Order with a subform in it called Order Info. The subform has a nested subform in it called Order Details. Master Order cycles through all of the parts...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.