Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Table Splitting (2003-SR3)

    I have a database that needs to be normalized. I had a similar problem two years ago that this forum helped solve, using Normalize/Denormalize (N/D)by Roger Carlson. See <post#=571,051>post 571,051</post#> That problem was fairly straight-forward. I split a table into two tables, in a one-to-many relationship. The fields on the many side were all text and the new two-column table was created using N/D. My new database is for a non-profit that receives contributions. For each donor there are three fields on the "many" sideate, Amount ($), GiftCode (text). These three fields will repeat for each new contribution by the same donor. The current table has room for three sets of contributions and (lucky us) some donors are about to give a fourth. A classic case for normalization.
    As a test, I ran N/D and it produced a two-column table by converting the Date and $ fields to text. So I need another solution. In the earlier thread HansV answered my then hypothetical question about fields with different data types as follows:

    "The idea is to convert a series of similar fields (next to each other) to a single field in a series of records. If you have several series of similar fields, you'll have to create a field for each series, or perhaps even a separate table."

    My problem is I'm not sure how to do this. Hans, are you watching?
    HH

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

    Re: Table Splitting (2003-SR3)

    This situation usually asks for custom code. Could you post a sample database with dummy data so that we have an idea of what you're working with? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Table Splitting (2003-SR3)

    I have attached one zip file, VKillWopr Test.zip, with two databases: VKillWOPR Test.mdb and NormalizeDenormalize Test.mdb. The first is a stripped down version of my working database VKill.mdb, with 15 records. The main table is Contacts. I split off the right side of the table, containing all the contribution info, into table ContactsRight. I then exported ContactsRight to NormalizeDenormalize Test.mdb, changed its name to table2, and executed the Normalize code.
    That produced table1, which is a two-column table, with an Index field and a data field called Value. The Normalize code converted the currency and date fields to text.
    I hope this is enough info.
    Thanks,
    Howard
    Attached Files Attached Files

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

    Re: Table Splitting (2003-SR3)

    Your EventsRight table has 4 contribution fields (currency), 4 contribution date fields (date/time) and only three event fields (string).
    I would create a table tblContributions with 4 fields:

    ContactID (number, long integer)
    Contribution (currency)
    ContributionDate (date/time)
    Event (text)

    Contacts would have a varying number of records in this table - currently up to 4, if there are 4 contributions.
    Does that sound OK to you? If so, I'll try to write the necessary code later on, I'm soon going offline for several hours.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Table Splitting (2003-SR3)

    Hi Hans,
    Thanks for your offer. One correction. The table in question is ContactsRight, not EventsRight. Your proposed tblContributions is exactly right. The reason there are only three Event fields is that some people make contributions that are not in response to a specific event. If it makes your life simpler you can make a fourth event field. I can later populate that with text such as NoEvent.
    I really appreciate your help, since I'm a novice at VBA code.
    Howard

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

    Re: Table Splitting (2003-SR3)

    See the attached code. It assumes that he target table has already been created. The code can easily be expanded to add a record for the pledge amount too.

    I'll post the sample database with the code in my next reply.
    Attached Files Attached Files

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

    Re: Table Splitting (2003-SR3)

    Here is the sample database. The code is in the module basConvert.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Table Splitting (2003-SR3)

    Thanks, Hans. This is exactly what I wanted. The only change I needed to make was to change the currency format to $. Where ar you located? Obviously, somewhere in the Euro zone.
    Best,
    Howard

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

    Re: Table Splitting (2003-SR3)

    I'm in The Netherlands, indeed in the Euro zone.

Posting Permissions

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