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.
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.
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.
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.
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?
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.
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.