On Mon, 18 Jan 2010 22:39:01 -0800, Maki
wrote:

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.

I think Allen may have misinterpreted your question: if I understand, you
already have a denormalized table with fields like surname_2 and so on, and
you are (hooray!) normalizing it, right?

If so, I'd suggest creating your new Handler table with all the appropriate
fields (you might want to post the Household and Handler table fields here if
you'ld like more eyes on them). Create it in the table design window, empty;
use an Autonumber for Handler_ID, and a long integer link to Member_ID (which
I'm presuming is itself an autonumber). Use the Relationships Window to
establish the link and to enforce referential integrity.

You can then create a "Normalizing Union Query" based on your Member table.
You need to go to the SQL window for this, you can't do it in the query grid.
It would be *something like* (depending on the actual structure of Members):

SELECT Member_ID, Surname, Forename, Mobile_No, etc
FROM Members
WHERE Surname IS NOT NULL OR Mobile_No IS NOT NULL
UNION ALL
SELECT Member_ID, Surname_2, Forename_2, Mobile_No_2, etc
FROM Members
WHERE Surname_2 IS NOT NULL OR Mobile_No_2 IS NOT NULL
UNION ALL
etc through all the repeating fields

Take a look at this query as a datasheet and see if it captures the
information you want in the Handler table. If so, create a new Append query
based on *this* query, appending Member_ID to Member_ID, Surname to Surname
etc. in the new Handler table. Run the append query.

One concern I'd have here is that my suggestion implicitly links "Surname"
with "Mobile_no", and "Surname_2" with "Mobile_no_2". That might not be the
case! A family might have three people and three cellphones, but you may not
want to make assumptions about which family member uses which phone. I'd
actually suggest storing phone numbers in a new table linked one-to-many to
the Members table, unless you have explicit informaton that lets you link one
phone to one person.
--

John W. Vinson [MVP]