multivalue fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Farmer O - 24 Feb 2008 16:16 GMT

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?

Reply to this Message

Dale Fye - 25 Feb 2008 01:25 GMT

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

> 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?

Reply to this Message

John W. Vinson - 25 Feb 2008 03:32 GMT

>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.

Signature

John W. Vinson [MVP]

Reply to this Message

Farmer O - 25 Feb 2008 14:22 GMT

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

> >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
[quoted text clipped - 15 lines]
> You'ld really be better off designing these tables as *two* tables in a one to
> many relationship.

Reply to this Message