Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23

    Using Access 2010 as front end, Allow multiple values in table

    I have a database that resides in SQL Server, but the frontend is in Access 2010. My table has a field where I need to be able to store muliple values, using a drop down list in a form. In Access, there's the table field property called "Allow Multiple Values" that can be set to yes/no. Is there a way to select a similar property in SQL Server so that this linked table's field property is set to yes? Thank you.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is there a way to select a similar property in SQL Server so that this linked table's field property is set to yes?
    AFAIK, No.

    Using Multi-value fields is generally considered to be a bad idea. This is a "feature" Microsoft added to Jet that breaks normalization rules.
    There are no other database engines that I know of, other than Jet, that support Multi-value fields.

    Another Microsoft bad idea is using "Look-up fields" in a table (different that lookup tables).

  3. #3
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23
    Do you have any suggestions? I have a continuous form, where I need the capability for the user to choose from multiple values in a drop down box and then have those values saved in a single field of a table. Is there a way maybe to have the user select values and then concatenate them to store? I don't know code very well, but will try my best.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have any suggestions?
    The best thing is to create a normalized table structure.
    Why do you need to have the values stored in a single field?


    I need the capability for the user to choose from multiple values in a drop down box and then have those values saved in a single field of a table
    Can't use a combo box (aka dropdown box). Could use a multi-select list box. Or maybe a subform.


    Is there a way maybe to have the user select values and then concatenate them to store?
    Look at this site by Allen Browne http://allenbrowne.com/func-concat.html

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I agree with Steve - normalize data.

    Allen's function is to concatenate values from multiple related records of a normalized data structure. It could be useful later if you first adopt the normalized structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23
    Can't use a subform because the main form is a continuous form. A multi-select box would take up too much room. Here's why we need multi-values stored. The user enters information about an estimate. In one of the fields, the user is to mark why an estimate may not be correct. They need to choose from options such as: Inaccurate Dimensions; Incorrect Pricing; Scope of Damages; etc. We'd like them to be able to select more than one option and then have that information stored, almost like a notes field.
    I'm not sure how I would use the article you mentioned for this.
    Any ideas?
    Thank you so much for your help, dany.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    SQL Server cannot use multi-valued fields. That is cast in stone.

    So the best option is to normalize your tables.

    An alternative might be to have a button that would open a pop up form with a multi-select list box (or a subform). You could make selections, concatenate the selections, and store them in a varchar type field in SQL Server (a memo field in Access).

    Sorry, I'm out of ideas. Maybe someone else can suggest something........


    I'm not sure how I would use the article you mentioned for this.
    The article was a guide on ow to concatenate.

  8. #8
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23
    Thank you so much. That's what I've done... a pop form with different options to select from works wonderfully. You guys are awesome.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Great.... Ready to mark this solved???

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    "I'm not sure how I would use the article you mentioned for this." See my reply in post 5.

    It is possible to relate two subforms. Review: http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

    Normalize data and put your current 'main' as a subform next to it's 'child' subform. The new 'main' form would not be bound to data, it would just be a canvas for the two related subforms.

    I see that or the popup described by Steve as your only viable options.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23
    June7, I know how to use subforms, but I don't understand how I could select multiple categories for one estimate?

    One of the SQL Sever gals here suggested that they often use a "relationship table". She said if I set it up this way, then I can change/edit my list when necessary, while if I go the pop-up form route I would have to make my variance categories fields (type yes/no) and they could never be changed by a user.

    For example, 3 tables:
    Variance table has fields: Variance_ID, Variance_Category
    Relationship table has fields: Variance_ID, Estimate_ID
    Estimate table has fields: Estimate_ID, (& many other fields)

    Does this sound familiar? (Unfortunately the SQL Server gal doesn't know Access and so can't help me any further.) I don't know how I would set this up in Access and how I would then select multiple caterogies for each estimate?

    I don't have much time to finish this project, so I may just have settle with the pop-up. It works great, it's just not flexible.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not really understanding the SQL Server recommended structure.

    Normalized structure means each category would be selected in a separate record in the subform. By setting the subform container control Master/Child links properties, the main form pk will automatically be saved as fk in the subform child record. This will not happen with a popup form. The child table would be like:

    ID MasterID Category
    1 876 A
    2 876 P
    3 876 J
    4 921 A
    5 921 H
    6 921 D
    7 547 P
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23
    This is exactly what I understand to be the recommended SQL Server structure as well.

    I know how to use the parent/child relationships if I'm adding one child record to one parent record at a time, but don't know how to select multiple child records for one parent record at one time. I'm sorry... I'm having trouble putting into words what I'm trying to say... thank you for your patience.

    Maybe I can explain it like this...
    I know how to do the following:Click image for larger version. 

Name:	ScreenShot.jpg 
Views:	29 
Size:	135.1 KB 
ID:	13728(hope this doesn't look so small when it posts) The user enters a new Estimate record in the form and if there's a variance, they click on the "Var" button which opens the pop-up form. Here the user can just click any checkboxes if the categories apply.

    Basically, I created a new table, Variance, linked to the Estimate table. Each field in the Variance table, except for the Estimate_ID, is a category of type true/false. This works well, except the categories can never change nor can any be added by the user since the categories are fields instead of rows in a field.

    I don't understand how to re-create this if the categories are rows or records instead of fields.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I am confused, first you describe selecting multiple values for saving into a single field, now you describe multiple yes/no fields. The latter is not normalization but I would choose over the multi-value field approach. And if you have multiple fields, why bother with another table and a 'popup'? Couldn't those 4 fields be in the estimate table and on the form? And what does 'variance test to delete' mean?

    I have referred you to an option that normalizes this structure and uses related subforms for data entry. Use of subform would not 'select multiple child records for one parent record at one time'. Each desired category would be a record in the subform. Enter record(s) only for the desired category(ies).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    danyd is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    23
    I'm sorry for the confusion... I've certainly had trouble putting this into words.

    I described saving the multiple values in a field before realizing that I can use a parent/child table to store the values. It's not necessary that I have the multples values in one field, but it is necessary that the user can select multiple values for one record all at once.

    If I use the yes/no fields, you are right... it does make sense to have the category fields in the estimate table. However they would not be on the estimate form as they would take up too much space on the form. There are more than 4 categories and they are only selected 25% of the time (don't want to see these unless needed).

    The 'variance test to delete' is just a name i used to remind me that this pop-up form is just a test with test categories. It opens when the 'var' button is clicked.

    In the end... I need either a pop-up, or a drop-downlist, or some other quick way of selecting multiple categories with a click of a button. I will go with the pop up if necessary, but wanted to see if there was a way to do this so that the user can add/change a new category if needed.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 12-29-2015, 10:41 PM
  2. Replies: 9
    Last Post: 01-22-2014, 01:27 PM
  3. Replies: 9
    Last Post: 05-07-2013, 08:37 PM
  4. Replies: 11
    Last Post: 08-31-2012, 12:24 AM
  5. Look up values in multiple table fields
    By nmcentire in forum Programming
    Replies: 2
    Last Post: 11-12-2010, 02:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums