Thank you, Allen, for the posting.

I guess our circumstances are not as complex as your sample, as any member
(household) would have at least one handler (individual) and a dog.
(Our definistion of household is a group of people who share the same
residential address.)

My question here is how exactly to put records of similar fields (e.g.
"surname" and "surname_2", "mobile_no" and "mobile_no_2", etc.) from
tblMember and get them in one field ("surname", "mobile_no", etc.) in the new
table (tblHandler), allocate new primary key (Hanlder_ID) and foreign key
(Member_ID) to each handler record.

Should I be using table analyzer first or should I create Make-Table action
query?

I guess the design decision has already been made so the question could have
been better served in "general question" forum rather than "Database design",
in which case, my apology.

--
Maki @ Canberra.AU


"Allen Browne" wrote:

If a dog could be owned by either an individual or a household (consisting
of multiple individuals), you might like to use the structure suggested
he
People in households and companies - Modeling human relationship
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Maki" wrote in message
...
I've been user of Access 2003 for a while and am ok using databases but
somehow lack practical knowledge when it comes to redesigning or
normalizing
existing database and hope someone can guide me step-by-step.

I'm trying to redesign our dog training club membership database.
Out membership is based on household but we have only one set of handler
name, mobile (cell) phone and email fields in our enrolment form and such
was
the case with our database Member Table. Soon we noticed people putting
multiple names, mobile numbers and emails so we added second set of
handler
fields in the Member table to accommodate this.

I decided to normaliise and create a new Handler table, put all surname,
first_name, mobile_no and email records acroos from Member table and link
with Member_ID as foreign key, while each record will have Handler_ID as
the
primary key.

Could someone let me know how to go about from here? I'm planning to do
this at home using Access 2007, although ultimately it will have to be
made
compatible with Access 2003 as that's the version used with our Club
computer.

Thank you.

--
Maki @ Canberra.AU

.