Re: MultiValue Field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for the thorough response. I understand the table setup, which
confirms what I had in mind as the set of tables that would be needed (and
adds very good detail to it). The data I receive is very reliable in its
data integrity. On the other hand, I don't see that doing the more rigorous
setup is that much more difficult, but I find that I often change my when I
start trying to implement things.

The part that I am having some difficulty with is the parsing of the data
from the table I receive. Can you provide sample code for parsing such data?

Below is a description of how we get the data from the outside source, and
how we use it. Please let me know if I am going down some paths that are
either wrong or unnecessary.

The data from the outside source is downloaded (synched) from the home
office each night to SQL Server (located on a server in our office - what I
refer to below as the big database). That data is used by a home office
managed system (front end) that is on each user's computer in our office to
provide data both for our office use and for the home office. We add/edit
data during the day and it is synched to the home office each night as well.
I can add fields to the big database, up to a limit, but I want to use the
data that is in this big database to use in forms and reports that will need
to have data that the big database is unable to accommodate. Hence the
additional tables to which I want to add (through the Union query) to the
home office tables.

I do not have direct access to the data in SQL server. An IT Specialist who
has direct access has created an Access database (back end - referred to
below as Access back end-1) that links to the SQL Server data. Each morning
he opens Access back end-1 to refresh the data, and then opens a third
database (referred to below as Access back end -2) that imports the data from
the Access back end-1 (see note below). Access back end -2 has the
additional tables that are use by our office for "special" reports and forms.
The imported tables in Access back end-2 are unlinked, but they can be
refreshed as needed throughout the day. Access back end-2 is what the front
end for our office users will link to to create reports and forms that the
big database is not designed to do.

I realize that this is not the most elegant way to manage data, but the fact
that I can't use the SQL server front end to genrerate reports and forms that
are useful to our office users has led me down this path. One of the
problems is that our office users also use the home office front end to add
data to the tables in SQL Server. So, if they want to change data after
looking at a report, theoretically they would have to go into the home office
system to change the data, refresh the data in Access back end-2, and rerun
the report. I intend to get around that by allowing them to change the data
in Access back end-2 (which is unlinked) and have it generate tables of
changed data that can be added to the home office system by a person assigned
to enter data to that system. There would be a disconnect between the two
systems from the time the user changes the data in Access back end-2 to the
time it gets updated by the data enterer, but I don't see how to get around
that.

Note: I intend to add code that parses the data in the categories field to
the code that imports the data.

Thanks for your help. Sorry for the long description. I hope it helps you
understand what I am trying to accomplish.

"Roger Carlson" wrote:

First of all, you are correct. Mulitvalue fields were introduced with
Access 2007 and only in the ACCDB format (not MDB).

Secondly, even if you did have a multivalue field, you'd still have to parse
it programmatically because the multivalue field actually has a many-to-many
relationship with a couple of hidden fields that maintain the data
integrity. You wouldn't be able to simply insert the field value into the
multivalue and have it come out right.

There are a couple of ways to actually solve your problem. One is simpler
but less correct from a normalization standpoint. The other is more
normalized, but harder to implement.

Simpler:
Assuming you trust the data from your external source to be correct, you
could simply have a one-to-many relationship between your contacts table and
your categories table.

Contacts
======
ContactID(pk)
<other fields>

Categories
=======
ContactID (fk)
Category

You would have to parse your semi-colon delimited field with string
functions and programmatically create a new record in the Categories table,
storing the primary key of the Contacts table in the foreign key field, and
then store the value.

Your categories table would look something like this:

ContactID.........Category
101...................networking contact
101...................school director
102...................(etc)

As I said, this has simplicity on its side, but you will be storing
redundant data. Whether this is a problem depends on your business rules,
which I don't know.

Harder:
In this, you would have a many-to-many relationship between Contact and
Categories with a junction table (ContactCategories) with foreign keys to
each of the others.

Contacts
======
ContactID(pk)
<other fields>

ContactCategories
============
ContactID(fk)
CategoryID(fk)

Categories
=======
Category(pk)
Category

To implement this, you would have to parse your semi-colon delimited list,
check each value to see if it exists in the Categories table. If it does,
simply create a new record in ContactCategories, storing the ContactID and
existing CategoryID. If it does not exist in the Categories table, create a
new record, save it, find the record again, then store it's value in the
ContactCategories table along with the ContactID (as before).

Your tables will look something like this:
Categories
=======
CagegoryID.........Category
1...................networking contact
2...................school director
3...................(etc)

ContactCategories
============
ContactID.......CategoryID
101.................1
101.................2
102.................etc

As I said, this is harder to implement, but it is more normalized and you
will have less chance of data anomalies down the road.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



"Scott" <Scott@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DAB4AAB2-E634-4303-9529-C459A067E6EF@xxxxxxxxxxxxxxxx
I saw in earlier posts that using multivalue fields is not recommended and
I
can see why. In any case, I am using Access 2002 and I have no control
over
the version of Access that I use. It is my understanding that creating a
multivalue field is not an option in that version (is that true?).
Unfortunately, I get data from a source on a daily basis that has a
multivalue field and I need to be able to interact with it. I would like
some help understanding how I can do this.

The database from which I import tables (there are a lot of them) is
fairly
complex, with a field called category. Obviously some contacts will fit
several categories. When I get the data the field has entries like
"networking contact; school director", etc. I want to add to this list of
contacts by creating records in a separate table that I then add to the
data
I import each day (the data changes each day, but the structure is static)
by
doing a Union query. I don't use all of the field from the imported
table,
but one of the fields that I would want to include in the union is
categories. I realize that I need to create a separate table for the
categories and then do a Union with the relevant table in the imported
database, but the imported database does not have such a table.

The help I need is to figure out a way to get the data that is now in a
field in the contacts table into a categories table. Or is there a
better/different way to do this? Any suggestions?

Note: I get the table that defines the categories from the imported
database
so I can use that list for my own tables. The number of categories can be
increased (or decreased - but I don't necessarily want to deal with that
yet).



.

Relevant Pages

  • Re: MultiValue Field
    ... Dim rsContactsDownload As DAO.Recordset ... If you want to see it in action, I created a small sample database called ... provide data both for our office use and for the home office. ... Categories with a junction table (ContactCategories) with foreign keys to ...
    (microsoft.public.access.tablesdbdesign)
  • Re: MultiValue Field
    ... If you want to see it in action, I created a small sample database called "ParseMultivalueFieldTonormalizedStructure.mdb" and put it out on my website. ... provide data both for our office use and for the home office. ... Secondly, even if you did have a multivalue field, you'd still have to parse ... Categories with a junction table (ContactCategories) with foreign keys to ...
    (microsoft.public.access.tablesdbdesign)
  • Re: MultiValue Field
    ... Now that you've helped me break apart the multivalue field, ... If you want to see it in action, I created a small sample database called ... provide data both for our office use and for the home office. ... Categories with a junction table (ContactCategories) with foreign keys to ...
    (microsoft.public.access.tablesdbdesign)
  • Re: MultiValue Field
    ... Now that you've helped me break apart the multivalue field, ... Dim rsContactsDownload As DAO.Recordset ... If you want to see it in action, I created a small sample database ... provide data both for our office use and for the home office. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Cant delete table "You do not have the necessary permisions to us
    ... I was able to create a SQL Data Definition query for each of the tables I ... opens, it imports three tables from a user-level secured backend database, ... I am using the database as the database owner and I (and all the Full Data ... Permissions dialogue box. ...
    (microsoft.public.access.security)