Solved Handling Multiple Products for many customers (1 Viewer)

Wabash&Erie

New member
Local time
Today, 08:03
Joined
Jul 7, 2021
Messages
5
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

Banishment Pending
Local time
Today, 07:03
Joined
May 11, 2011
Messages
11,613
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

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,358
Hi @Wabash&Erie

Welcome to AWF!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:03
Joined
Jul 9, 2003
Messages
16,245
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:-


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

..forever waiting... waiting for jellybean!
Local time
Today, 20:03
Joined
May 7, 2009
Messages
19,175
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.
 

Users who are viewing this thread

Top Bottom