Setting up a new db

I need help and direction in building a db that will show Company Info  /  Sales Rep Info  /  Product Info.

A Company can have multiple Sales Reps and multiple Product lines

I will need to pull lists by Sales Rep showing Product lines and Product lines by Company

I have attached my best effort at starting this. I'm not sure on how to set up the relationships.
VendorContacts.accdb
ssblueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well you've got a good start so far....the material groups looks a little off (two ID's).

So on this:

A Company can have multiple Sales Reps and multiple Product lines

one table for Sales Reps (or maybe this is your contacts):

tblSalesReps
SalesRepID - AN - PK
RepName - Text
...

one for products:
tblProducts
ProductID - AN - PK
ProductName - Text
...

Then you need some linking tables, but the question is, is it one sales rep for each product or no?  Might there be a sales rep that represents multiple companies and products, etc?

If your just keeping track of sales reps associated with a company, and it's products:

tblCompanyProducts
CompanyProductID - AN - PK
CompanyID - Long - CK1A, FK to CompanyInfoTbl
ProductID - Long - CK1B, FK to tblProducts

tblCompanySalesReps
CompanySalesRepID - AN - PK
CompanyID - Long - CK1A, FK to CompanyInfoTbl
SalesRepID - Long - CK1B, FK to tblSalesReps

some of the abbreviations used:

'AN' - Autonumber
'PK' - Primary Key
'FK' - Foreign key (a copy of a primary key in another table, or in other words, a pointer).

'CK' - Candidate Key - Could serve as a unique key.  There may be more than one Candidate key in a table.   Keys can be made up of multiple fields as well, so CK1A    is Candidate Key #1, part A, and CK1B is Candidate Key #1, part B

 The two fields together form the key.  In the linking tables above, what it implies is that a sales rep or product can only be listed once for a company.

 What your doing with this linking table is forming a many to many relationship:


   Companies    1 -----  M M  ------ 1  Sales rep

  So a company can have many sales reps, and a sales rep can have many companies

  I'll stop here so you can digest that.

Jim.
0
hnasrCommented:
I suggest you have at look at the Northwind database.
Otherwise you will spend more time in your learning process.

Most of the experts, if not all, have gone through Microsoft database samples in learning Access.
0
ssblueAuthor Commented:
So I have multiple Companies.

Each Company will have multiple products and multiple Sales reps.

Each Sales Rep can have multiple product lines and could represent multiple Companies.

I know what I think I want but I am still a novice with Access.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ssblueAuthor Commented:
I looked at the Northwind DB and it is impressive but way more than I am needing and way more than I can figure out in a reasonable amount of time. Some day I hope to be able to spend some time understanding it but right now I am in a bit of a hurry so suggestions and explanations would be greatly appreciated.

I basically just need an address book that keeps track of Sales Reps and who they work for and what the sale with the ability to create lists and reports by Company, Sales Rep and Product. I would also need search functions for Company, Sales Rep and Product.
0
D PineaultCommented:
Since you appear to be new to Access development, you may like to read http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/ which can quickly bring you up to speed for setting things up, dos, don'ts, sharing, ...
0
hnasrCommented:
You may start with a template from Microsoft that is most relating to what you want to do.
Personalize it and start asking for extra help, using that as a model, in a series of questions.

This guarantees getting a continuous help for a longer period.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So I have multiple Companies.

Each Company will have multiple products and multiple Sales reps.

Each Sales Rep can have multiple product lines and could represent multiple Companies.

 So this is a good start right here...stating what you want to get out.  That is the first step in designing any application.   The second is what data you need in order to produce that information.   Once you have that, then you will starting grouping those pieces of data into tables.

but right now I am in a bit of a hurry so suggestions and explanations would be greatly appreciated.

It's probably a little more complex then what your thinking, but read on.

I basically just need an address book that keeps track of Sales Reps and who they work for and what the sale with the ability to create lists and reports by Company, Sales Rep and Product. I would also need search functions for Company, Sales Rep and Product.

 Well as a start, you need a table for each "thing" your representing in the above statements.   That includes the relationships between each (ie. the fact that salerep x, sells product y).

So you will have:

tblCompanies
tblSalesReps
tblProducts


 as a start.  You will also need the linking tables between each (take a look at my first comment).   However one thing that is not clear is if you want to track who is working for who and what they offer, vs who they are selling to and what.   If you want to track both, then you want:

tblCustomers
tblVendors
tblSalesReps
tblProducts

  and then again the linking tables.  

