This month Rebecca Riordan looks at implementing a common business rule. In fact, she provides two methods for handling the common case of multiple prices for a given product, depending on the quantity ordered–the customer pays $1.79 each for buying up to nine items, but only $1.69 each when buying 10-14 items, and so on.

I’ve always been impressed with the Northwind sample database. Yes, it’s true that it’s slightly artificial, and perhaps not all of the code is as efficient as it might be. But as a sample database bundled with a commercial product, it’s as close to a production application as you’re likely to get. I truly wish I had a dollar for every person who’s learned the fundamentals of database design by adapting Northwind to their needs.

But there’s one common situation that Northwind doesn’t address: multiple prices for a product, depending on the quantity purchased. This situation is common in any organization that typically sells multiple units of its products–hardware (who buys a single nail?), paper goods, even lighting fixtures.

I’ve seen a variety of ingenious, if ineffective, solutions to this problem. The most common is to create multiple price fields, as shown in Figure 1. Until you try to implement it, this might look like a good solution. After all, even so-called experts denormalize tables in this way on occasion. (As a matter of fact, I’ll do it myself later in this article. While I don’t claim much in the way of expertise, I do have a claim on experience and common sense.)

Figure 1

But implementing a lookup against this structure is a nightmare. We’re all so spoiled by Access’ row fix-up (the mechanism by which Access will automatically look up the values on the many side of a relationship) that we expect related values to pop up automatically for us. With this design, however, you’ll find yourself writing ugly “Select Case quantity <= x And quantity > y” statements in the Exit event handler of an enormous number of form controls.

Even if you’re more patient about writing If statements than I am, the structure shown in Figure 1 embeds the pricing structure of your business rule in the field names of your database. Trust me: Putting data in a field name is always a bad idea. The reason, of course, is that data changes, and before you know it you’ll be looking at the behemoth shown in Figure 2. Just look at all those zeros. And imagine what your If statement will start to look like: “If quantity <=x And quantity > y And rs.PriceXtoY > 0 Then…ElseIf…ElseIf…” I don’t care how much you get paid per hour; life is just way too short.

Figure 2

Getting the data design right

I’m sure that it will come as no surprise to discover that the solution is to split the original table up. There are two ways to do this, depending on how much flexibility is required. The most flexible method is shown in Figure 3. The original table has been divided in two–a Products table and a ProductPrices table, with a one-to-many relationship joining the two.

Figure 3

The Quantity field in this table contains the smallest quantity that qualifies for that price. There’s no need to specify the top range (as Doug Steele demonstrates in this month’s “Access Answers” column). By the way, the ProductPrices table actually contains the ProductID field, and the primary key of the ProductPrices table consists of both the ProductID and Quantity fields. You don’t see the ProductID in my figure because Access hides the foreign key value when displaying sub-datasheets.

Implementing an order form based on this schema is much simpler than using the flat structure shown in Figure 1, but you still can’t rely on the row fix-up feature to look up your prices. But now, instead of writing complex conditional statements, you only need a single query and a relatively simple, generic Exit handler. The SQL for your generic Exit routine looks like this:

PARAMETERS theID Long, theQty Long;

SELECT TOP 1 ProductPrices.ProductID,

ProductPrices.Quantity, ProductPrices.Price

FROM ProductPrices

WHERE ProductPrices.ProductID = [theID]

AND ProductPrices.Quantity <= [theQty]

ORDER BY ProductPrices.Quantity DESC;

The statement declares two parameters, theID and theQty, both Long Integers that will receive (respectively) the ProductID and the quantity entered on the form. The Where clause does the first part of the work, selecting rows that have the specified ProductID and a quantity less than or equal to the specified quantity. The Order clause sorts the rows in descending order of quantity, and the Select clause specifies that only the Top 1 row be returned. Combined, these two clauses mean that only the single row with the highest qualifying discount will be returned. Given the table shown in Figure 3, for example, a ProductID of 2 and a quantity of 107 would return $1.49–exactly what you want.

Figure 4 shows a form that uses the query. The form is shown in Continuous Forms view. Typically you would embed this form, as a subform, in another form containing customer or order information, but that data is irrelevant to my example.

Figure 4

