Results 1 to 5 of 5
  1. #1
    aaendewy is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    6

    Exclamation New to Access and need help with multi-valued field

    Hi Forum members,



    Extremely new to MS Access and need help with multi-valued fields.

    Current situation:

    I am working on an existing MS Access file that was created by an external company for us, the tables I am looking at are Employees and CommunicationLog.
    The current form view of the Employees table is showing existing Communication logs.

    Objectives:

    I have been request to create a new functionality of the current access database: "Have a page where you can enter a single communication entry and select multiple people to add to it."

    Progress:

    I originally thought I can make the EmployeeID to hold more than one item. So I went to CommunicationLog and made changes to the EmployeeID field to a combobox look up so that it capture more than one ID "Multiple values" and hope that it would append that single communication to the selected Employees via EmployeeID look up field, but it's throwing me all sorts of errors.


    https://postimg.cc/image/kfthxs2fr/

    Would anyone here please direct me to the right process/source/how to do?
    Last edited by aaendewy; 06-10-2018 at 09:55 PM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Most of us don't/won't use multivalue fields. You'll also have better luck attaching pictures here; most won't won't follow links to unknown sites. If you get away from multivalue fields this may help:

    http://www.baldyweb.com/MultiselectAppend.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    aaendewy is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    6
    Hi @pbaldy, what would you use instead of multi value fields? Do you have any suggestions? Thanks in advance.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    These fields look like they contain comma separated values but in fact are a representation of what has been hidden from the user and designer. Access creates what is basically a properly normalized table for the data, but you not only can't see it, querying a mvf can be a pain. You can end up with multiple unwanted records because there are multiple values that fit the criteria. Mainly though, no other relational database schema will accept them, so if you ever want to migrate your app to one of these platforms, you cannot.

    That being said, all that you need to do is what Access is doing for you and hiding - create properly normalized and related tables. This is very simplistic, but should serve to explain:
    This is what you see
    DEPT
    LNAME
    sales Smith, Brown, Adams

    This is what you should create, instead of Access doing it
    DEPT
    LNAME
    sales Smith
    sales Brown
    sales Adams
    NOTE: the examples are not meant to suggest any particular table design for your db; they are only for answering your question on how to avoid the use of mvf's. You are probably wondering why have these at all if we refrain from using them. The only answer I've ever seen that makes sense on that is that they were developed to support SharePoint lists. Otherwise, they can be a PITA and make little sense when you can create a properly normalized and related set of tables that can be migrated to other platforms.

    I also don't arbitrarily upload from other sites thus I haven't viewed your file and can't suggest what it is you need to do. You can always compact a copy of your db and zip it. Usually that will compress it enough so that you can upload it here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @aaendewy,

    Before modifying an existing database, I suggest you work through some tutorials on normalization.
    As pbaldy and micron have said ---multi valued fields are rarely (if ever) used by developers.

    Get yourself a list of requirements in plain English to understand what is within the scope of your project.

    Here is a link to information on Database Design and Planning - articles, tutorials and videos on various aspects of data base concepts, analysis and design techniques, reference materials..... I strongly recommend the tutorials from RogersAccessLibrary within the linked info.

    The soapbox: Just as you wouldn't attempt to fly a jumbo jet without proper training; don't try to modify an existing database without some training in database and Access.

    Look at some of the links in "similar threads" at the bottom of the page to get other opinions on MVFs.

    Good luck. Treat the project as a learning opportunity.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-19-2013, 04:02 PM
  2. join two tables on multi-valued field
    By smudger in forum Queries
    Replies: 3
    Last Post: 06-17-2012, 03:12 AM
  3. Replies: 5
    Last Post: 12-18-2011, 09:12 PM
  4. Multi Valued field sort
    By bugman61 in forum Reports
    Replies: 2
    Last Post: 08-06-2011, 11:45 AM
  5. Append Query - Multi-Valued Field
    By catat in forum Queries
    Replies: 0
    Last Post: 05-11-2010, 01:52 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