Store a list in a single cell

[pkeegs]

I would like to select several names from a listbox or Combo box in a form
and have them stored in the same cell in a table. eg I have a list of clients
and I would like to be able to select say three of those clients and store
them in a single cell in a table. Is this possible?

 

[John W. Vinson]

I would like to select several names from a listbox or Combo box in a form
and have them stored in the same cell in a table. eg I have a list of clients
and I would like to be able to select say three of those clients and store
them in a single cell in a table. Is this possible?

Since tables don't HAVE "cells" (spreadsheets do...), no. You could write VBA
code to update a Field in a table to a concatenated string of names; but in
general you would NOT want to do so. It violates two basic principles of
database design: fields should be "atomic" (having only one value), and data
should not be stored redundantly.

What business need are you trying to accomplish? What will you be doing with
this list of clients? I'm sure there's a better way to accomplish it!

 

[Tom van Stiphout]

On Thu, 10 Sep 2009 18:59:01 -0700, pkeegs

It is possible, but bad practice. Relational database design rules
require that you store the selected clients one at a time, in a table
with a one-to-many (1:M) relation to the Clients table. Don't worry
about storage space or having many records in this table.

Example: say I have two tables: Products (ProductID PK, ProductName)
and Clients (ClientID PK, ClientName), and I want to store who likes
what product. I would create a third table ProductsLikedByClients
(ProductID PK, ClientID PK) to create a many-to-many (M:M) relation
between the two main tables. Btw, this is not a contradiction with the
first paragraph: a M:M relation is a combination of two 1:M relations.

Once you have this in place, querying who likes what, or which
products are liked by whom, is MUCH easier than what you're proposing.

-Tom.
Microsoft Access MVP

 

[pkeegs]

Thanks for the reply John. I have since found what would probably provide an
answer for me in a reply to a posting by Allen Brown 03/04/08.I have a list
of trust clients and I wish to identify beneficiaries from another list that
relate to each trust. I could do it by setting up enough 'Fields' to
accomodate all possible beneficiaries, but thought I would see if there were
other options and possibly less cumbersome.

 

[pkeegs]

Thanks everyone, I'm beginning to see how to get around my problem & should
be able to work out something practical from here.

 

[George]

Yes, but it's a very bad idea.

If this question is related to Access, you are working with a relational
database, not a spreadsheet. One of the really valuable things a relational
database allows you to do is avoid bad designs of that sort.

Without knowing more about your specific business and business rules, we can
only offer generalities, but it is possible to say that you need at least
two tables to accomplish this.

The first table is the one to which you are referring in this question.
Whatever it is for, it should have a Primary Key field, which uniquely
identifies each of the records in it. You can use that Primary Key to relate
or link this table to the second table, which is the one in which your
clients will be identified. You'll also have a client table, in which
attributes of your clients are stored.

Here's a quick look at the general structure of these tables:

tblClient
=============
ClientID (Primary Key, Autonumber)
ClientName
etc.
tblYourTable
=============
YourTablePimaryID (Primary Key, Autonumber)
FieldOne
etc.

tblClientsYourTable
============
ClientYourTableID (Primary Key, Autonumber)
ClientID (Foreign Key form tblClient)
YourTablePimaryID (Foreign Key from tblYourTable)
etc.


Note that the third table contains two Foreign Keys. One links to Clients
and the other links to your other table.

THis is typically referred to as a "Junction" table, and its purpose is to
handle requirements like the one you describe.

Here's a link to a Powerpoint presentation which discusses this sort of
relationship, and other aspects of table design.
http://www.gpcdata.com/downloads/saugpresentation.zip

George

 

[John W. Vinson]

Thanks for the reply John. I have since found what would probably provide an
answer for me in a reply to a posting by Allen Brown 03/04/08.I have a list
of trust clients and I wish to identify beneficiaries from another list that
relate to each trust. I could do it by setting up enough 'Fields' to
accomodate all possible beneficiaries, but thought I would see if there were
other options and possibly less cumbersome.

See Steve's reply: it's the correct relational design. "Fields are expensive,
records are cheap".

 

[fangzige]

I would like to select several names from a listbox or Combo box in a form
and have them stored in the same cell in a table. eg I have a list of
clients
and I would like to be able to select say three of those clients and store
them in a single cell in a table. Is this possible?