Results 1 to 6 of 6
  1. #1
    TXStateMom is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    3

    Convert multivalue fields back to normalized tables

    I have inherited a database written in Access 2010 that has MVF fields in several of the tables. I would like to remove the MVF fields and go back to a many to many relationship with a junction table. I am not very experienced with VBA yet. Is there a way to loop through the records and write a record with the key and each instance of the mutlivalue in individual records? Here is an example of two of the table definitions:

    tblContact
    Contact_ID, autonumber
    Last_Name


    First_Name
    Team_Lookup, number (MVF) Combo box display control

    tblTeam
    Team_ID, autonumber
    Team_Name

    Thank you for any guidance you can give me.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So you need to populate a new TeamID field in tblContact. Method I am not quite sure of. Try building a query that expands the MVF field to separate records and see if that can be used as source for an UPDATE sql.

    http://office.microsoft.com/en-us/ac...33722.aspx#BM7
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TXStateMom is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    3
    Thanks. I created the query with the fields:

    contactID,Team_ID

    Then used the query to export to Excel because a maketable query is a no-no with MVF. I then imported it back to Access, changed the keys to number\long integer and waalahh I have my junction table! I have been fighting with this query and amazed at the simplicity of it. Looking too deep...
    I appreciate your post.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Glad you got it resolved. I now see my final edited post was not quite on track. Of course you needed a new table, not UPDATE of tblContact, duh!

    Couldn't MAKE TABLE with the expanded MVF query?

    How many records were there? Wonder if could have created the junction table then copy/pasted records from the MVF query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    TXStateMom is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    3
    No prob. I have read on other posts that a make table query is not allowed with MVF fields so I didn't bother. The export\import was quick I have a small data set of less than 500. Google MVFs sometime and you will see all the vitriol out there concerning them. They certainly slow things down, that much I have discovered. Thanks again.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Cannot use unexpanded MVF in UPDATE, INSERT, MAKE TABLE but I would expect the expanded query could be used. I am very aware of the negative aspects to MVF and that's why I never have used them.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating friendly forms from normalized tables
    By robsworld78 in forum Forms
    Replies: 4
    Last Post: 06-14-2013, 09:11 AM
  2. Replies: 11
    Last Post: 03-26-2013, 05:34 PM
  3. Replies: 2
    Last Post: 10-22-2012, 05:32 PM
  4. How to display multivalue fields
    By Trojnfn in forum Access
    Replies: 5
    Last Post: 10-22-2012, 03:48 PM
  5. Creating Form from Normalized Tables
    By heathers in forum Forms
    Replies: 2
    Last Post: 09-10-2009, 03:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums