Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Table splitting (2003 SP-1)

    I have just inherited a membership database with a very large table. Each member's record contains Name, Address, PhoneNum, etc., plus twenty fields indicating what committees or affiliated organizations he belongs to. However, a member usually populates only 3 or 4 of these fields, so most are empty. Does it make sense to split this table into two? I have read the thread that starts with <post#=307,211>post 307,211</post#> and found my case to be similar. If I follow HansV's recommendation and don't split the table, what else can I do to reduce the empty space?

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table splitting (2003 SP-1)

    Without following Hans' recommendation, there isn't much you can do to eliminate empty space. Splitting the table absolutely makes sense. Query and report design is going to be difficult if you're trying to see which organizations each person belongs to, the only easy thing about that table design you've got now would be filtering by each organization. You're best off in the long run splitting now while records are at the lowest they'll be...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table splitting (2003 SP-1)

    I agree with Jeremy - in this situation you should split the table into two - a members table and a member-committee table.
    If you already have records in the table, it would be possible to use VBA code to transfer the data to the new table(s). If you'd like to do that, you can adapt the code from NormalizeDenormalize on Robger's Access Library (it'll need some modification to fit your table design). If you need help with it, post back with details about the structure of the table.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table splitting (2003 SP-1)

    That posting deals with a one-to-one relationship. What you have described for your membership list is usually represented as a many-to-many relationship. Each (many) of your people can be a member of zero or more (many) different committees. A many-to-many relationship requires three tables:

    tblPeople
    PeopleID - PK - autonumber
    FName
    LName
    etc

    tblCommittee
    CommitteeID - PK - autonumber
    CommitteeName
    etc

    tblPeopleCommittee
    PeopleCommitteeID - PK - autonumber (optional, and subject to debate...)
    PeopleID - long Integer
    CommitteeID - long Integer
    other fields describing the Person's relationship to this committee (e.g. date of appointment)
    etc
    (Create a unique compound index on PeopleID and CommitteeID to prevent duplicatation)

    tblPeopleCommittee will contain one record for every occurrence of a person being on a committee. The conventional way to manage the table is to create a main form for the tblPeople and a subform for tblPeopleCommittee. The subform contains a combo box that uses tblCommittee for its RowSource. If you search the forum you should find plenty of examples of constructing the tables and form.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Reply to all

    Wow, what quick and useful responses! I'll study these, try something and report back. One question for Jacksonmacd - What does PK mean in your ID fields? The rest of your post is quite clear. BTW, the table has 11,000 records, so I'll be making a copy before I try any fixes.
    Thanks to all.
    HH

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reply to all

    PK means Primary Key.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Reply to all

    Howard,

    I just did something similar to your request. I inherited a database that had peoples names and pertinent information as well as different organizations they belonged to in one table with the name, pertinent information, and organization names as column headings. The users were having trouble with queries every time the input a new organization as they had to redo their queries to pick up new data.

    The rules are a person can belong to many organizations or a one to many relationship.

    To solve this we created a Mater Personnel Table to hold names and other pertinent information so it is only input once. Then created a new table to hold the organization data which contained the personnel ID to link back to the personnel table so a one to many relationship could be created. Then only one other column was created named Org to hold the organization data.

    The people could not understand why the data should go "vertical" in one column until they understood how much easier it was for querying.

    HTH
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Table splitting (2003 SP-1)

    You definitely should split the tables, creating that Member-committee table others have mentioned. However, the purpose is to normalize the database, making it more useful, NOT to save space. In fact, it is doubtful you will save any space at all.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Reply to all

    It's good to have this forum back. Silly me for not recognizing PK! Thanks for the link to NormalizeDenormalize. I may have to go that route since I have 11,000 records. My case is almost identical to that described by GaryPSwanson in this thread. I don't understand why jacksonmacd calls this many-to-many. It looks like one-to-many to me.
    Part 2. I understand why spliiting the tables will make the db more efficient and queries easier. What I don't get is why this is "normalizing" the db. I've reread the rules for first, second and third NF and don't see how they apply. There is no redundant data in any record (just a lot of blanks) and there is only one record per member. Is 2NF in play here, referring to the second table?
    HH

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reply to all

    Having 20 committee membership fields is considered to be in violation of the 1st normal form, although not exactly in the way Codd originally stated it. If a record can have multiple attributes of the same type, you shouldn't store the attributes in multiple fields in that record.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Table splitting (2003 SP-1)

    Hi Hans,
    I'm having trouble with NormalizeDenormalize, so I'm taking advantage of your offer to help. I imported my table into Normalize2k, used a MakeTable query to create a new table with the ID and ten fields relating to membership in other orgs. After looking at the code I renamed this table Table2, figuring that's the name of the table it will look for. When I ran the Sub I got an Error 3265, Item not found in this collection.

    Do While Not rs1.EOF
    For i = 1 To FieldCount - 1
    If Not IsNull(rs1("Value" & i)) Then ****** died here.*********
    rs2.AddNew
    rs2!ID = rs1!ID
    rs2!Value = rs1("Value" & i)
    rs2.Update
    End If
    Next i
    rs1.MoveNext
    Loop

    It looks like it choked on "Value" when triying to write a non-null field to the new table. Do I have to let the sub know what my field names are? Where do I enter them?
    Is there any other info I have to give the sub?
    Thanks
    HH

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table splitting (2003 SP-1)

    The expression rs1"(Value" & i) refers to a field named Value1 (or Value2 or Value3 etc., depending on the value of i). Are the fields really named Value1, Value2 etc.? If not, you can use rs1(i) to refer to the i-th field of the recordset starting with i = 0, i.e. the very first field is rs1(i)

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table splitting (2003 SP-1)

    What was the value of i when it crashed? Is there a field called ValueN where N is the value of the crash?

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Table splitting (2003 SP-1)

    OK, I fixed that, but it now stops at the next-to-last statement in the IF loop.

    Do While Not rs1.EOF
    For i = 1 To FieldCount - 1
    If Not IsNull(rs1(i)) Then ' Original Code If Not IsNull(rs1("Value" & i)) Then
    rs2.AddNew
    rs2!ID = rs1!UniqID ' UniqID is name of my ID field
    rs2!Value = rs1(i) ' Original Code rs2!Value = rs1("Value" & i) ******* Dies here.*******
    rs2.Update
    End If
    Next i
    rs1.MoveNext
    Loop

    I know rs2!Value is not correct, but every change I make creates a different error. I am comfortable with the program logic here (updating table1) but really a dunce on VB syntax. What is the correct argument for rs2?
    Thanks for all your help
    HH

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Table splitting (2003 SP-1)

    N was 1. It died because of bad syntax. Now I've gotten a little further. See my reply to HansV earlier today.
    HH

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •