Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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! <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    I would suggest you start by shooting the SAS programmer![ <img src=/S/hosed.gif border=0 alt=hosed width=73 height=24>

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

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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... <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

    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?

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

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

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

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

Posting Permissions

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