none
Survey Checkbox

    Question

  • I have two tables connected by a primary key.  Table 1 contains customer data, Table 2 contains Yes/No survey data fields: Example: Rock, Pop, Jazz.  I am trying to find a way to update a field in Table 1 with a concatenated list of what the customer filled out on the survey.  The trick is the fields in Table 2 are Yes or No.  I need to be able to convert Yes to the type of music the field is for, and then only concatenate the fields that are marked Yes.  Each field would need to be separated by a space and comma.  Table 2.Rock = Yes

    Table2.Pop = Yes

    Table2.Jazz = No

    Table2.Blues = Yes

    Update Table 1 with concatenated Table 2 yes field names

    Here is what the LikesMusic field in Table 1 would look like after the update:

    Table1.LikesMusic = Rock, Pop, Blues

    Any ideas on how I can do this? Thanks in advance, Cameron

    Wednesday, February 27, 2013 8:16 PM

Answers

  • Hi CameronCameronCameron,

    This code do what you are asking

    Dim rec_tb1 As Recordset
    Dim rec_tb2 As Recordset
    Dim tmp_str As String
        
        Set rec_tb1 = CurrentDb.OpenRecordset("select * from table1", dbOpenDynaset)
        Do While Not rec_tb1.EOF
            tmp_str = ""
            Set rec_tb2 = CurrentDb.OpenRecordset("select * from table2 where FK=" & rec_tb1!PK, dbOpenDynaset)
            For i = 0 To rec_tb2.Fields.Count - 1
                If rec_tb2.Fields(i).Name <> "FK" And rec_tb2.Fields(i).Value Then
                    tmp_str = tmp_str & rec_tb2.Fields(i).Name & ", "
                End If
            Next i
            tmp_str = Left(tmp_str, Len(tmp_str) - 2)
            rec_tb1.Edit
            rec_tb1!field_2_be_updated = tmp_str
            rec_tb1.Update
            rec_tb1.MoveNext
        Loop
        rec_tb1.Close
        rec_tb2.Close
        MsgBox "Done"

    I called the primary key in table1 PK and in table2 FK. I named the field that'll contain the results in table1 field_2_be_updated. Change the names in the code accordingly to your real situation.

    HTH Paolo

    Thursday, February 28, 2013 2:55 PM

