Sum Total Sales Revenue by Sales Rep coving Multiple Regions in Different Months

[macmill]

Hi,

I've run into a problem I just cannot figure out. I have two tables. The first has region number and Total Sales for that Region. I have 193 Regions. My other table has sales rep's with multiple columns to account for the region they cover. For example, the sales rep can be assigned to one region for the first three months and then assigned to region one and region two for the next three. So in the table you would see their employee number in column 1 and then their first region assignment in column two. On the next row the same sales rep will have their employee number again in column 1, their first assigned area in colum two and the third assignment in colum 3. To continue this example, it is possible that for the last 4 months of the fiscal year they are assigned to a different region, so my table will show the employee number again and their new assiment in column 2. The repeating of rows made sense to me as I'm trying to account for the movement of the sales rep and their contributed to the Total Sales they contributed towards during the fical year.

How would you compile the Total Sales for each assignment? The math is straight forward as the total sum would be region 1 + region 2 which I could then proportionally apply towards the months they were assigned but I am struggling to get Access to do this.

I tried using DSum but it does not seem to like evaluating each of the columns where the region is assigned. I also tried using IIF, but it would work for the first region but didn't work once I added the next region column.

I was able to figure this out in Excel. If it is helpful, this is my formula:

{=SUM(IF((Sheet1!A:A=Sheet2!B5)+(Sheet1!A:A=Sheet2 !C5)+(Sheet1!A:A=Sheet2!D5)+(Sheet1!A:A=Sheet2!E5) +(Sheet1!A:A=Sheet2!F5)+(Sheet1!A:A=Sheet2!G5)+(Sh eet1!A:A=Sheet2!H5)+(Sheet1!A:A=Sheet2!I5)+(Sheet1 !A:A=Sheet2!J5),Sheet1!C:C,0))}



Sheet1 holds the Total Sales by region and Sheet two holds the sales rep assignment by column. Essentially its looking at region assignment in B5 and matching it to the total sales table in Sheet1 plus region assignment in C5 and total sales in sheet 1 plus region assignment D5, etc. The formula looks long because the worst case situation is a sales rep assigned to nine regions during a few month period.

Any help is appreciated.
macmill

[June7]

Doesn't sound like a normalized data structure which would probably be more like:

RepID Yr Mo RegionID SalesAmt

Instead of saving a figure for annual sales in first table, should calculate the annual sales from the second table.

Provide sample data in table structure. Either build tables in thread post or attach a db file.

[macmill]

Doesn't sound like a normalized data structure which would probably be more like:

RepID Yr Mo RegionID SalesAmt

Instead of saving a figure for annual sales in first table, should calculate the annual sales from the second table.

Provide sample data in table structure. Either build tables in thread post or attach a db file.

Please see attached data base with the two tables. I've tried linking each managed area to the Region but I end up with no data. Appreciate any advice.


macmill

[orange]

macmill,

You really need to research some basic database concepts --tables, normalization, primary key, relationships.
I'm not sure what you expect of Access (database), but I can assure you that it will not do anything you haven't told it.
Good luck.

[macmill]

macmill,

You really need to research some basic database concepts --tables, normalization, primary key, relationships.
I'm not sure what you expect of Access (database), but I can assure you that it will not do anything you haven't told it.
Good luck.

Orange,

Really not helpful. I'm reaching out for help. Is it possible to do this in Access? I did share how I am accomplishing this in Excel.

[ArviLaanemets]

Access isn't Excel! Even for Excel, your table structure was far from optimal, any you have yourself experienced first of difficulties you will have with it in Access.

The current problem is, than for every column you must use a different dataset for calculation. There principally is a method for it - you can use a query with INNER JOIN to link several instances of same table, but there is a limit, how much such joins you can have in a query - and this number is much-much lower than 193 (actually it is 16 or less).

So design a better table, and import your current data into it (probably you have to run 193 queries for it, but maybe there is an easier way), and after that you can create your report without problems (or you can ask for advice again).

[June7]

It's certainly possible in a properly designed relational database. You do not have that. Forget what you know about Excel when designing relational database. Providing the Excel formula is not helpful.

There is no way to know which of the 46 regions each SalesRep/Area belongs in. You need another field in SalesRep for the RegionID or another table that relates employees to regions or areas to regions.

The SalesRep table really should be a normalized structure.

Some employees have multiple records in SalesRep table. Is that valid? Why?

[orange]

macmill,

I was hoping you would research the terms using Google.
Yes, it can be done using Access, but it needs to be designed to make proper use of Access.
Here is a link with info to specific database concepts and planning.

Good luck

[macmill]

