multivalue fields

[Farmer O]

I'm trying to combine many databases with exactly the same fields, but two of
the fields in one of three tables are multi value fields. I can do two of
the three tables with an "insert. . .union select" statement but that
doesn't work with the multi value fields. How do I combine the tables with
the multi value fields into one table?

 

[Dale Fye]

Ideally, you don't. If you have a multi-value field, you are violating the
1st Normal Form of relational database design (each field should contain
one, and only one, piece of information.

If your data is actually related this way, I would strongly recommend
creating a details table that parses the multi-value fields and inserts them
into the details table.

Dale

 

[John W. Vinson]

On Sun, 24 Feb 2008 08:16:01 -0800, Farmer O <Farmer

I'm trying to combine many databases with exactly the same fields, but two of
the fields in one of three tables are multi value fields. I can do two of
the three tables with an "insert. . .union select" statement but that
doesn't work with the multi value fields. How do I combine the tables with
the multi value fields into one table?

I doubt that it will be possible with a UNION query.

Multivalue fields actually don't exist. Microsoft (many say unwisely) included
them in A2007 as part of an attempt to make the application "easier" for new
users, avoiding the need to understand relational theory - which expressly
prohibits multivalue fields. The way they were included is by having Access
create a carefully concealed table, related one-to-many to the parent table
and containing the multiple values as individual records; however this hidden
table is not exposed to developer or user view, and it cannot easily be
included in such things as UNION queries.

You'ld really be better off designing these tables as *two* tables in a one to
many relationship.

 

[Farmer O]

Thank you John and Dale. Your responses confirmed what I had thought. I
guess when you volunteer for a project you have to deal and work around the
existing parameters . This is the 2nd time in the past 30 years that I have
undertaken this type of project and find it to be a lot of fun.

Thank you again for your time