Simple Design Question

[Tony]

On the main form (CustomerDB) I have a tabbed control on
which I have to track all the customers subscriptions to
our products. We currently have over 100 products. What
is the best way to select and display what customers
subscribe to? I had 34 combo boxes, but then, when a new
product is added it would be a nightmare because I would
have to re-design. I though about a subform with a
combobox lookup. Do you think this is a good way?
Thanks, Tony

 

[John Vinson]

On the main form (CustomerDB) I have a tabbed control on
which I have to track all the customers subscriptions to
our products. We currently have over 100 products. What
is the best way to select and display what customers
subscribe to? I had 34 combo boxes, but then, when a new
product is added it would be a nightmare because I would
have to re-design. I though about a subform with a
combobox lookup. Do you think this is a good way?
Thanks, Tony

If you have the 100 products in 100 fields in your table you do indeed
need a re-design right now, for the very reason you describe. You have
a many (customer) to many (subscriptions) relationship. The proper way
to handle this involves THREE tables: Customers; Products (100 or so
records, one for each product); and Subscriptions, with two fields (or
perhaps more) - CustomerID and ProductID. You may want to add a
DateSubscribed, or other information about this customer's
subscription to this product.

The Form for entering this data would simply be a mainform based on
the Customer table, with a Subform based on Subscriptions. On the
Subform you'ld have a combo box allowing selection of a product (and,
of course, controls for any of the additional information fields for
that subscription).