Access isn't Excel! Even for Excel, your table structure was far from optimal, any you have yourself experienced first of difficulties you will have with it in Access.

The current problem is, than for every column you must use a different dataset for calculation. There principally is a method for it - you can use a query with INNER JOIN to link several instances of same table, but there is a limit, how much such joins you can have in a query - and this number is much-much lower than 193 (actually it is 16 or less).

So design a better table, and import your current data into it (probably you have to run 193 queries for it, but maybe there is an easier way), and after that you can create your report without problems (or you can ask for advice again).

I do not think that many queries would be needed. I venture to suggest 9 queries at most because that is the largest number of regions a sales rep is assigned. Thank you for putting me in a direction. I will take a look at INNER JOIN's.

Regards,
Macmill

[June7]

INNER JOIN on what fields? These tables do not have a relationship.

[macmill]

It's certainly possible in a properly designed relational database. You do not have that. Forget what you know about Excel when designing relational database. Providing the Excel formula is not helpful.

There is no way to know which of the 46 regions each SalesRep/Area belongs in. You need another field in SalesRep for the RegionID or another table that relates employees to regions or areas to regions.

The SalesRep table really should be a normalized structure.

Some employees have multiple records in SalesRep table. Is that valid? Why?

Yes it is valid. The sales rep Employee ID can be listed multiple times because they were assigned to a different managed area. Also my SalesRep table is the table that defines where the sales rep was assigned for each of the 46 regions.

[June7]

And the light bulb comes on, although rather dimly. I was seeing the data in SalesRep as dollars and now I see it is the Region. However, I see the same rep with more than one record for the same region.

EmployeeID	Managed Area1	Managed Area2	Managed Area3	Managed Area4	Managed Area5	Managed Area6	Managed Area7	Managed Area8	Managed Area9
1364626	11304	11306	11308
1364626	11304	11306	11307

Not clear to me what you want to accomplish with these two tables. The Excel formula sums data yet the TotalSales table is already aggregated data. How should this be related to the SalesRep table?

A UNION query can rearrange data to normalized structure. And if you want EVERY value even if it creates a duplicate record, use UNION ALL, otherwise remove ALL.

SELECT EmployeeID, [Managed Area1] AS Region, 1 AS SID FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area2], 2 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area3], 3 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area4], 4 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area5], 5 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area6], 6 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area7], 7 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area8], 8 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area8], 9 FROM SalesRep;

Now use that query like a table in subsequent queries.

The query can join to the TotalSales table to associate all reps that contributed to the region sales but the same sales data will repeat.

SELECT TotalSales.Region, TotalSales.FieldFESalesPlan, TotalSales.FieldFESalesActuals, Query1.EmployeeID
FROM TotalSales LEFT JOIN Query1 ON TotalSales.Region = Query1.Region;

Shows that region 10012 has no reps.

[ArviLaanemets]

I will take a look at INNER JOIN's.

Sorry! My mind was wandering - I wanted type LEFT JOIN's. But June's Union query will be easier. And it has a limit for 32 tables joined, which is more than 16 - but anyway much less than 193 again.

The largest number of regions being 9 doesn't help much too. Yes, you can design a query for 9 regions easily, but you have to design a separate query for every sales rep, as every one of them has different set of regions.

When you can use SQL Server, then there you can have several hundred tables in UNION query. In this case, you can import your table into SQL Server, design a new table there, import data from old table into new one with a single query, and after that import the new table into access.

Edit: At start you did write you have 193 regions. In post #11 it is 46 regions. How much really?

Btw. I had a look at our DB. In your starting post you comment, that in SalesRep table some data are for first 3 months, and some data for last 4 months - but there is no column to determine for which month which data belong to. You are saying to Access "Go there, I don't know where, and bring me that, I don't know what!".

[macmill]

And the light bulb comes on, although rather dimly. I was seeing the data in SalesRep as dollars and now I see it is the Region. However, I see the same rep with more than one record for the same region.

EmployeeID	Managed Area1	Managed Area2	Managed Area3	Managed Area4	Managed Area5	Managed Area6	Managed Area7	Managed Area8	Managed Area9
1364626	11304	11306	11308
1364626	11304	11306	11307

Not clear to me what you want to accomplish with these two tables. The Excel formula sums data yet the TotalSales table is already aggregated data. How should this be related to the SalesRep table?

