Restructure existing database to move fields from one table to another

[Ken]

Main
ID
First Name
Last Name
etc

People
BioID
BirthDate
DeathDate

The two have a one to many relationship and I have a form that uses
both tables. When I create a new record it uses the Main Name fields.
And a corresponding record is create for People based on the
ID<=>BioID relationship. Unfortunately, I get duplicate names in Main
and duplicate records in Bio.

Unfortunately I got it backwards as I should have the First and Last
Name only one time in People to be a one to many in Main. (i.e. The
Main table would have multiple occurences using the name.)

Here is what I want to do:
Have People table to only have unique records-one per name (First Name
and Last Name key?)

What would constitute the relationship since the People table key
would be based on two fields? Or should it? I need help with what each
table's key should be.

How can I restructure the database? How can I get the name fields to
be only one time in People? I can run a query to show ID, BioID and
the name fields together but how can I add or append the names to the
People table?

Should I save the Main table to another name (People1) and then delete
all fields but the name fields. Then go in and delete the duplicates
and set the key to no dupes based on first and last name fields? I
have about 500 records.

All of my queries and most forms use the Main First and Last Name
fields so I would need to revise them.

 

[John Vinson]

Unfortunately I got it backwards as I should have the First and Last
Name only one time in People to be a one to many in Main. (i.e. The
Main table would have multiple occurences using the name.)

The First and Last names should exist ONLY in the People table, not in
any other table.

Here is what I want to do:
Have People table to only have unique records-one per name (First Name
and Last Name key?)

Names are NOT unique. I've got three friends (a father and son, and
someone unrelated to either) named Fred Brown. They do NOT make good
keys!

What would constitute the relationship since the People table key
would be based on two fields? Or should it? I need help with what each
table's key should be.

An Autonumber or manually maintained meaningless PersonID.

How can I restructure the database? How can I get the name fields to
be only one time in People? I can run a query to show ID, BioID and
the name fields together but how can I add or append the names to the
People table?

By opening the People table on a form and entering a name.

Should I save the Main table to another name (People1) and then delete
all fields but the name fields. Then go in and delete the duplicates
and set the key to no dupes based on first and last name fields? I
have about 500 records.

I wouldn't do it that way - instead, create two new tables, and run
Append queries to populate them. You can use SELECT DISTINCT on the
one to populate the People table (assuming you don't - YET! - have any
Fred Browns in the table).

All of my queries and most forms use the Main First and Last Name
fields so I would need to revise them.

Probably only a bit - you'll still have the first and last name
fields, they'll just be pulled from the People table not from the Main
table.

 

[Ken]

Thanks John,
SOmehow with update query I GOT the names to show up correctly in the
People table. They still seem to be linked correctly to the Main table
records via the ID autonumber field. The only problem is that I have
dupes in the People table since there were dupe names in the Main
table. Any way to easily get rid of the dupes without messing up the
links? Should I find the dupes in the Main table and delete them using
the form and then re-enter.

Ken