I have a client who sells products that have width and height attributes: 1. Measurements are taken from an architectural plan or measured onsite 2. Width and height are rounded to the nearest 100mm 3. Staff member looks up the rounded width across columns in a pdf table (or a spreadsheet) then finds the corresponding height 4. Intersection of the column and row gives the base cost per item
NOTE: These base costs are not linear and cannot be calculated (if only!!); each width/height price must be stored.
There are many products (~20) each of which has a different base cost per unit based on its width and height. I have created a spreadsheet version which is fairly straightforward, but I am grappling with how to store this in Access...any articles/examples I have found have price as a single column and based on SKU; simple but not how this pricing model works.
Any ideas on how to structure the data? Huge thanks in advance...
Matt
The simplest schema is a table with three columns: Width, Height, Cost
Define a unique index containing both Width and Height columns. To find a particular cost, search (filter or join) on the Width and Height columns.
This schema will require that you have duplicate width and height values (although each pair should be unique), but that's the nature of relational table design. It may not seems as "efficient" as the visual lookup capability of the original table since in that case you only label each Width column and each Height row once, but the index defined on the table provides the necessary, efficient lookup mechanism.