Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    3

    Question Unanswered: Normalizing imported excel data

    I'm very new to Access, so bear with me.

    We currently have a simple excel spreadsheet that contain error reasons for various pieces of equipment. This spread sheet is to be imported weekly into Access 2007. The error codes are listed vertically and the pieces of equipment horizontally, with the # of errors at the intersection of the column and row.


    Code:
                           Machine 1   Machine 2
    Error Reason 1         1               3
    Error Reason 2         2               8
    Error Reason 3         0               4


    Importing this into Access has been easy, however I would like to find an automated way to replace the error reasons with a foreign key to a "Error Reason" table. The types of error reasons may grow in the future. I was curious as to how this is done? Is there an automatic way to have Access do this (Lookup tables?) or will this require a macro to be written and ran?

    Thanks for any direction / help anyone can provide. I'm new to Access and just starting to wrap my mind around it.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Grrrr. Damned forum ate my first post.

    Short of it is: what you are describing is not normalisation. Funnily enough, your table is not normalised, but for different reasons.

    Normalised design:
    Code:
    error_reason           machine_number     error_count
    ------------------------------------------------------------
    Error Reason 1         1                  1
    Error Reason 2         1                  2
    Error Reason 3         1                  0
    Error Reason 1         2                  3
    Error Reason 2         2                  8
    Error Reason 3         2                  4
    Note that you would probably not include the red line since the information can be inferred by its absence.

    You may wish to have a second table with "Error Reason 1", "Error Reason 2", "Error Reason 3" etc. in there, with a second column of an integer data type, and have that number in the table above instead of "Error Message 1" however it is not necessary and (to emphasise) is nothing to do with normalisation.

    HTH

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you really want to normalize it, you don't want fields for each machine. The normalized structure would be fields for machine, error code and the value. Your example would result in 6 records, not 3 (or 5 if you didn't want a zero record). Presuming the text of the reasons exactly matched that in a lookup table, you could join the 2 tables in an append query to append or update using the key field instead of the text.
    Paul

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Oops, sorry Pootle. This is what happens to slow typists.
    Paul

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No need to apologise of course Paul

    But if you had disagreed....

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Perish the thought!! I would never disagree with you when you're right.
    Paul

  7. #7
    Join Date
    May 2010
    Posts
    3
    pootle_flump: Grr, you're right concerning it not being normalization. I wrote the title too quick.

    I understand what I need, however I don't understand how to do it in Access. If I import the information via an Excel document in the format I described, how can I break it down in that form (Error table, error_code table, machine_code table)? Is this something Access can do automatically, or will I be writing a macro and running it against a temporary table to get it into the form I want?

    Let me know if that's not clear enough!

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Pootle is out flumping it looks like.

    From the sound of it, you would probably import the Excel data into a temporary table, then run append queries on that to pull the data out in the appropriate format into a normalized table. You could either run one append query per machine column or you could create UNION query that "normalized" the data in the temp table and base an append query on that.
    Paul

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, I agree with Paul (again ). A rolls royce solution would be an ETL tool, but that is overkill here. Just load to a staging table and perform your transformations in SQL in to the production tables.

  10. #10
    Join Date
    May 2010
    Posts
    3
    Thanks a bunch, I appreciate it guys!

Posting Permissions

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