MultiValue Field

[Scott]

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

 

[Roger Carlson]

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]

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]

The simpler scenario would ONLY be acceptable if you were downloading the
data for read-only use. If you are going to allow users to enter data, you
MUST use the properly normalized design (ie Many-to-Many). Anything else
would allow the possibility of data anomalies entering your system.

Parsing code of this sort is always tricky. However, here is a routine that
should do something like you are looking for. You may have to modify it for
your circumstances:

'*******************************
Sub NormalizeMultiValueField()
'This routine takes a table with a multivalue field (text)
'and writes it into 3 normalized tables
'in a Many-to-Many structure

'declare variables
Dim db As DAO.Database
Dim rsContactsDownload As DAO.Recordset
Dim rsContacts As DAO.Recordset
Dim rsCategories As DAO.Recordset
Dim rsContactCategory As DAO.Recordset
Dim tmpCategory As String 'temp variable to store category
Dim tmpMultivalue As String

Set db = CurrentDb
Set rsContactsDownload = db.OpenRecordset("ContactsDownloadMultivalue",
dbOpenDynaset)
Set rsContacts = db.OpenRecordset("tblContacts", dbOpenDynaset)
Set rsCategories = db.OpenRecordset("tblCategories", dbOpenDynaset)
Set rsContactCategory = db.OpenRecordset("jtblContactCategory",
dbOpenDynaset)

'loop through each record in the Contacts table
Do While Not rsContactsDownload.EOF
tmpMultivalue = rsContactsDownload!categories
'loop through the values in the Categories field
Do Until InStr(tmpMultivalue, ";") < 1
tmpCategory = Left(tmpMultivalue, InStr(tmpMultivalue, ";") - 1)

'check first value in the Categories multi-value field
'to see if it exists in the tblCategories table
rsCategories.FindFirst ("Category = '" & tmpCategory & "'")
If rsCategories.NoMatch Then
rsCategories.AddNew
rsCategories!Category = tmpCategory
rsCategories.Update
End If

'find category again (in case it was added)
rsCategories.FindFirst ("Category = '" & tmpCategory & "'")

'add new record to the tblContacts table if it doesn't exist
'WARNING: this DOES NOT edit existing contacts.

rsContacts.FindFirst ("ContactID = " & rsContactsDownload!ContactID)
If rsContacts.NoMatch Then
rsContacts.AddNew
rsContacts!ContactID = rsContactsDownload!ContactID
rsContacts!ContactName = rsContactsDownload!ContactName
rsContacts.Update
End If

'add new record to junction table and write foreign key values
rsContactCategory.AddNew
rsContactCategory!ContactID = rsContactsDownload!ContactID
rsContactCategory!CategoryID = rsCategories!CategoryID
rsContactCategory.Update

'remove recently processed category from the multivalue variable
tmpMultivalue = Trim(Mid(tmpMultivalue, InStr(tmpMultivalue, ";") +
1))
Loop

'add single or final record to Category table if it does not exist
rsCategories.FindFirst ("Category = '" & tmpMultivalue & "'")
If rsCategories.NoMatch Then
rsCategories.AddNew
rsCategories!Category = tmpMultivalue
rsCategories.Update
End If

'add single or final record to Contact table if it does not exist
rsContacts.FindFirst ("ContactID = " & rsContactsDownload!ContactID)
If rsContacts.NoMatch Then
rsContacts.AddNew
rsContacts!ContactID = rsContactsDownload!ContactID
rsContacts!ContactName = rsContactsDownload!ContactName
rsContacts.Update
End If

'add single or final record to junction table and write foreign key
values
rsCategories.FindFirst ("Category = '" & tmpMultivalue & "'")
rsContactCategory.AddNew
rsContactCategory!ContactID = rsContactsDownload!ContactID
rsContactCategory!CategoryID = rsCategories!CategoryID
rsContactCategory.Update

rsContactsDownload.MoveNext
Loop
End Sub
'*******************************

If you want to see it in action, I created a small sample database called
"ParseMultivalueFieldTonormalizedStructure.mdb" and put it out on my
website. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=449

I don't often create samples in answer to specific newsgroup questions, but
I thought this one would be of use to many people.

 

[Scott]

Thank you. It may take me a while to get through this, but this is what I
was looking for.

 

[Scott]

Thanks again for your help. I modified the code a bit and it works like a
charm.

Now that you've helped me break apart the multivalue field, can you help me
put it back in reports and forms in the format it was in before I broke it
apart? In other words, I now can do the subform and subreport that lists the
categories for a given contact (and I have the data in tables that provide
more flexibility in getting data to users), but I kind of liked the way the
categories were presented in the original table (networking contact;
supervisor; etc.). I presume (perhaps incorrectly) that, since the
multivalue field has hidden tables beneath it, the fields seen in the table
that shows the multivalue fields must be a relatively simple (but too complex
for me) query. Please don't feel that you have to create a lot more code,
but I'm hoping it is something that is more a calculated field in a query
than code.

 

[Roger Carlson]

Unfortunately, I don't have good news for you here.

The SQL query language is designed to work with normalized data. That's why
it's no good for pulling apart non-normalized data. In the same way, it's
not much good at presenting it in a non-normalized fashion.

To get around this, all of the database implementations (Oracle, SQL Server,
Access, etc) have procedural language solutions. In Access, that would be
Visual Basic for Applications (VBA). So you would have to create a User
Defined Function to build a string out of the values of separate records.

This isn't a trivial problem. First of all, you have to deal with the
possibility of very large strings. What will you do with them in your
report? You also have to program for the possibility of 1) Nulls, 2) only 1
value, and 3) multiple values.

I guess I would envision something that could be called like a Domain
Aggregate function. You would provide the field, table, and a where clause
string, which the function would turn into a SQL statement, then march
through it to build your string.

Perhaps there's something easier, but off hand, I can't think of it.


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

No problem. Thanks for your help.

Thanks again for your help. I modified the code a bit and it works like a
charm.

Now that you've helped me break apart the multivalue field, can you help me
put it back in reports and forms in the format it was in before I broke it
apart? In other words, I now can do the subform and subreport that lists the
categories for a given contact (and I have the data in tables that provide
more flexibility in getting data to users), but I kind of liked the way the
categories were presented in the original table (networking contact;
supervisor; etc.). I presume (perhaps incorrectly) that, since the
multivalue field has hidden tables beneath it, the fields seen in the table
that shows the multivalue fields must be a relatively simple (but too complex
for me) query. Please don't feel that you have to create a lot more code,
but I'm hoping it is something that is more a calculated field in a query
than code.

 

[Roger Carlson]

You know what? I think I found a solution, and it's on my own website!

Fortunately, it's not one of my samples (THAT would be embarassing), but
Duane Hookom has a function that I think will work. It's called "Generic
Function To Concatenate Child Records", and you can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16


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