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!

 

[Uncle Gizmo]

Excel table where the customers were row headers, products were column headers

This sounds like a job for my "transpose tool"...

The tool can separate out the products into a new "vertical" table, with each product associated with each customer.

With regard to the "customer value" I think the same approach could be adopted, not sure though, not without seeing the table setup.

For more information see my blog Excel in Access:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

There should be a link where you can download the tool. If you want a free copy, contact me and I will tell you how you can get a free copy.

 

[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.