Change from single value field to multi value

Posted on 2009-02-09
Last Modified: 2012-05-06
Hi, I have a database in MS Access 2007. I want to change every field on the form except for field "Site Name", "Contract ID", "Site ID" to a multi valued field so that each site name can have multiple "Vendors", "Name of Contacts" etc.
Please see my attached database to see what I am saying.
I have heard that we can make multi valued fields in Access 2007 without any coding.
Question by:sahi0002

    Author Comment

    Attached is the database. I was unable to attach the database in Access 2007 form. it is in access 2000
    LVL 84
    You really should not do this. Multivalued fields aren't intended to hold data of this nature, and you would be very hard pressed to search and report on this data.  MV fields are really intended to store a relatively small set of choices, and are not intended to replace proper relational design (which is basically what you're trying to do).

    Instead, you should store your data in related Tables. For example, you'd have a Vendors table, a Contacts table etc etc. I'd suggest you read up on basic database structure before you get too much futher along. Also you might review the NOrthwinds sample that ships with Access.

    Author Comment

    Someone suggested me to use a subform embedded in the mainform. When you set the LinkChild and LinkMaster fields it will automatically handle the filtering of records and setting the value for the link field in new records.
    But i'm not sure what it means. Can you please elaborate
    LVL 84
    A subform is typically used to represent a "Master/Detail" data setup, much like you'd typically have with your "SiteName/Vendor" relationship. If you have tables like this:

    lSiteID [Primary Key, AutoNumber]
    etc etc

    lVEndorID [PK, AutoNum]
    etc etc

    lSiteVendorID [PK, AN]

    You can then store multiple Vendors for a Site, and each Vendor can be related to more than One Site. In this example, your tblSiteVendors tablew would be used for the subform's Recordsource, and you'd use either the tblSiteVendors.lSiteID or tblSiteVendors.lVendorId as the Child link, depending on what you were viewing in the main form.

    Author Comment

    Hi, i'm very new to Access. Is it possible for you to do it for me. :S
    I want all field except for the field Site Name to have options of adding more values for every Site Name.
    LVL 74

    Accepted Solution


    This site is not really a "do it for me site".

    Althogh some experts (myself included) will take the time to do small things for you, it is at our discretion.
    I create samples all the time but I rarely "customize" them for each asker. They are always generic.

    Having worked with your sample database before, the first thing I will say is that you only need a "LookUp" table (One to Mamy) for each of these fields, not a "Multi-valued" Field (Many to Many).
    In this regard, the term "Multivalued" is a little misleading.

    The one to many relationship is the most basic table relationship you should be familiar with when designing databases.

    If you are in fact "very new to Access", then perhaps it might be to your advantage to set this project asside for a while and get familiar with the basics of designing a database.

    Access 2007 is a complex program and is not easy for a new developer to get comfortable with.
    I suggest the following books:
    Access 2007 Bible:
    Access 2007, step by step:

    The Northwind 97-2003  sample is alse a great Hands-On database for beginning developers.


    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now