Saving multiple selections to a table

[freddawson]

I am looking for the best way to save multiple selections into one field on a table.

I'll try to make this succinct:
My company has a relatively simple but functional contact list built in Access. There is actually only one table; it holds all of the data for every customer, and has the labels you anticipate, like Name, Address, Email, etc.

We are a used equipment company, and want to be able to label the products and services each custmer supplies, in order to send them the right updates. The problem is that each customer usually does more than one thing.

I have a proposed list of about 20 or so choices I would like to be able our people to identify.

What is the best method for me to add these, so that 1) our people can see all the options and select multiples; 2) have their selections saved to the current table; and 3) have that data be searchable.

I appreciate any help or advice anyone has to offer.

 

[jzwp22]

What is the best method for me to add these, so that 1) our people can see all the options and select multiples; 2) have their selections saved to the current table; and 3) have that data be searchable.

Easy, you don't.

If you a customer can have multiple selections you have a one-to-many relationship which requires a related table. Futher since a selection can apply to many customers you have a second one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (customers and selections in your case), you have a many-to-many relationship for which you need 3 tables.

tblCustomers
-pkCustomerID primary key, autonumber
-txtName
-txtAddress


tblSelections (a table to hold all possible selections)
-pkSelectionID primary key, autonumber
-txtSelection

Now a table to hold those selections pertinent to each customer. Each selection/customer will be a record (not a field) in this table

tblCustomerSelections
-pkCustSelectID primary key, autonumber
-fkCustomerID foreign key relating to tblCustomers
-fkSelectionID foreign key relating to tblSelections


I use several prefixes to my field names as follows:

pk=primary key field (I always use an autonumber datatype)
fk=foreign key field (You must use a long integer number datatype to match the corresponding autonumber datatype in the related table)
txt=text field
dte=date/time field
lng=long integer number but not a foreign key
sp=single precision number
curr=currency

 

[Privateer]

freddawson:
Putting multiple things in a field is a bad idea and you really don't want to to this. You will have problems down the road. The answer to your question, however, is concatenation. Something like things = things & " " & newthing. Or you could add twenty columns to your customer table and put a check in whichever field they can do. But I don't recommend either. If you want to grow your Access abilities, read on.

You need to make two new tables and relate them to the customer table. This will require a little work on your part, but the benefits will really pay off. Basically you need to "normalize" your data into two tables. Setting tables up properly is one of the most important steps in building a database. Essentially you need two other tables, one with the "more than one thing" that they do, and another to hold the one to many relationship. Among the other fields that you need in the first table are two id fields, ThingID, autonumber and primary key and CustomerID, foreign key to the customer table. This means the primary key number that identifies your customers goes into this field. That is how you relate one table to another. Don't store the customer name, the customer table should have a unique ID number for each customer. If it doesn't fix that first.
Now you need a second table to track the one to many relationships, I call this a linker table. This table will have just the CustomerID and ThingID fields. If customer 1 has three things it does, the table will look like this:
CID TID
1 3
1 8
1 15
And that is about it for your question. To merge the information back together, you put all three tables in a query and relate the tables by their common numbers.

However, this is about to get complicated because your next question is how can I select a customer and see all the things that he does? This involves a form with a subform in it, the former attached to the customer's table and the latter attached to the a query made up of the linker and things table. With a combo box on the main form to select a customer, the sub form will display all the things that he does.

You have some work ahead of you, but if you take my suggestions you will learn the steps to creating a good database. Good luck and let me know how it is working.
Cheers,
Privateer

 

[freddawson]

Thanks to both Privateer & jzwp22 for help on this. I'm glad I got the same answer from 2 different people. That definitely lets me know its the right way to go.

Several experts pointed me towards learnging about normalization today, and after reading about it, I sort of anticipated this type of advice.

Not to say I understand completely, but I think I get it enough to start piecing together the back end.

I guess my next issue would be advice for how my users will label these things in the front end. If we get a brand new customer contact in tomorrow, and I want someone to enter it, how will they visually see the product choices?

If I make a list box or something in my Customer form, can I make it save into jzwp22's tblCustomerSelections or Privateer's linker table?

 

[Privateer]

You use a combo box on your form and your users select the product choices (they see the text), but the combo stores the number. Read up on combo boxes, they display the text but store the numbers, which is fundamentally how databases work.
As far as where to save this number, it depends on how you add a new product choice. You could make a small form with a customer combo and a product choice combo tied to the linker table. On your customer form, create a button called new product choice which opens this form. That's it, just two combos. The source for the product choice combo, however, is the product choice table but the combo is bound to the field in the linker table.
Good Luck,
Privateer

 

[jzwp22]

You will need to create forms for your user interface (the users should never see your tables)

You would have a main form based on your customer table. Within that form you will need a subform based on tblCustomerSelections. In the subform you would use a combo box based on tblSelections. That combo box will be bound to the fkSelectionID field of tblCustomerSelections.