separating data into new table

[Guest]

I currently have inherited an Access database with a People table that has
First Name, Last Name, Second First Name, Second Last Name and Address
fields. Each person that visits our facility has a separate record in this
database- so John Smith has a record with his address and Jane Smith has a
record with the same address, and their names are cross referenced in the
Second Name fields.

Of course this presents a problem when creating mailing lists, because we
have multiple records with the same address.

I would like to run a query that would add a field and autonumber by address
(records with the same address would have the same number), with the intent
of separating the tables into a People table and a Household table, connected
by a one to many relationship via the Household ID.

Could anyone suggest the code to do this, as well as tips on potential
problems? (This is a 12,000 record database, so no small thing).

Thanks, Amanda

 

[Jeff Boyce]

Amanda

Are you quite confident that you have no more than one John and one Jane
Smith? Trying to cross-reference by name alone is a risky undertaking.
Even using address is no guarantee, as John & Jane may have two children
named John & Jane, all four of them living at the same address.

If you have a way to uniquely identify each person (no, not a Social
Security Number -- think identity theft), and a way to uniquely identify
addresses (no, not the street address -- how many towns have a "Main"
street?!), you could create a third table that shows which person (PersonID)
goes with which address (AddressID). And this design let's you record the
fact that some folks have more than one address.

If you need to associate one person with another, a small (narrow) fourth
table would do. If you wanted to keep relationship info ("is a spouse of",
"is a child of", "is a sibling of", "unrelated but living in same
household", ...), your table would look something like:

trelRelationships
PersonID1
PersonID2
RelationshipID (use a "lookup" table to hold valid relationships)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You're right! Trying to cross-reference by the second name would indeed be a
risky undertaking. The process of cross-referencing has not at all been
consistent. So the second name cross-reference is fairly irrelevant for what
I'm trying to do.

I'm not very concerned about tracking multiple addresses for the same
person, but regardless of whether I use two tables or three, I will need to
create a new field and populate it with an ID number that will become the
Household ID, and the only way to get started is by running a query that
sorts by address, and then autonumbers the records based on whether the
address is the same as the prevous record or not.

how could I go about building such a query?

thanks again, Amanda

 

[Jeff Boyce]

Amanda

Here's another approach...

Create a query against the table holding the addresses. Select the address
field(s) and change the properties to get unique addresses.

Create a new address table, and include an AutonumberID field as your
primary key.

Now append the (unique) addresses to the new Address table. They each have
a unique ID value. Check twice before proceeding, as the following may
actually be the same address, spelled differently:

1234 Elm St.
1234 Elm Street
1243 Elm
1234 Elm St SW
1234 Elm Ave
...

You might have to go back to the original table holding addresses and do
cleanup to get the addresses consistent.

When all that's done, you can use your new Address table to help you select
all the "household members" sharing that address by joining it back to your
original ?person? table. If you are creating a table for HouseholdMembers,
you could use this query/join to populate it much like you had populated the
above (new) Address table.

Regards

Jeff Boyce
Microsoft Office/Access MVP