Assigning personnel to geographical regions

[machumpion]

let's say i have a sales team whose personnel i must assign to customers based on geographic regions of the customer

i.e. Ted sales person is assigned to California, John sales is assigned to Florida

each month, i'd like to see how much sales Ted and John generated in there respective regions

Is this as simple as having a sales personnel table and customer table. Adding a "sales person" field in the customer table and entering the sales persons foreign key into the customer table based on the region they are assigned?

What might be the most efficient way? Thanks!

what if more than one person was assigned to the same region? what if more than one person was assigned to 1 specific customer?

 

[sneuberg]

One thing to think about is whether there is any information already in the customer table that might help. For example if the customer table has Zip codes you could make a table that assigns Zip Codes to regions or states to regions for example.

Another thing to think about it whether a sales person will be assigned to multiple regions.

 

[machumpion]

One thing to think about is whether there is any information already in the customer table that might help. For example if the customer table has Zip codes you could make a table that assigns Zip Codes to regions or states to regions for example.

Another thing to think about it whether a sales person will be assigned to multiple regions.

let's say im assigning people to US states. US state data is available already in the customer table

would it be better to create a US State table and have a field for the sales people assigned to the states? If CA had two salespeople, should there be a field for each sales person?

 

[plog]

let's say im assigning people to US states...If CA had two salespeople,

Those 2 statements are contradictory. If you have more than 1 person per state, you are not assigning by state.

You must assign all people at the most granular level that you want to report on. This assignment should be done in a new table.

 

[machumpion]

Those 2 statements are contradictory. If you have more than 1 person per state, you are not assigning by state.

You must assign all people at the most granular level that you want to report on. This assignment should be done in a new table.

currently i have a customer table only. Should I create a new table with the fields:
Country, State, sales person 1, Sales person 2, sales person 3? Then link the State field back to the State field in the customer table? Thanks!

 

[plog]

First a few notes about things tangentially related to this issue:

1. When you start numbering field names (salesperson1, salesperson2, salesperson3) that means you are not structuring your tables properly. When you feel the need to do that, its time to use a new table. So the proposed structure is absolutely wrong.

2. I would only use alphanumeric characters in table and field names. that means no spaces.

3. Another rule of databases is to not store redundant data. That means this assignment table doesn't need the Country field. If you wanted to assign states to countries you would have another table for that. It doesn't belong in the salesperson assignment table.

On to the actual issue. It still sounds like you don't know what level salespeople are actually assigned. If you are thinking of assignming multiple people to a single state, its not going to work. Maybe this example will help:

Joe is assigned all of Arizona and everything in California south of San Diego. Pete has the rest of California and no other state. Suppose Arizona sales are 5 and California sales are 19. Joe will show with 24 sales and Pete with 19. There will be no way to discern which California sales are Pete's and which Joe's and they will both get credit for them. Then when you run your Total Sales Report (regardless of salesperson) its total comes up 19 short of your Report By Salesperson because Report By Salesperson double counted your California sales.

You must assign salespeople at the most granular level you will need.

 

[Solo712]

currently i have a customer table only. Should I create a new table with the fields:
Country, State, sales person 1, Sales person 2, sales person 3? Then link the State field back to the State field in the customer table? Thanks!

If you have a salesman assigned to the customer, you can add a field with the salesman's ID to the customer table. (You should have a salesman table). If more than one salesman service customers in a state then you need to capture the salesperson via sales table. I would recommend capturing the sales by a salesperson via sales table always, since this method guarantees the sales record is permanent, i.e. stays if the assigned salesman to a customer changes. In this way you can track sales performance by the individual sales people reliably.

Best,
Jiri

 

[The_Doc_Man]

In order to see how to handle the case of more than one salesperson to the same district, and one salesperson with more than one district (assuming that happens EVEN ONCE), look up the concept of "Junction Tables" in this forum and in a general search.

 

[machumpion]

