Creating normalized DB from denormalized flat file (A2003 SP3)

Topic

New Reply

I am new to the Lounge… although I have been snooping for years. It took me awhile to decide on jumping in.

My task is to collect data from an AS400 database (the administrator sends it as an Excel file) and then import it into an Access DB (a back end). I will access the data from a front end DB.

I need to normalize the flat file and import the fields into separate tables. The flat file has over 79,000 records. It will need to be updated/appended weekly. I have tried using the built in wizards in Access to split the Excel file into tables without the success I desire. I have concluded I will have to design the tables myself and designate the proper PK to FK relationships. The process is a little daunting but I did happen upon this post 306354 about updating table information. Can this process be used to create a new DB (on the first import)?

I found another thread, post 511247, describing the process via the event of a form field. Unfortunately neither post has their attachments anymore

I am not afraid to start the learning process… just need to know if I am headed in the correct direction. Any suggestions are welcome.

Reply | Quote

Replies

Welcome to Woody’s Lounge!

Although it is possible to use code to create a database and to create tables, it is probably more efficient to do this manually, since you have to do it only once. The exact structure of the tables depends very much on the structure of the flat table, it is not something you can automate easily – you need to make decisions based on your knowledge of the meaning of the fields.

After that, you can use VBA code to fill (or update) the new tables from the flat table. Although the general structure of this code is always the same (you’ll find examples in the threads that you mention), the details again depend on the structure of the flat table.

If you would like to receive more detailed help, you’d have to provide information about the flat table.

Reply | Quote

Hans, this is the flat file description I promised.

Reply | Quote

The situation is in fact simpler than I had originally thought. It’s mostly a question of creating lookup tables. This can be done as follows:

– Make a backup of the database!
– Copy and paste the FlatFile table in the database window.
– Name the copy Incidents.

– Create a query in design view based on the FlatFile table.
– Add a single field for which you want a lookup table to the design grid, for example LOCAT.
– Click in an empty part of the upper half of the query window.
– If necessary, activate the Properties window, then set the Unique Values property to Yes.
– Switch to datasheet view to see the result. You should get an ordered list of unique values.
– Switch back to design view.
– Select Query | Make Table Query…
– Specify the name for the table to be created (tblLocationCode in this example), and click OK.
– Select Query | Run… and confirm that you want to add records to the new table.
– Save the query so that you can run it again if necessary.

– Open the new lookup table in design view.
– Rename the field if desired, and make it the Primary Key (this is temporary).
– Add an AutoNumber field. In this example, name it LocationID.
– Save and close the lookup table.

– Open the Incidents table in design view.
– Add a new number (long integer) field with the same name as the AutoNumber field in the new table.
– Save and close the Incidents table.
– Create a new query in design view based on Incidents and the new table.
– Join them on the “old” fields (LOCAT in this example).
– Add the number field (LocationID) from Incidents to the grid.
– Select Query | Update Query.
– In the Update to line, enter tblLocation.LocationID.
– Select Query | Run and confirm.
– Save the query for later reuse.

– Inspect the Incidents table to check that LocationID has been populated.
– Close the incidents table.

– Open the lookup table in design view.
– Make the AutoNumber field the primary key.
– Save and close the lookup table.

– Select Tools | Relationships…
– Add Incidents and the lookup table.
– Drag a line from the AutoNumber field in the lookup table to the corresponding field in incidents.
– Double-click the line and specify that you want to enforce referential integrity.
– Close and save the Relationships window.

– Repeat for the other lookup fields.

I’m not sure I understand why you want RealOffense and RealLocation tables.

Note: in the new Incidents table, you can replace the CCYR, YEAR, CASEMO, CASEDAY, TIME and DOW field by a single Date/Time field.

Reply | Quote

Hans, thanks for the quick response. I have been adding some fictitious data to the test database and on a break I took peek at the Lounge and saw you had responded. You must do these logic teasers a lot. I was getting lost in the jump between the query creation and its conversion. The Access Help file was utterly useless on the make-table subject and several Access developer books I have mention the make-table query, but not the actual step by step process. I will test out your solution ASAP!

The RealLocation and RealOffense fields were created by AS/400 management folks to help narrow down an exact location. Some of our areas are so that they place names within them and all fall within the same LOCCDE. It might be better to pull the RealLocation and RealOffense fields off the LocationCode_ID in the Incidents table. At least that way the person querying the data could compare the Location to the RealLocation data information. A similar issue is involved with the Offense and RealOffense fields.

The CCYR, YEAR, CASEMO, CASEDAY fields I have used to create single concatenated date field in queries, but I had not thought of using the TIME and DOW field in that way. Thinking about that… all those fields concatenated together would create a datestamp for an incident date.

Reply | Quote

Hans,

Been away from the PC for a bit. I thought I would be a lot faster at adding the data… it is going to be a bit slower than I thought. I followed your directions on creating a lookup table, but I kept getting all the fields instead of one field in the resulting lookup table. I finally resorted to examining the creation design via SQL View and discovered that even If I only placed one field in the design grid, Access would force all the fields as a *. I changed the * to the field name and got what you were describing.

Of course, I tried the same approach on the other fields I wanted to make as lookup tables… I got a different problem. I have encountered null values in the data which will not allow the creation of indexes. I have thought about stripping out the nulls to create the structures of the lookup tables, after which I would have to reload the data into tables. Is there an alternative?

BTW, is there a better way of writing a reply into this forum? I am replying online not via email… the online box is not big enough to write more than five sentences…

Reply | Quote

You can use queries with Is Not Null in the criteria row to create lists of lookup values without Nulls.

Go to Edit display preferences; you can set the number of TextArea rows (lines) and columns (characters) there.

Reply | Quote

Edited by HansV to reduce very large screenshot in size

Sorry for the slow response… internet was up and down yesterday. I fleshed up a sample design that will need to be tweaked a lot. Next is to add some dummy data to the flat file (I can not use the existing data in this forum). I made the sample design without data in the flat file. I already see a problem with getting data to flow through to the out laying tables. I am guessing the make-table query is what I am supposed to use, but my attempt at its use are not making lookup tables as I would get in the analyzer wizard. I guess I will have to resort to using VBA.

We are attempting to analyze incident data to determine numbers of incidents at locations, types of incidents to response, how many incidents were reclassified into some more serious… it goes on. I have requested that additional fields be added to the flat file dump. But to answer in part your question about the flat file information I will sent screen shot of its description in another post.

Reply | Quote