How do you normalize a table?
in [Tables]
|
From: Maki on 20 Jan 2010 02:00 Yes, yes, thank you John. What Allen said "Do you "really" want..." was exactly what we currently have and he also described the kind of problem we are having at the moment! I'll experiment with the Normalizing Union Query that you indicated and report back the results. Thank you, John, Allen, for taking time to respond. Cheers, -- Maki @ Canberra.AU "John W. Vinson" wrote: > 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] > . > |