Selecting multiple choices of data for one field. How??

[Dean Austyn]

Hi community,

I wish to set a field in my database which can contain multiple choices of
data. To simplify things I want a field which states the colours of an item.
Items can be up to 10 colours of any combination. So if an item is red, blue
and green, I want to be able to check red, blue and green for that field out
of a list of 10 colours.

Any ideas?? Thanks!

 

[strive4peace]

Hi Dean,

make a related table to store the multiple values.

For instance:

Items
- ItemID, autonumber - PK
- ItemName, text

Colors
- ColorID, autonumber - PK
- Color, text

ItemColors
- ItmColrID, autonumber - PK
- ItemID, long -- FK to Items
- ColorID, long -- FK to Colors

PK = Primary Key
FK = Foreign Key

then you can have a mainform based on Items
and a continuous subform based on Itemcolors

ColorID will be a combobox on the subform that uses the Colors table as
its RowSource

ItemID will be used for the LinkMasterFields and LinkChildFields
properties of the subform control

for more information on setting up a mainform/subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal

*
have an awesome day
*

 

[KARL DEWEY]

I want to be able to check red, blue and green for that field out of a list
of 10 colours.
You need three records, one for each color along with other data like
quanity, price, etc.

 

[John W. Vinson]

On Fri, 15 Aug 2008 02:05:01 -0700, Dean Austyn <Dean

Hi community,

I wish to set a field in my database which can contain multiple choices of
data. To simplify things I want a field which states the colours of an item.
Items can be up to 10 colours of any combination. So if an item is red, blue
and green, I want to be able to check red, blue and green for that field out
of a list of 10 colours.

Any ideas?? Thanks!

Access 2007 introduced a multivalue field; a lot of us consider that to be A
Very Bad Idea, since it violates the very principles of relational database
design. Actually under the covers it does what you can do manually: uses
*another table* to store the multiple values.

Access is a relational database. As such, each field has only one value. To
get a Many (items) to Many (colors) relationship, you need *three tables*: a
table of Items (primary key ItemID); a table of Colors (just one text color
field as its primary key); and a table of ItemColors, with fields for the
ItemID and the Color. You could use a subform based on ItemColors on an Item
form for data entry.