• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Change table or field in MS Access 2010 table from multi-valued to normal table or field.

I have table with several multi-valued fields. Table is populated with data.
I want to change this table that those fields are not multi –valued .
How to do that?
I tried to create the table with same fields and append  multi-valued field table to this not multi-valued but it will not allow me to do it get error that source or destination of query could not be multi-valued table.
0
Taras
Asked:
Taras
2 Solutions
 
EirmanChief Operations ManagerCommented:
According to page 189 of this online book, you cannot change back
http://books.google.ie/books?id=7SZ5r5F6n9QC&pg=PA189&lpg=PA189&dq=change+from+multi+value+fields+to&source=bl&ots=Ni0y7-inJ0&sig=BVpBJ7KRwRg_XQfdQXdKcxW4j50&hl=en&sa=X&ei=9GJaVJq2J8iP7AaSqIGwDw&ved=0CCwQ6AEwAg#v=onepage&q=change%20from%20multi%20value%20fields%20to&f=false

Multi value fields effectively create many to many relationships using hidden tables.
If you could somehow access the hidden tables (I think there is an option for that) it might be possible interact with them.
0
 
PatHartmanCommented:
I can't give you the queries you need but I can outline the procedure.
1. Start with a query that selects all the values of the MVF for all the rows.  Group by the field and change the query to a make table query.  This will make the "lookup" table for you.
2. select all the values for the MVF again and this time include the PK of the parent record.  Change this query to be a make table query.  This will make the junction table for you.

Create relationships and test thoroughly.  Once you are certain that everything works, delete the MVF field from the original table.
0
 
TarasAuthor Commented:
Thanks a lot.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now