Set Up Price Tables

[Jo22]

Hello,

I am trying to set up a database for ordering products. I have about 100 different products. Each of these products has its own price grid which prices the product based on the width and length ordered.
eg.
.....12...24.. 36..42 (Width)

12 $50 $60 $70 $80

24 $55 $65 $75 $85

36 $60 $70 $80 $90

On my Order form I have set up a combo box to select the product, and there is wdth and lngth text field for entering the width and length criteria. I would like to have a Price text box which looks up the price from the price grid based on the criteria entered in my form. (If width = 30 and length = 25, it should return a value of $80).



What is the best way to set this up? Should I use excel worksheets linked to the database or is there an easy way to do this from ms access tables?

[June7]

Set up table like:

RecID | ProdID | Length | Width | Price
1 | 1 | 12 | 12 | 50 
2 | 1 | 12 | 24 | 60
3 | 1 | 12 | 36 | 70
4 | 1 | 12 | 42 | 80
5 | 1 | 24 | 12 | 55
6 | 1 | 24 | 24 | 65
7 | 1 | 24 | 36 | 75
8 | 1 | 24 | 42 | 85
...

Make the width and length fields number type. If all the products follow the same grid layout, will be about 1200 records.

RecID is primary/foreign key.

User selects product from combobox, RecID is saved.

Two ways to display the price data.

1. The textbox could have expression:
DLookup("Price", "tablename", "RecID=" & Me.cbxProduct)

2. Join the product table to the order detail table.

This table setup assumes price for each dimension pair will be different for each product. If 12 x 12 will always be $50 regardless of product, need to rethink this.

[Jo22]

Thanks June 07. Yes, price for each dimension pair will be different for each product.

I am not sure how the expression "DLookup("Price", "tablename", "RecID=" & Me.cbxProduct)" would retreive the correct price. My form has a combo box to select the product. The width and length dimensions must be entered in text boxes. The price then needs to be determined based on the values in the width and length fields.

Let's say I entered 14 for the width and 18 for the length. Since there is no RecId for 14 X 18 dimension, the expression in the Price textbox would need to return the value that is in RecID 6. (width and length needs to round up). I can do this fairly easily in excel, but I am at a loss in MsAccess.

[June7]

Two ways to approach the dimension selection.

1. A multi-column combobox that lists all the records from the product table. User selects the product/width/length combination from the combobox. Do not have to enter width and length in separate textboxes. User selection saves the RecID. Price is retrieved as described in previous post. This utilizes relational db concept.

2. User must select product, enter width and length, each in separate controls. The DLookup would have multiple criteria. The RecID is not saved or referenced. This does not utilize relational db concept.
DLookup("Price", "tablename", "ProdID=" & Me.cbxProduct & " AND Width=" & Me.tbxWidth & " AND Length=" & Me.tbxLength)

If there is a possibility price could change, need to decide whether to save the retrieved price or to structure the Product table to accommodate the price changes. Restructure would mean another field called Active (Yes/No). Enter new records for the revised price. Then for no. 1 the combobox row source would include only active and for 2. would need a 4th criteria for the DLookup " AND Active=True".

EDIT: Just reread your post and caught the last paragraph and now I see the issue presented in the first post. That really throws a wrench into the works. Basically makes related records approach unworkable. Yes, I suppose VLookup in excel handles this easily. Maybe setting the DLookup length and width criteria to >= instead of = will produce correct result. It should return value from first record that meets criteria. Also means need code to save the price value.

[Jo22]

June 7 - Thanks again for your post. The sizes I provided for the price grid were just a sample. The whole grid has about 100 values. This was obviously too much for a drop down list. I went with the DLookUp approach that you suggested, and while I did have to work on the syntax to adjust to my field types, this method worked (to a point).

I just read your Edit and I am glad you see my problem. Currently, as long as I enter a width and length value that matches the grid, it will return the price value. I have the price value bound to a field in my order table and it seems to be saving Ok. The problem is what to do with the in between sizes that don't match exactly to the grid values. I will try the >= and post back later today on the results.

Now that you see the whole picture, if there are any other suggestions you can make, I do appreciate it. And yes, this is an easy job for Excel, but MsAccess - Yikes, it's brutal!

[Jo22]

Hello June7!

I tried the >= and it works perfectly.

To recap:

(1) We set up the tables as you suggested. The "PriceTable" is linked to the "Products" table by ProductID.

(2) The form "Orders" has a combo box for selecting the [ProductID] along with two text box fields, [Wdth] for entering the width and [Lngth] for entering the length value. There is a third text box Price, which automatically populates based on the code in the AfterUpdate event of the [Lngth] field.

(3) In the AfterUpdate event of the [Lngth] field, I placed the following code:
Price = DLookup("Price", "PriceTable", "ProductID = " & Nz(ProductID, "") & " And Wdth >= " & Nz([Wdth], 0) & " And Lngth >= " & Nz([Lngth], 0))



It is working great! Now I just have to go to the Order form, select the product from the combo box, enter any number I want for the width and length fields, and it will go find the correct price and return that value to my Price field.

Thank you for all your assistance. I truly appreciate it.

Jo