Sales Rep 1364626 held the first grouping of regions (11304;11306;11308) for the first six month of the year and then (11304;11306;11307) for the last six months of the year. The difference is Managed Area3 (11308 vs 11307). What I am trying to do is sum the total sales of each region (Region 11304 ($47,814,118.21)+Region 11306 ($50,847,562.31)+ Region 11308 ($52,282,152.23)= $150,943,833. This is from the FieldFESAlesActuals fields on the TotalSales table. I would also do this same thing for FieldFESalesPlan (total sum is $152,737,166).

Maybe a little more background about the whole process would be helpful. Once I have everything summed, I then divide the two ($150,943,833/$152,737,166= .988). With this result, I have a matrix that reflects a bonus amount. Lets say this would be a $10,000 bonus. Since the sales rep held this combination of regions for the first 6 months, it is prorated by 6/12=.5, or $5,000. Working through the same process with the other combination of regions ($152,620,155/$154,399,093=.988). Just so happens the results are the same in this example and the proration .5 for these regions will be $5,000. The last task is then to sum the two bonus amounts ($10,000) for a total bonus payment. Hope this explanation gives some background to the whole process but my biggest challenge is summing these regions together.

Thank you for your directions below.





A UNION query can rearrange data to normalized structure. And if you want EVERY value even if it creates a duplicate record, use UNION ALL, otherwise remove ALL.

SELECT EmployeeID, [Managed Area1] AS Region, 1 AS SID FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area2], 2 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area3], 3 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area4], 4 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area5], 5 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area6], 6 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area7], 7 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area8], 8 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area8], 9 FROM SalesRep;

Now use that query like a table in subsequent queries.

The query can join to the TotalSales table to associate all reps that contributed to the region sales but the same sales data will repeat.

SELECT TotalSales.Region, TotalSales.FieldFESalesPlan, TotalSales.FieldFESalesActuals, Query1.EmployeeID
FROM TotalSales LEFT JOIN Query1 ON TotalSales.Region = Query1.Region;

Shows that region 10012 has no reps.

Sales Rep 1364626 held the first grouping of regions (11304;11306;11308) for the first six month of the year and then (11304;11306;11307) for the last six months of the year. The difference is Managed Area3 (11308 vs 11307). What I am trying to do is sum the total sales of each region (Region 11304 ($47,814,118.21)+Region 11306 ($50,847,562.31)+ Region 11308 ($52,282,152.23)= $150,943,833. This is from the FieldFESAlesActuals fields on the TotalSales table. I would also do this same thing for FieldFESalesPlan (total sum is $152,737,166).

Maybe a little more background about the whole process would be helpful. Once I have everything summed, I then divide the two ($150,943,833/$152,737,166= .988). With this result, I have a matrix that reflects a bonus amount. Lets say this would be a $10,000 bonus. Since the sales rep held this combination of regions for the first 6 months, it is prorated by 6/12=.5, or $5,000. Working through the same process with the other combination of regions ($152,620,155/$154,399,093=.988). Just so happens the results are the same in this example and the proration .5 for these regions will be $5,000. The last task is then to sum the two bonus amounts ($10,000) for a total bonus payment. Hope this explanation gives some background to the whole process but my biggest challenge is summing these regions together.

Thank you for your directions below.

[June7]

The following query sums the TotalSales data for each rep.
SELECT Sum(TotalSales.FieldFESalesPlan) AS SumOfFieldFESalesPlan, Sum(TotalSales.FieldFESalesActuals) AS SumOfFieldFESalesActuals, Query1.EmployeeID
FROM Query1 RIGHT JOIN TotalSales ON Query1.Region = TotalSales.Region
GROUP BY Query1.EmployeeID;

However, if you need the data aggregated by 6-month periods, then need another field in SalesRep to identify the period (1 or 2).

Rep 1621098 has 3 records. Possibly need to delete the first one and renumber the other 2.

EmployeeID	Period	Managed Area1	Managed Area2	Managed Area3
1621098	1	11201
1621098	2	11201	11202	11209
1621098	3	11203	11209	11210

Revise the UNION query:

SELECT EmployeeID, Period, [Managed Area1] AS Region, 1 AS SID FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area2], 2 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area3], 3 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area4], 4 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area5], 5 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area6], 6 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area7], 7 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area8], 8 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area9], 9 FROM SalesRep;

Then the aggregate query:
SELECT Sum(TotalSales.FieldFESalesActuals) AS SumOfFieldFESalesActuals, Sum(TotalSales.FieldFESalesPlan) AS SumOfFieldFESalesPlan, Avg([FieldFESalesActuals]/[FieldFESalesPlan]) AS Pct, Query1.EmployeeID, Query1.Period
FROM Query1 RIGHT JOIN TotalSales ON Query1.Region = TotalSales.Region
GROUP BY Query1.EmployeeID, Query1.Period;

There will be a record with no EmployeeID because region 10012 has no reps.

BTW, a UNION query has a limit of 50 SELECT lines - I know because I've hit that limit.