this might help explain
And what tables, relationships have you set up?
The basic set of invoicing tables and relationships are a good start:
WHere you use a portal to lineItems and add new records in that portal to list the materials.
From there, there are several different approaches that can supply a thickness based price:
You can match by LineItems to Materials by both a Material ID and a thickness to look up the price. This requires one record for each thickness of material.
LineItems::_fkMaterialID = MaterialsThicknessPrices::_fkMaterialID AND
LineItems::Thickness = MaterialsThicknessPrices::Thickness
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Or you can set up several fields in each Material record to document the different thicknesses and their prices. An auto-enter calculation in LineItems can then copy data from different fields in Materials by what thickness is specified.
The first option is more flexible as you can have as many different thicknesses as you need and adding more thicknesses is a matter of adding more records to your table, and reports listing out all your thicknesses and current prices are pretty straight forward. But if your materials only have a few thicknesses, the second option may be simpler to set up and to maintain the different prices.
it's not the relationship i'm having problems with here its more the calculation side of things, i.e. i will have lots of materials with lots of different widths and thicknesses. However if have have a material that is 120 wide and another one that is 150 wide but both are 50 thick and i need a material that is anywhere inbetween 121 and 150 than i want it to lookup the price for 150. so it looks at my table of matching material names then looks at thickness and then looks at the nearest width to the next width up. i don't think an if statement on its own can handle this and i was looking at a case statement but still with no joy.
it's not the relationship i'm having problems with here
Yet it's the relationships that you choose to use that will control what pricing info is looked up. Since you didn't describe what tables and relationships that you are using, I had to start with those for any answer that I post.
There are two basic approaches you can implement and I can't choose one for you.
If you can devise a formula that uses dimension info to calculate the "piece price" from a looked up unit price, we can implement that. But I'd need to see that formula. Such a formula can even be different for each type of material as the formula can be looked up right along with the unit price.
Without a formula, you need some method for using a) the type of material and b) it's dimensions to look up that price. My previous post described an approach for that. If you need to use more factors than the thickness, (such as the width), a relationship that matches by more fields could be used or a more complex calculation and multiple fields can be employed.
i don't think an if statement on its own can handle this
I agree and did not suggest that you use one. A CASE function, on the other hand, could do this, but the other option where you have one related record for each individual price will be much more flexible.