Jim.
0
ssblueAuthor Commented:
So I am just wanting to keep track of the sales reps basically. I need to know who they work for and what products they sale.
The reasoning behind this is that as I come in contact with a Sales Rep, I want to capture that information for future reference so that when I need a "pump" I can look through the db and find out which companies sell pumps (there could be multiples) and which Sales Reps can I contact concerning the pumps.
I'm thinking this really isn't complicated but I just need help in setting up the tables correctly to begin with so I can show products by company and by Sales Rep and be able to pull that information quickly.
The issue I have is understanding what fields are needed in which tables and how to link them.
Thanks for your help and efforts to explain.  I will be away from the computer for about a day but I will check back as soon as I can. I know EE likes one question per post but I don't know how to ask just one specific question. I am sure once I get the tables and links right there will be more.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, so your looking at this from a procurement viewpoint and assumes that sales reps can be independent (represent more than one company).

If so then it is pretty much just what I laid out before:

tblcompanies
CompanyID - AN - PK
CompanyName - Text
...

tblSalesReps
SalesRepID - AN - PK
RepName - Text
...

tblProducts
ProductID - AN - PK
ProductName - Text
...

and the linking tables:

tblSalesRepCompanies
SalesRepCompanyID - AN - PK
SalesRepID - Long - CK1A, FK to tblSalesReps
CompanyID- Long - CK1B, FK to tblCompanies


tblSalesRepProducts
SalesRepProductID - AN - PK
SalesRepID - Long - CK1A, FK to tblSalesReps
ProductID - Long - CK1B, FK to tblProducts


With looking at these two tables, you can see:
1. What companies a sales rep is associated with.
2. All the reps that work with a particular company.
3. What reps sell a product (regardless of company).

This won't tell you though which companies sell a given product, so it may need to be structured a bit differently, but that's is the jist of it.

 For example, for a "pump", sales rep x, y, and z might all sell pumps it, but you won't know out of the ten companies that x might represent, which of the ten sell the pump you need.   Don't know if that is critical or not.

Jim.
0
ssblueAuthor Commented:
Sorry for the confusion. However you are right, I do need to know which company a Sales Rep works for. Let me try again.

I need to know the Sales Rep information. I also need to know what Company they work for and what Products they handle.
I need to know the Company information. I also need to know who works for the Company and what Products the Company handles.
I need to know the Product information. I also need to know what Company the products belong to and what Sales Rep handles each product.

A Sales Rep can belong to many Companies.
A Sales Rep can handle many Products.
A Company  can have many Sales Reps.
A Company can have many Products.
A Product can belong to many different Companies.
A Product can belong to many different Sales Reps.

Example:
Products: Pump, Motor, Fan, Conveyors
Sales Reps: Tim, Ben,  Steve
Companies:  A, B, C, D

Companies A, B, C can all handle Pumps, Motors and Fans, Company D only handles conveyors.
Sales Rep Tim works for all four Companies
Sales Rep Tim handles all four components.
Sales Rep Ben works only for Company B and only handles Motors
Sales Rep Steve works for Company A and C and Handles Pumps for Company A and handles Fans for Company C.

Thanks for the questions, it is helping me to understand the complexity of this. I hope this helps.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry for the delay in response.

So it is basically what I posted before.    I've changed the naming slightly because the primary thing for you is "products" and I've added a field here and there with some ways in which you might want to expand this (i.e. a Product categories table):

Main Tables:

tblProducts - One record per product
ProductID - AN - PK
ProductName - Text
ShortDesc - Text
CategoryID - Long - FK to tblProductCategories
...

tblCompanies - One record per company
CompanyID - AN - PK
CompanyName - Text
...

tblSalesReps - One record per sales rep
SalesRepID - AN - PK
RepName - Text
...

tblProductCategories
CategoryID - AN - PK
Description - Text


 and the linking tables:

tblProductsCompanies - One record for each company that makes a product.
ProductCompainesID - AN - PK
ProductID - Long - CK1A, FK to tblProducts
CompanyID - Long - CK1B, FK to tblCompanies
VendorPartNum - Text - Vendors part # for this item.

tblSalesRepCompanies - One record for each sales rep that represents a company.
SalesRepCompanyID - AN - PK
SalesRepID - Long - CK1A, FK to tblSalesReps
CompanyID- Long - CK1B, FK to tblCompanies


tblProductsSalesReps - One record for each sales rep that represents a product.
ProductSalesRepID - AN - PK
ProductID - Long - CK1A, FK to tblProducts
SalesRepID - Long - CK1B, FK to tblSalesReps

 
This design is based on the idea that if a rep sells a certain product, and they represent a company making that product, then they sell that product for that company.

 If reps only handle only specific products for a company, then it will need to take a different form.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ssblueAuthor Commented:
Thanks for the help with this. I think you have answered my original inquiry. I will work with this a bit and then I am sure I will have additional questions in the future.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Setup the tables as outlined and then try entering some data.    You'll find out quickly enough if anything you need is missing, and it will help you visualize the setup.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.