how to work with a non-normalized database? (A97 SR2)

Topic

New Reply

I’ve been asked to create a tracking database for one of the studies my group is doing, and am running up against the limits of my knowledge as far as how to deal with the non-normalized data file that needs to be included.

About half of the information used for tracking comes from a flat-file, non-normalized Excel worksheet which our SAS programmer sends me. It has fields like MRN_INFANT_1, MRN_INFANT_2, and MRN_INFANT_3 (which will cause trouble if a set of quadruplets comes along during the study), and so on. Some of these essentially-duplicated fields I can ignore, I think. Dealing with the toxicology results, for instance, I’ll leave to the SAS programmer who set up the file in the first place.

Some of them I can’t ignore, though, and I’m wondering how to deal with them. Each infant’s contact information, for instance (we can’t assume that an infant shares contact info with its mother; some don’t). Is there a way to set this up so that a normalized side table can interact with the non-normalized Excel data, or should I grit my teeth and create a mess of a table with fields like INFANT_1_PHONE, INFANT_2_PHONE, INFANT_1_ADDRESS, INFANT_2_ADDRESS and the like?

Any advice or pointers to other references will be most appreciated!

Reply | Quote

Replies

I would suggest you start by shooting the SAS programmer![

Kidding aside, you’ve already identified a major problem with the flat file format–that it can’t accommodate unanticipated data. Is the contact data for each individual infant also repeated in the flat file? If it isn’t, you have a larger proglem.

My suggestion, assuming that all the necessary fields are indeed included in the flat file is that you create normalized tables to hold the data and import it into Access, parsing it into the normalized tables. Don’t try to interact with the Excel data itself, it will drive you crazy and make it extremely difficult, if not impossible, to do the kind of analysis they’re bound to ask for when you least expect it.

What I would do is create a single flat table that has the same fields as the Excel file you receive. Then add some fields to the end of the table structure so they don’t interfere with an import directly into that table. The fields you add should be things like MRN_INFANT_1ID, MRN_INFANT_2ID, etc. You’ll use them to hold the keys created when you parse the individual infant’s record into a table built to hold it. You’ll parse out one at a time, append that infant to the other table, and then do an update query on your flat table matching whatever is in MRN_INFANT_# with the corresponding field in the other table to update the MRN_INFANT_#ID field as appropriate. Once your infant records are created (assuming they’re new–if you’re getting information on infants you already created, you’ll need to do an update query *BEFORE* you add any new ones), you’ll be able to use that infant key to parse any other information associated with that child out to the appropriate tables.

Does that help?

Reply | Quote

Well, I have a meeting set up with another of our SAS programmers to talk about why our datasets are as ugly as they are. (Oops, editorializing again.) I’m hoping that understanding why will make dealing with them a little less frustrating — although not necessarily easier!

Thanks for the advice on importing the data – it sounds like the best approach. And it should require much less Tylenol than trying to deal with the flat file as is…

If the data eventually needs to be flattened out again, I imagine it would mean doing something similar (with append & update queries) but in reverse?

Reply | Quote

I suspect that your SASs database may not be that un-normalized (if there is such a word), but that putting it out to Excel is causing the problem. It may be quite straightfoward to export the data you want into different worksheets, and then import them into Access in a normalized structure. Give it a shot, as it may save you spending hours trying to normalize the data.

Reply | Quote

I would think the simplest way to “flatten” the data, other than using a steamroller, would be to use a crosstab query. But it depends on how your customers (internal, external) want it.

Still, when you chat with the SAS programmers, you might want to find out how they want the data – if they ever need to get it from you.

Good luck!

Tom

Reply | Quote

ok, i get the part about adding the ID fields, but i got lost in the subsequent tables.

What do you mean when you say ‘parse’?

Are you doing this in VBA, SQL, or what?

Reply | Quote