Date Range for Prices - How to handle?

[doozer]

Here is my problem:

I have a database which we use to track customer orders of products that we provide to agents to sell to their customers. We have to keep track of the end-customer for redemptions/cancellations, and pay commissions to agents based on sales.

So, I have a Customer Table, Product Table, Order Table, and Agent Table. These are all tied together via the Order Table (Each Customer buys a Product from an Agent and this is known as an "Order").

We occasionally change the commission levels to the agents and the cost of the Products. However, these are mostly fixed costs and they apply across the board to all products of a type sold by that Agent. We institute changes as of a particular time period (all products sold after October 1st will cost $, etc.)

My concern is that if I change a price or commission now, it effects the commission and income reports for all time periods prior to that when the price change wasn't in effect. So if the price change takes place in October, I don't want that change to cascade to August orders.

I'm thinking that I need a price table with a date range in it and the ability to associate that with commissions and prices. I'm thinking I may have to further distinguish between prices and commissions, but I'm not sure. I'm sure that this issue has been addressed before (hopefully by someone on this forum ) and am looking for advice on setting this up.

 

[M8KWR]

The easiest way to do this would be to insert fields into the relavant tables, and make there default values what you use at the moment to work out commission etc.

You could hide these fields, to certain users and show them for people who coul dalter the value. But the admin person could also alter the default value. This way you can have each records use a different value to work out its calculation, instead of having a unique value across the whole database.

The only thing this does not cover is you date range issue. But would you need one, if the value can be alter as an when you need them to be.

You would have to change the way all the calculations are done, but heopfully you do not have that many...

Hope this helps. I have done this type of thing a financial advisor, who need to alter the commission structure for each product he sold, and within each product there were 3 people who got commission, depending on the value of the sale, the percentages changed...

 

[Rich]

Store the commission rate and the product cost along with the remaining invoice details at the point of sale

 

[doozer]

Rich/M8KWR,

So, If I'm understanding both of you correctly, If I have a Table that has the relationship between Agent-Product-Commission, then when I enter the order in have the form consult that table and store the value it finds in the Order table under a Commission field?

That way no matter how many times I altered it each Order would have their own commission associated with it?

That would work except for the fact that I can have multiple agents associated with a particular deal and each one have a unique commission structure which can be altered. I should've mentioned that earlier. While realistically, we don't have more than 3 agents on a deal, I could see that changing some point down the road.

Am I understanding you correctly?

 

[The_Doc_Man]

When your pricing structure is variable, that is a flare-lit tipoff that you need a pricing structure table. So instead of having a parts table that includes your price for the item, you SPLIT that table as follows...

tblParts
fldPartID, autonumber, prime key (unless you have another prime key of your own)
etc etc etc - descriptive data for the part

tblPartPrices
fldPartID, long, foreign key (matches ID in parts table, so again if you have another key of your own, put that here)
fldPrice, currency
fldStartDate, date - first day on which this price is valid.
fldEndDate,date - last day on which this price is valid.

Now, if you have different prices on the same day depending on who sold the item,... keep on adding fields. Like, ...

fldAgentID - shows the price if this particular agent sold it.

OR if the commission structure is the same for every part an agent sells, put the agent ID in the sales detail table (line-item table) and use the ID to pick out the commission structure from the AGENT table, not the PART table.

We cannot advise you directly on what you need here because you have to apply YOUR business model.

Suppose that it was a commission structure and you only allowed, say, ten different structures. Then instead of adding an Agent ID, you would put a commission structure ID and get your commission data from there.

But suppose that it really WAS the case that any agent could apply a different commission structure to each part... (egad, what a witch's brew of a business model) - OK, then you have to have a table that tracks what commission structure an agent is allowed to apply to a part. That would look like this...

tblAllowedCommissions
fldAgentID - identifies the agent by code number
fldPartID - identifies the part to which this permission applies
fldCommID - identifies the commision structure allowed for this Agent/Part combination.

Now you might have to add dates to these entries if the ability to use that commission structure changes with time. I.e. add dates like
fldFirstUse - date this Agent/Part/Comm group is first legal
fldLastUse - date this group is last legal.

But think of how many entries you would have to maintain. If this is really your business model, it is possible to design Access to do it, but your data entry overhead becomes horrendous. Also, does it ever occur that an agent has discretion over which commission structure to use for a single part? THEN you have to also track which structure was actually used, and that would have to be part of your detail-level sales invoice (line-item) table.

You folks can have any model you want, but some models will be far easier to program than others. Yours doesn't seem to be one of the easier ones.