If you have a salesman assigned to the customer, you can add a field with the salesman's ID to the customer table. (You should have a salesman table). If more than one salesman service customers in a state then you need to capture the salesperson via sales table. I would recommend capturing the sales by a salesperson via sales table always, since this method guarantees the sales record is permanent, i.e. stays if the assigned salesman to a customer changes. In this way you can track sales performance by the individual sales people reliably.

Best,
Jiri

What I say next may be very confusing.

sales data comes from several external parties that already require alot of manual formatting before it is ready to be imported into Access.

Let's say a salesperson (John) is only responsible for Florida from Jan 2016 to April 2016. Is there a way to query John's sales without adding John's name in each sales record for Florida, and without specifically querying only Florida sales from Jan 2016 to April 2016?

Is there some way, in the database to communicate "John was in charge of Florida from Jan 2016 - Apr 2016) and easily query his sales based on his name? Possibly by some kind of "Start Date Florida" & "end date florida" fields?

Thanks!

 

[plog]

Is there some way, in the database to communicate "John was in charge of Florida from Jan 2016 - Apr 2016) and easily query his sales based on his name?

I mean, is it easy for me to drive from my house to work in under 15 minutes? Well, yes, but that's because hundreds of thousands of man hours went into building cars, paving roads, refining gas, etc.

What you want is possible, and it can even be made simple for a user. But laying the foundation that enables it that is going to require work. And the first step is setting up your tables properly. I think its time for you to share your current tables. Can you post a screenshot of your relationship window? Or perhaps upload your actual database with sample data in it?

 

[machumpion]

I attached a picture of the database relationship chart.

the [broker] table is supposed to assign [states] to sales people [salesteam].[name].

There are customers/[stores] for which we have [state] data for. Stores make [orders] and the date they order is tracked [orders].[ordermonth].

(start) and (end) on [salesteam] is supposed to be the date range they are responsible for orders in a given state

If Frankie was assigned the state CT from Jan 2015 to May 2015. How can I easily query the sales for Frankie without explicitly entering the date range under (ordermonth) criteria?

Thank you!

 

[plog]

f Frankie was assigned the state CT from Jan 2015 to May 2015. How can I easily query the sales for Frankie without explicitly entering the date range under (ordermonth) criteria?

You've got 2 things working against that:

1. Start/End dates should be in the Broker table. Not in the SalesTeam table.

2. Your timeframes are unkosher. Start/End are actual date fields from your explanation. Going just by its name [OrderMonth] is most likely text. You need to make those 3 fields the same datatype, one that allows you to work with the data it contains properly. That means all 3 of those fields should be date fields.


Once you do that you could get what you want via 2 queries. This would be the SQL of the sub query (let's call it _sub1).

SELECT StateProvince, Revenue, OrderDate FROM Stores INNER JOIN Orders ON StoreName=Store

The above assumes you change OrderMonth to OrderDate and OrderDate contains an actual date. The above assigns a state to every order, with that you can build your final query and assign salespeople:

SELECT Broker, SUM(Revenue) AS TotalRevenue
FROM _sub1
INNER JOIN Broker ON State=StateProvince AND (Start<=OrderDate) AND (End>=OrderDate)
GROUP BY Broker

 

[machumpion]

SELECT Broker, SUM(Revenue) AS TotalRevenue
FROM _sub1
INNER JOIN Broker ON State=StateProvince [B]AND (Start<=OrderDate) AND (End>=OrderDate)[/B]
GROUP BY Broker

thank you so much for your help. I only have one more question:
how would the AND (Start<=OrderDate) AND (End>=OrderDate) part of the code appear in Query Design?

 

[plog]

It wouldn't. that type of JOIN is not supported in the Query Design. You have to hand code it by viewing the SQL. Further, once you do modify the JOIN to that you can't use the Query Design to edit your query without going back into SQL view and restablishing the JOIN.

 

[Cronk]

It would be a lot simpler to record the SalesmanID in the Orders table