Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Location
    Indianapolis, Indiana, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help Denormalizing a Table (Access 2003 SP2)

    I am familiar with Roger Carlson's Normalizing/Denormalizing method, but it hasn't helped. I am not a VBA person.

    The attached test.mdb contains two tables, tbl_Normal and tbl_Denorm. I have only included 3 IDs and 59 records in these test tables. The real tables will have hundreds of IDs and thousands of records. What we need is to denormalize tbl_Normal into the table structure in tbl_Denorm.

    What it needs to do is to populate the Value field in tbl_Normal to the appropriate Q1, Q2, C1, C2, C3 fields in tbl_Denorm. (The field "None" will have data. These particular IDs have no values in this field.)

    Is this possible? If so, you will have to show us how. Any assistance is greatly appreciated.

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Denormalizing a Table (Access 2003 SP2)

    See the code module in the attached version. To run the code, click anywhere in the procedure Denormalize and press F5.

    Note: in this version, if there are multiple records with the same values in the first four fields in tbl_Normal, the values of None will overwrite one another in tbl_Denorm, so that only the 'last' value will be stored.

  3. #3
    New Lounger
    Join Date
    Aug 2006
    Location
    Indianapolis, Indiana, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Denormalizing a Table (Access 2003 SP2)

    Very nice. I sort of knew that the code would have to do something like what you wrote. I just didn't know how to get there. Thank you.

  4. #4
    New Lounger
    Join Date
    Aug 2006
    Location
    Indianapolis, Indiana, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Denormalizing a Table (Access 2003 SP2)

    HansV,

    It works!! I didn't notice that you had added a combined key to the tables. So...after I fixed our versions of the tables, all was well.

    Could you explain how the fields collection in rstD.Fields(rstN!ValueType) = rstN!Value works? This is very powerful for us. It does exactly what we need for a number of table conversions we have to do now.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Denormalizing a Table (Access 2003 SP2)

    The ValueType field in tbl_Normal contains a text value that is either C1, C2, C3, Q1 or Q2
    The tbl_Denorm table has fields named C1, C2, C3, Q1 and Q2.
    The code loops through the records of rstN based on tbl_Normal. Let's say that value of ValueType in the current record is "C3".
    rstD.Fields(rstN!ValueType) evaluates to rstD.Fields("C3"), in other words it refers to the C3 field in tbl_Denorm. It is equivalent to rstD!C3 but we can't use that since the field name varies from record to record.
    The code assigns the value of rstN!Value to this field.

  6. #6
    New Lounger
    Join Date
    Aug 2006
    Location
    Indianapolis, Indiana, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Denormalizing a Table (Access 2003 SP2)

    Hans,

    I would like to thank you again for your assistance in teaching me how to denormalize a table. I have used it extensively. We even modified it to denormalize three fields in the same table into fifteen fields in the denormalized table.

    This has made our databases much more useful.

    Again, thanks.

Posting Permissions

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