All replies

  • I believe this can be done, using a function that opens a recordset on the matching Table2 record and processes its Fields collection, but really you would do better to change your table design so that you have a table of Genres (maybe), with one record for each genre of music, and a separate table of CustomersGenres, with one record for each customer/genre combination. 

    Would you be open to redisigning your tables?  It's a bit tricky to set up a survey using a normalized structure, but Duane Hookom's "At Your Survey" sample database shows a good way to do it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 27, 2013 8:28 PM
  • I would use a lookup field from Table1 creating a Multi-Select cbo. Then be done with it.


    Chris Ward

    Wednesday, February 27, 2013 8:34 PM
  • I will take a look at Duane Hookom's sample DB.  I can't modify Table 1 (Customer Data table) all that much because there are thousands of records and there are reports that pull off that table for ordering Music CD's every month.  Table 2 is part of my new survey input design which I am building to make survey data input much easier via a check box system instead of manually typing out each genre in one field.  Table 2 is built out with a field for each genre, however they are yes /no fields.  If there is a better table 2 design I am all for it as long as I can convert table 2 data into a single field in table 1. 

    Thank you, Cameron

    Wednesday, February 27, 2013 9:40 PM
  • I will take a look at Duane Hookom's sample DB.  I can't modify Table 1 (Customer Data table) all that much because there are thousands of records and there are reports that pull off that table for ordering Music CD's every month.  Table 2 is part of my new survey input design which I am building to make survey data input much easier via a check box system instead of manually typing out each genre in one field.  Table 2 is built out with a field for each genre, however they are yes /no fields.  If there is a better table 2 design I am all for it as long as I can convert table 2 data into a single field in table 1. 

    You can certainly come up with a better survey interface than having people type in a the name of each genre.  Yes, you can even do it with check boxes.

    I'm not 100% sure that Duane's survey database is exactly what you need, but it's certainly a good place to start.  You should be able to download it from here:

        http://www.rogersaccesslibrary.com/forum/topic3.html

    As an aside, if you are using Access 2007 or later, you could potentially use a multivalue field as Chris Ward is suggesting.  I wouldn't do it that way myself, but it may be simpler.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 27, 2013 9:49 PM
  • The multivalue field is the answer, unfortunately I am using access 2003.  I won't have a problem upgrading 1 pc, but I won't be able to update the other 5-9 other users that use my access databases.  I may just be stuck doing the survey work myself or at times allowing others to use my pc to do the work. 

    I am sure there is another way to do this in access 2003, but it certainly won't be a snap of a finger like using the multivalue field and it will be more complex needing multiple tables and queries to do the same thing with the chance of problems. I am still in shock I can't have a multivalue field in access 2003.  I haven't been doing much developing lately and have forgotten.

    Thank you Dirk and Chris

    Thursday, February 28, 2013 2:00 PM
  • Chris, that can only be done in 2007 and newer correct?  "I am still in shock I can't have a multivalue field in access 2003.  I haven't been doing much developing lately and have forgotten."  Cameron
    Thursday, February 28, 2013 2:02 PM
  • Hi CameronCameronCameron,

    This code do what you are asking

    Dim rec_tb1 As Recordset
    Dim rec_tb2 As Recordset
    Dim tmp_str As String
        
        Set rec_tb1 = CurrentDb.OpenRecordset("select * from table1", dbOpenDynaset)
        Do While Not rec_tb1.EOF
            tmp_str = ""
            Set rec_tb2 = CurrentDb.OpenRecordset("select * from table2 where FK=" & rec_tb1!PK, dbOpenDynaset)
            For i = 0 To rec_tb2.Fields.Count - 1
                If rec_tb2.Fields(i).Name <> "FK" And rec_tb2.Fields(i).Value Then
                    tmp_str = tmp_str & rec_tb2.Fields(i).Name & ", "
                End If
            Next i
            tmp_str = Left(tmp_str, Len(tmp_str) - 2)
            rec_tb1.Edit
            rec_tb1!field_2_be_updated = tmp_str
            rec_tb1.Update
            rec_tb1.MoveNext
        Loop
        rec_tb1.Close
        rec_tb2.Close
        MsgBox "Done"

    I called the primary key in table1 PK and in table2 FK. I named the field that'll contain the results in table1 field_2_be_updated. Change the names in the code accordingly to your real situation.

    HTH Paolo

    Thursday, February 28, 2013 2:55 PM
  • Paolo has gone ahead and written for you the sort of code I alluded to in my first reply.  He's got it set up to update a text field in the parent table, while I would probably have done it as a function to be called in a query to return a calculated field -- thus avoiding the storing of non-relational, redundant data -- but the principle and technique is what I had in mind.

    That said, it would still be better to use a more normalized, relational structure for the data, and you could *still* have a nice check-box interface.  All A2007's multivalue fields do is create such a relational structure with hidden tables, and present a nice built-in UI for dealing with them.  You can do it with A2003, but you have to build your own tables and UI.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, February 28, 2013 3:14 PM
  • Chris, that can only be done in 2007 and newer correct?  "I am still in shock I can't have a multivalue field in access 2003.  I haven't been doing much developing lately and have forgotten."  Cameron

    Since you mentioned you could get A2007 on 1 machine, I would do it on your Machine for development. You can then roll out a Runtime version to the Other Machines in A2007.

    Having said that, Other layouts might be better like a Multi-Select listbox. You could make the listbox as large as needed to view it all. And I like what Paola and Dirk are talking about. They have a good grasp on Database. I just learn as I go.


    Chris Ward



    • Edited by KCDW Thursday, February 28, 2013 3:31 PM
    Thursday, February 28, 2013 3:27 PM
  • As a follow up to this post.  I have since upgraded to Access 2013 and built out the use of the multi-value field.  I had a problem the first time around but the second try at converting my database to an .accdb (2013 version) from a .mdb and then converting the fields to Short-Text which then allowed me to chenge the properties to "Allow Multiple Values".  I had to open some sub-forms first prior to opening the main form, which allowed the form to not display an error messge.  Other than that, the multi-value fields are working and saving me hours of time each month.  My next feat will to actually put the survey online so that it can be filled out by the customer and I get the data and can just upload the data saving me many times the hours each month.  If anyone has a suggestion to best convert this to an online based survey (secure of course) in a format easily imported into a db then send the info my way.  Thank you.

    Cameron

    Tuesday, December 17, 2013 3:29 PM
  • Hmm. If you are looking at converting this to a Web App, the Tables will be fine but you cannot convert a Desktop database to a Web App. You will have to build that from scratch. Also you will not be able to use VBA in the Web App. You can still use Macros and Table Triggers.

    Chris Ward

    Tuesday, December 17, 2013 4:26 PM