D
datadyl
I'm trying to create a report for each of our sales reps showing the market
share of their respective clients.
Each sales rep has a list of clients.
Each client has a list of state and counties they ship to within the U.S.
Some state and counties have more than one client shipping to it, while some
may have none.
What I'd like to have is a report that shows the following for each sales rep:
===REPORT===
CLIENT STATE COUNTY CLIENT_SALES INDUSTRY_SALES
ClientA Tennessee Sullivan 20 200
ClientA Tennessee Union 45 100
ClientA Virginia Buchanan 5 20
ClientA Virginia Dickenson 3 15
Total for ClientA: 73 335
% Share of Industry Sales 22%
ClientB Tennessee Scott 100 150
ClientB Tennessee Union 10 100
Total for Client B: 110 250
% Share of Industry Sales 44%
Total for All Clients 183 585
% Share of Industry Sales 31%
===
As you can see, since both clients have shipped to Union county in
Tennessee, the "Total for All Clients" of 183 is correct; however, the
"INDUSTRY_SALES" total that is calculated for All Clients double counts
Union, Tennessee amount. The correct totals should be as follows:
===
Correct Total 183 485
% Share of Industry Sales 38%
===
I have a table that lists INDUSTRY_SALES for each state and county in the
U.S. and a table that lists all of our client sales to each state and county.
As noted, many clients ship to the same state and county so there may be more
than one record per state and county group in that table.
Initially, I tried to base the report on a make-table query so that I could
sort the clients in descending order of total sales for each sales rep's
report but I continue to get the duplicated total for INDUSTRY_SALES.
I've spent hours searching this forum, trying different tips, but I haven't
found any that apply to my situation. Any advice would be appreciated. Thanks.
share of their respective clients.
Each sales rep has a list of clients.
Each client has a list of state and counties they ship to within the U.S.
Some state and counties have more than one client shipping to it, while some
may have none.
What I'd like to have is a report that shows the following for each sales rep:
===REPORT===
CLIENT STATE COUNTY CLIENT_SALES INDUSTRY_SALES
ClientA Tennessee Sullivan 20 200
ClientA Tennessee Union 45 100
ClientA Virginia Buchanan 5 20
ClientA Virginia Dickenson 3 15
Total for ClientA: 73 335
% Share of Industry Sales 22%
ClientB Tennessee Scott 100 150
ClientB Tennessee Union 10 100
Total for Client B: 110 250
% Share of Industry Sales 44%
Total for All Clients 183 585
% Share of Industry Sales 31%
===
As you can see, since both clients have shipped to Union county in
Tennessee, the "Total for All Clients" of 183 is correct; however, the
"INDUSTRY_SALES" total that is calculated for All Clients double counts
Union, Tennessee amount. The correct totals should be as follows:
===
Correct Total 183 485
% Share of Industry Sales 38%
===
I have a table that lists INDUSTRY_SALES for each state and county in the
U.S. and a table that lists all of our client sales to each state and county.
As noted, many clients ship to the same state and county so there may be more
than one record per state and county group in that table.
Initially, I tried to base the report on a make-table query so that I could
sort the clients in descending order of total sales for each sales rep's
report but I continue to get the duplicated total for INDUSTRY_SALES.
I've spent hours searching this forum, trying different tips, but I haven't
found any that apply to my situation. Any advice would be appreciated. Thanks.