Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    First Normal Form Normalisation (Access 97, Win 2000)

    I have a table listing email users and the mailing groups they belong too (imported from a CSV file created by Exchange 2000 Server). The mailing groups for each user are all listed in a single field. What I want to have is a table linking users to a single mailing group at a time. E.g. to go from:

    | Bob | Sales Mailing List, Widget Mailing List |
    | Carol | Purchasing, Widget Mailing List |

    to:

    | Bob | Sales Mailing List |
    | Bob | Widget Mailing List |
    | Carol | Purchasing Mailing List |
    | Carol | Widget Mailing List |

    where the | indicates the start/end of a field. Does that make sense? I hope so!

    Trawling through various books and websites (including the ever-reliable Lounge!), I've discovered that I need to normalise the table to take it to First Normal Form. However, I can't find a process for achieving this. The process will need to be automated because the CSV file will be regularly re-imported.

    Any ideas or suggestions, please?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: First Normal Form Normalisation (Access 97, Win 2000)

    I don't know of any standard process that will take the structure you have and normalize it. But it isn't terribly hard to do using DAO and the Instr() function in the mailing list field to separate out the multiple entries. If you're not comfortable with recordset manipulation in DAO, you might consider a process in Excel that creates additional rows in a worksheet based on how many entries there are in the mailing group field - the Excel command Data / Text to Columns . . . can separate the mailing groups into different columns, and then create a simple macro in Excel to add a row for each extra column beyond the first and copy the username from the prior row. Once you have the Excel workbook you can import it into an Access table with no difficulty.
    Wendell

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

    Re: First Normal Form Normalisation (Access 97, Win 2000)

    Depending on the original text file, you may be able to import it into a temp table that will have a name column and then a column for each of groups. If you can't, then you will have to do a bit of creative coding to break the big string into groups. You could then do a series of queries to create your normalized table.

    Maybe you could post the first couple of lines of the CSV file?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First Normal Form Normalisation (Access 97, Win 2000)

    I'm trying the DAO route as suggested. It's going well so far. I'll let you know what happens. Thanks for your help!

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: First Normal Form Normalisation (Access 97, Win 2000)

    In addition to Mark's suggestion,
    FYI, somewhere in my closet I have an almost-finished form which could do the conversion from a "user-group1-group2-group3-...." to a "user-GroupNr" table (just adding records for the valid user-group-combinations). Give me a sign if you need such thing. It might be an opportunity to make it, eventually, useful :-).

Posting Permissions

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