Best option for choosing, storing, and searching multiple selections

[fdawson]

I apologize in advance for mangling terms and confusing phrases. I am new to Access, and have had a relatively large scale database redesign project dropped in my lap.

We currently have an Access database that lists our roughly 3500 sales contacts. We already have a pretty nice form for all of their contact info, etc. I have already added a search form that allows the user to click a "Search" button, and have a dialogue come up. The user can then choose a Field to search, enter their string, and either click or Search, or search another field (I currently have 3 possible). The search form then closes and displays the filtered results in the main form.

What we would like to be able to do is have a streamlined method of labeling and searching for the products and services that each customer offers. I have a list of about 20 possible products/services, and most companies will offer more than one from the list.

I have looked at list boxes, radio buttons, etc. and would like a more informed opinion about what would be the best method. Also, I would like to make it an option in my Search form.

Thanks in advance for any help or advice.

 

[MajP]

Are the products and services discrete records or is this just information in a memo/text field?

 

[fdawson]

The products are services are a very simplet list. They don't exist in any form in the database yet. This is something we want to add to the current structure.

We are a company that sells used steel mill equipment, and we want to identify the different products our contacts manufacture, so we can know what types of offers to send them.

For instance, the first 5 items on the products list are:
bar
beam
billet
bloom
cold coil

It would be very possible for one manufacturer to make several of those. We are looking to identify what products, and then be able to have it be a searchable field somehow.

Thanks by the way.

 

[MajP]

Ok. Its like building a house. You need to worry first about the foundation and frame (tables) before worrying about the curtains and paint (forms, form controls).
If your tables are correctly designed then making a robust interface is easy. So if you are still designing the data we can help in getting the correct tables. Need to kind of describe your table structure. Like

tblName
fieldName (primary key)
otherimportantfields
otherField (foreign key to another table)

Once you have those it will be easy to come up with forms and controls to allow you to add products and services for a customer and then search the database for these.

 

[fdawson]

We are basically pulling all of the info from one very robust table. It looks like the person who initially designed this was, like me, more familiar with Excel then Access.

There is one large table called Customers, and within that table are columns with the headings you'd expect for Conatct Info: FirstName, LastName, Company, Address, etc. We do have some simplet Yes/No checkboxes as well, like if they are supposed to get emails or snail mail or whatever.

I don't feel like it was designed to do much computation, just to be a more visual display of the spreadsheet data, just one record at a time.

 

[MajP]

Depending on the time and energy you plan for using this database will determine how much work to put up front. In a spread sheet you design for presentation. There is really one view. In a database you design for data structure. There are unlimited amount of views of that data. If you are looking to do this right.
Identify the types of information you plan to manage
Identify the types of output you will require
Identify the functionality you will want.
From there you need to design tables to support this data structure.

You should google
"relational database design basics" and learn about table normalization
Here is one but there are many. Some may be easier than this one.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

We tend to be reluctant to give advice on how to band-aid a sucking chest wound. Because it just is a snow ball of fixes. So i recommend fixing your current table and making additional tables to support future requirements. If your tables are correct then adding functionality is much easier.