MultiValue Field

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 DeleteThis @discussions.microsoft.com> wrote in message
> news:DAB4AAB2-E634-4303-9529-C459A067E6EF@microsoft.com...
> >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).
>
>
>

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.


--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


"Scott" <Scott.TakeThisOut@discussions.microsoft.com> wrote in message
news:EDAB1C2B-E479-4D5A-B1E6-D577DB80DCFF@microsoft.com...
> 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.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:DAB4AAB2-E634-4303-9529-C459A067E6EF@microsoft.com...
>> >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).
>>
>>
>>

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

"Roger Carlson" wrote:

> 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.
>
>
> --
> --Roger Carlson
> MS Access MVP
> www.rogersaccesslibrary.com
>
>
> "Scott" <Scott.RemoveThis@discussions.microsoft.com> wrote in message
> news:EDAB1C2B-E479-4D5A-B1E6-D577DB80DCFF@microsoft.com...
> > 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

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" wrote:

> 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.
>
>
> --
> --Roger Carlson
> MS Access MVP
> www.rogersaccesslibrary.com
>
>
> "Scott" <Scott DeleteThis @discussions.microsoft.com> wrote in message
> news:EDAB1C2B-E479-4D5A-B1E6-D577DB80DCFF@microsoft.com...
> > 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

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" <Scott.RemoveThis@discussions.microsoft.com> wrote in message
news:DB95547D-5286-4313-B4B3-D421DAE2DA84@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>>
>> --
>> --Roger Carlson
>> MS Access MVP
>> www.rogersaccesslibrary.com
>>
>>
>> "Scott" <Scott.RemoveThis@discussions.microsoft.com> wrote in message
>> news:EDAB1C2B-E479-4D5A-B1E6-D577DB80DCFF@microsoft.com...
>> > 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

No problem. Thanks for your help.

"Scott" wrote:

> 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" wrote:
>
> > 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.
> >
> >
> > --
> > --Roger Carlson
> > MS Access MVP
> > www.rogersaccesslibrary.com
> >
> >
> > "Scott" <Scott.RemoveThis@discussions.microsoft.com> wrote in message
> > news:EDAB1C2B-E479-4D5A-B1E6-D577DB80DCFF@microsoft.com...
> > > 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