Prices and Database

If you’re relatively new with Access or even if you have some experience designing databases but never had to store item prices in a database you will be interested in this post.

What’s so special with prices?

Simply put prices change and if you’re not careful you might have big problems in your reports. Let’s look at a simple classic example:

Suppose you work for a legal help centre and you charge the customer a fee per minute based of the type of legal aid they need. So you create a Call Types table and store all the types of legal subjects you support along with the rate for each type. For example your table may look like this:

You also have a Calls table in which you store the information related to calls. For example the date, the time, the duration, the customer and, of course, the call type. The relationships between the Call Types table and the Calls table might look like this:

In the tblCallTypes we find the CallTypeRate which stores the rate per minute. If you want to calculate the Total for a call you would use the expression: Total:CallTypeRate*CallLength. You can use that expression in a query to generate the Total (before GST) to charge the customer.

This would work perfectly well until you are told that there is a price change. How do you think changing the price for the Corporative type from $1.80 to $2.05 would affect your reports? True, all future calls using the Corporative category will be charged $2.05/min.

On the other hand, a dramatic situation crept into your database: All past calls using that category will be calculated using the new rate! Imagine that all categories changed fee a couple of times during the past 12 months and you’re asked to generate a sales report for the last 12 months! The only amounts that would be correct are those since the last price change. All others would be incorrect because they are not using the fee they were charges on that day.

If you thinks that no database developer would allow such a mistake in their application, think again. I do have a customer that have been using a database for years with that exact situation. The solution they came up with was to copy all the data for a period and paste it in Excel and use the price charged during that period. Tedious to say the least.

Solution

Storing the rate for each type of call is a good idea. On the other hand you have to think of these figures as the ‘Fee of the day’ or the current fee and nothing else!

In the table where you store the data describing all calls you also need a CallRate field. When you record a call, the current rate is copied from the CallTypeRate field in the CallTypes table into the CallRate field from the Calls table. Below you can see the result. The new field CallRate is highlighted.

How to copy the current rate in the Calls rate?

To do this you need a couple of basic objects in your form and one very simple line of VBA code. Below is the form allowing basic data entry for calls. Secretly concealed in the Type combobox in the current rate for the selected type of call.

To see for yourself look the RowSource property for that combobox and you will see that 3 fields are used: CallTypeID, CallTypeDescription and CallTypeRate. The Rate textbox is bound to the field CallRate from the table Calls.

The Type combobox has a simple line of code attached to its AfterUpdate event:
Me!CallRate = Me!CallTypeID.Column(2)

This expression is executed whenever the user selected a value from the Type combobox. It simply writes in the CallRate textbox the value of the 3rd column of the CallTypeID RowSource property.

Note: The RowSource property is Zero based. To the first column is referred to as Column(0). The second column is referred to as Column(1) and so on. So combo.Column(2) returns the value in column 3.

Download a copy of this database with the form and a Invoice report.

Hope you enjoyed this blog. In another post I will discuss how to see a history of all the Rate changes over a period of time.

Daniel from ComboProjects