Fixing Table Design

[bolivartech]

I have just inherited a database with, what I consider, to be a pretty bad design. I'm eventually going to migrate it to a SQL server but before that I need to get it cleaned up.
There are only two tables "Head of Household" and "Family" I will list out the fields.



Head of Household

ID
Last Name
First Name
MI
SS#
Sex
Age
Birthdate
Street
City
State
Zip
Phone
# in Family
Monthly Income
IncAmt1
IncType1
IncFrq1
IncAmt2
IncType2
IncFrq2
IncAmt3
IncType3
IncFrq3

Family

ID
Last Name
First Name
MI
SS#
Sex
Age
Birthdate
IncAmt1
IncType1
IncFrq1
IncAmt2
IncType2
IncFrq2
IncAmt3
IncType3
IncFrq3

What I'd like to do is have one table that holds the household info, another that holds the members(with a field that signifies head), and a final one for incomes. I have been trying different ways with some dummy data but I cannot seem to find an efficient way to do this. The table analyzer doesn't work like I thought it would. Any suggestions on how I should approach this?

[weekend00]

It seems like table "Family" is a part of duplicate in table "head".

I think your plan is fine.

[bolivartech]

Well the problem is I can't seem to figure out how to approach it without losing data or relations. Right now the family table is related to the head of household table by the hoh id.

[weekend00]

I am not sure how data is store in the 2 tables, isn't the data family table included in the head table?

[bolivartech]

Maybe a picture of the relation will help.

[bolivartech]

I think I've found a method that works.

Add HeadofHousehold(Y/N) fields to both tables
Add MI field to Family(Member) table
Change ID of HoH table to HeadofHouseholdID
Append Data from HoH table to Family table
Remove excess fields from HoH table
Create an Income table with amt, type, frq, id, and memberid
Add memberID to Family table
append data from family table to income table

[bolivartech]

Thought I should post that I figured it out. I think I was over complicating it till I started using the append queries. Thanks for the input.
Heres a pic of the final relationship.

[bolivartech]

I'm glad I posted the update, caught the fact I hadn't removed the extra fields from Family yet ;-)