[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change from single value field to multi value

Posted on 2009-02-09
7
Medium Priority
?
337 Views
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.
0
Comment
Question by:sahi0002
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:sahi0002
ID: 23597868
Attached is the database. I was unable to attach the database in Access 2007 form. it is in access 2000
PB-Listing-changed.zip
0
 
LVL 85
ID: 23604099
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.
0
 

Author Comment

by:sahi0002
ID: 23608725
Hi,
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
ID: 23610747
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:

tblSites
------------
lSiteID [Primary Key, AutoNumber]
sSiteName
etc etc

tblVendors
----------------
lVEndorID [PK, AutoNum]
sVendorName
etc etc

tblSiteVendors
--------------------
lSiteVendorID [PK, AN]
lSiteId
lVendorId

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.
0
 

Author Comment

by:sahi0002
ID: 23618626
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1500 total points
ID: 23619644
sahi0002,

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:
http://www.amazon.com/Access-2007-Bible-Michael-Groh/dp/0470046732/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1221793034&sr=1-1
Access 2007, step by step:
http://www.amazon.com/Microsoft-Office-Access-2007-Step/dp/0735623031/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1221793077&sr=1-1

The Northwind 97-2003  sample is alse a great Hands-On database for beginning developers.
http://www.microsoft.com/downloads/details.aspx?familyid=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23802953
:-)
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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