If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MultiValue Field
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). |
#2
|
|||
|
|||
MultiValue Field
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/...UBED1=ACCESS-L "Scott" wrote in message news 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). |
#3
|
|||
|
|||
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/...UBED1=ACCESS-L "Scott" wrote in message news 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). |
#4
|
|||
|
|||
MultiValue Field
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 he http://www.rogersaccesslibrary.com/f...ts.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" wrote in message ... 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/...UBED1=ACCESS-L "Scott" wrote in message news 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). |
#5
|
|||
|
|||
MultiValue Field
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 he http://www.rogersaccesslibrary.com/f...ts.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" wrote in message ... 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 |
#6
|
|||
|
|||
MultiValue Field
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 he http://www.rogersaccesslibrary.com/f...ts.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" wrote in message ... 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 |
#7
|
|||
|
|||
MultiValue Field
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/...UBED1=ACCESS-L "Scott" wrote in message news 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 he http://www.rogersaccesslibrary.com/f...ts.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" wrote in message ... 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 |
#8
|
|||
|
|||
MultiValue Field
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 he http://www.rogersaccesslibrary.com/f...ts.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" wrote in message ... 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 |
#9
|
|||
|
|||
MultiValue Field
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 he http://www.rogersaccesslibrary.com/f...sts.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/...UBED1=ACCESS-L "Scott" wrote in message ... 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 he http://www.rogersaccesslibrary.com/f...ts.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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|