I don't see how the first table is involved in producing the results that you want. It would seem that the data you need for your result is all specified in tables 2 and 3.
It looks like you need to match records between table 2 and 3 like this:
Table 3::Area = Table 2::Area AND
Table 3::Unit = Table 2::Unit
This assumes that combining the values of Area and Unit in Table 2 produces a value that is unique to only one record in Table 2.
Then you can reference or copy a price from Table 2 in order to calculate a cost in Table 3:
Quantity * Table 2::Price
The relationship controls which record in Table 2 provides the needed price for this calculation.
My table 3 currently has no "Area" field in it, not sure where to put what you sent I have include a screen shot of all 3 tables. Now that I look at, would a "-" in some of the units cause problems
Since it looks like "Unit" is the key field between the files you need to create relationships between all of the files using that field. Once done your calculations should be easy and displaying them in a portal or report also easy.
Some times you have to stop overthinking the problem. The table which had 4 different prices and units repeating was the problem. I changed the table by adding columns for the different prices. In the old table a record had a "Price", "Unit", and an "Area". The new table has "Unit", "Area 1 Price", "Area 2 Price", "Area 3 Price", and "Area 4 Price". Works perfect now.
Thanks for everyone's help.
The new table has "Unit", "Area 1 Price", "Area 2 Price", "Area 3 Price", and "Area 4 Price". Works perfect now.
The draw back to that approach is that adding a 5th Area requires a design change to your database. With other methods, the same change is a data entry task--but it would require you to specify the area or area's for which you want prices.