Structure Question 1

[fredka]

I have an existing database that tracks sales of external sales companies that we use to distribute our product.

Historically, we have tracked these agencies using a field named "masterbrokerkey" However, one of the outside companies may actually be two companies that together make up one of our masterbrokerkeys (basically they merged and sell under one name for us)

Now, I need to track one of these masterbroker key fields individually.

I do have another field that I use (brokerkey) for each of the individual agencies - therefore, I would have two brokerkeys under one masterbrokerkey.

My question is regarding table set-up. I have a table that holds the monthly results (tblsummarynet) and it is based on masterbrokerkey. The table tracks net growth so I have the following fields:

masterbrokerkey, year, month, base, current, growth

(growth is a % of growth that is ytd)

My question is now that I have to track these seperately, do I just add the brokerkey to the table? The problem is that I have several queries that use this table at the masterbrokerkey level and pull the "growth" field. Once I add the brokerkey I would have to calcuale the growth by masterbroker before using it in each of these queries that now just pull the growth number from the table.

I am thinking that this is probably the way to go but I am losing the fact that the growth field is in the table and now needs to be calculated.

I guess the other option is to create a seperate summary table by brokerkey.

Looking for advice - thanks!!

 

[rac2]

So there was a company 75 that grew tremendously because it merged with company 83. If it was just incremental growth due to ordinary sales activities, there would be no problem.

Suppose it was always the case that a company could have multiple brokers. When they acquired a new broker, you added another one. But when a broker moved from one company to another one on your list, then what? Could you re-assign the broker with no problem?

Possibly there is an interest in projecting backwards, or looking at the two companies combined history to get a trend as if they had always been one. Maybe that is a temptation simply because here you have the data for both companies; in other cases when a new company came on board, you had no history for it so the interest never arose.

What about creating a new company, 215, and give it the combined history of interest.

Is this a matter of a monthly data-point in one or two reports, or is it a pervasive issue involving all aspects of the business such as inventory, bonuses, supervision, etc?

Just some thoughts. HTH

 

[fredka]

The main issue is that I have one one masterbroker key with two seperate broker keys - For this masterbroker key I now have two internal sales reps.... each assigned to one of the two broker keys

Each month I get a detail report that shows each broker keys year to date results. I have always taken that data and appended it to a table that shows each masterbroker keys ytd results. (for example, the summary table has:

masterbrokerkey, year, month, base, current, ytd%

The problem is that I need to also run sales rep reports showing the same information that is in my summary table. I would previously just link up a goals table by masterbroker key.

However, now that a masterbroker may be split having two internal sales reps, I cannot use the summary table for the sales reps.

Do I change my summary table so that it now has more detail (at the broker key level rather than the masterbroker key level) ?? I would now have to change all my queries to calculate the masterbroker key level summary.

Or, do I create a seperate summary table at the broker key level - that seems likd duplication.

?? thanks!!!

 

[rac2]

I would now have to change all my queries to calculate the masterbroker key level summary.

Have all of your queries been based on the assumption that each sales rep belongs to one and only one broker and each broker belongs to one and only one master broker? If so, then yes you will need to change all of your queries.

Add a column to the summary table for broker id or sales rep id or add both.
When you need a report by masterbrokerkey, write a query to GROUP BY masterbrokerkey; when you need a report by broker, GROUP BY brokerID; when it is sale rep, GROUP BY sales rep id.

If the goals are for masterbroker then using them for brokers or sales reps does not make sense. Whichever entity (master broker, broker, or sales rep) has the goal, that is the ID to use in linking to a goals table.

I dont see any reason to create a separate summary table for the broker summary.

 

[fredka]

There are actually goals for master broker and also for sales reps. My dilema was..... is it better to change the table like you say (basically, I would add the brokerkey) and change all of my queries to group properly first or do I create a seperate table for the broker level.

The main difficulty I had was that the summary table now holds the ytd net % each month - so it was simply a matter of taking those ytd percentages rather than having to calculate them.

to summarize, is it better to do a lot of work changing the structure of queries and needing queries to group or do I just make a new table @ the broker level.

The majority of the reporting is at the masterbroker level.

What do you think ? thanks!!

 

[rac2]

I think you have answered your own question.

It sounds like the tables you are talking about are created and loaded for reporting purposes only. So you do the calculations one time and store them. If separate tables make sense and is easier for you to use, that is the way to go.

Unless you are working for Global Distributing with 100,000 sales reps, you wont notice the difference between a GROUP BY and a simple SELECT query. I mean it wont be a performance issue. So do it the easy way.

One thought, be sure to have a column in the broker table for the masterbroker key, just in case someone asks you to report on Joe and Bobs percentage of the masterbrokerkey they share in common.

 

[fredka]

Great - thanks for your help!!!

Fred