Solved Handling Multiple Products for many customers

[Wabash&Erie]

It seems to me that this should be obvious, but I cannot see through it. I work for a service company that offers multiple services to customers. Let's say there are 5 products or services. Customer A has all 5 services. Customer B has 3 of them. Customer C has 3 and cancelled a 4th. In others words, each customer may have between 1 and 5 services that are available. I inherited an Excel table where the customers were row headers, products were column headers (across one Excel sheet) and appropriate values were placed into the intersect of customer-to-product.

What is the best way to store this information of customer to service since there are multiple services available to multiple customers? I need to be able to facilitate new customer data entry, edits or cancelling of a service as well as reporting for sales people on which customers in their territories do or do not have specific services?

I have to further complicate it by saying that the value which will relate the customer to service is not a simple yes or no. There are 4 possible values that could be in each field. 1=Customer, 2=Strong Prospect, 3=Lost Service, 4=Partner Sales Funnel

Regards

 

[plog]

You have a many to many relationship. A Customers can have many products; and a product can belong to many customers.

In a database, this is handled with a junction table. Essentially its a table that stands between 2 tables and sorts out who has what:

tblCustomerProducts
cp_ID, autonumber, primary key
ID_Customer, number, foreign key to Customers table
ID_Product, number, foreign key to Products table
cp_Relationship, text, will hold the values-- Customer; Strong Prospect; Lost Service; PArtner Sales Funnel

THe above table will let you know which customers have what products and what products go with what customers and the relationship.

 

[theDBguy]

Hi @Wabash&Erie

Welcome to AWF!

 

[arnelgp]

you already familiar with your system, so i think it won't be that
hard for you to translate your excel sheet into access tables.