In order to make the sample work, however, there’s a little sleight-of-hand going on. The form is bound to the LineItems table, shown in Figure 5. That table contains a field for extended price. Both relational database theory and conventional wisdom say that you should avoid storing values that you can calculate on the fly, and, in fact, extended price is the classic example of what not to store in a table. But Access will only allow a single value for a calculated control on a form. If I calculated the extended value for any row and set a control to that value, that value would be repeated on every row of the grid. By storing the extended price in the LineItems table, the sample form will show the value for each row, not the last value calculated. (I warned you I was going to denormalize, and now I have.)

Figure 5

This denormalization isn’t the end of the world, provided that you understand why the rule exists. The danger with calculating the extended price is that the price or quantity may change without the extended value being recalculated. You could finesse this problem in a full-fledged server database by writing a trigger that recalculated the extended price whenever the price or quantity changed. However, there’s a simpler answer: Never use the extended prices field. Just because the field has to be there to make an Access user interface work, it doesn’t mean that you should count on it anywhere in your application. As you’ll see, I recalculate this field as part of displaying the form, just to make sure that the data is right. Another alternative would be to set up a temporary table, with the extended price, just to be used with this form. You’d then copy data from the “real” order details table into this temporary table to be used with the form. When the user was finished with her changes, you’d update the order details table with the data from the temporary table and then discard the extended price.

Building the UI

The Exit event handler of the Quantity control is shown in the following code. There’s nothing particularly tricky here. The procedure declares two variables (a QueryDef and a Recordset), opens the QueryDef, and then assigns the values contained in the form controls to the appropriate parameters:

Private Sub Quantity_Exit(Cancel As Integer)

Dim qry As DAO.QueryDef

Dim rs As DAO.Recordset

Set qry = CurrentDb.QueryDefs("PriceGroups")

With qry

.Parameters("theID") = Me.ProductID

.Parameters("theQty") = Me.Quantity

Set rs = .OpenRecordset(, dbReadOnly)

End With

Me.UnitPrice = rs!Price

Me.ExtPrice = Me.UnitPrice * Me.Quantity

End Sub

Once the parameters have been assigned, the procedure opens a recordset against the query, assigns the unit price, and calculates and assigns the extended price. This is all standard data manipulation code; the magic happened in the query you’ve already looked at. And that’s it. Not an If or Select statement to be found. You’ve effectively duplicated Access’ intrinsic row fix-up functionality.

If you refer back to Figure 3, you’ll see that the pricing information is duplicated for each ProductID. This is the most flexible schema, but it can result in a lot of duplicate information.

I have in front of me a catalog for a specialty paper company (and you wondered where I got the ideas for my articles). Their mulberry paper comes in 27 different colors, with different product numbers and descriptions, but all with the same quantity discounts. In situations such as this, it’s more convenient to group quantity prices rather than maintain records for each individual product. Maintaining 27 records that are identical except for a single field would be error-prone, not to mention tedious. The adjustment is simple to make. As shown in Figure 6, you only need to add a PriceGroup field to the Products table. The table containing the price groups is identical to the ProductPrices table, although you’ll want to rename the ProductID field. (I used PriceGroupID.)

Figure 6

The SQL statement for the “magic query” that retrieves the appropriate price is shown next. As you can see, it’s almost identical to the original–the only difference is in the fields returned by the Select clause. The Exit handler for the form is identical to the one in my previous code sample:

PARAMETERS theID Long, theQty Long;

SELECT TOP 1 ProductGroupPrices.PriceGroupID,

ProductGroupPrices.Quantity,

ProductGroupPrices.Price

FROM ProductGroupPrices

WHERE ProductGroupPrices.PriceGroupID = [theID]

AND ProductGroupPrices.Quantity <= [theQty]

ORDER BY ProductGroupPrices.Quantity DESC;

The query for retrieving a price by group rather than ProductID is almost identical. This is a simple variation to the schema that can save you a lot of typing and maintenance, but notice that the two approaches aren’t mutually exclusive. You’re not required to have more than one product in a price group, so you can easily combine both single-product and group prices in the same schema. Be careful, however, of creating artificial price groups. Just because mulberry paper and watercolor brushes happen to have the same price structure today, it doesn’t follow that they’ll stay that way forever.