Ms Access Tables : Autocalculate selected records in a column

[jonathanchye]

I am porting an Excel spreadsheet table into a database I am building in Access and have come across a rather tricky situation. My client has a table in the following format :

Type---- Weight ----Set Price
FixedVal1 userInput1 userInput1
FixedVal2 userInput2 userInput2
FixedVal3 userInput3 autoCal from userInput1*1.23
FixedVal4 userInput4 autoCal from userInput2+userInput1
FixedVal5 userInput5 userInput3

As you can see, Type column are all fixed values. Weight column consists of normal user input values but Set Price has a mixture.

How would I set to build such a table in Access? In Excel its very easy by selecting cells etc but I can't really see a way of doing that here....

Any advice?

 

[Galaxiom]

It is difficult without knowing the context. Can you explain more about why the records are handled differently.

Are those derived records an exception or are there many like that?

 

[jonathanchye]

It is difficult without knowing the context. Can you explain more about why the records are handled differently.

Well the client is manufacturing boxes that uses different Types of papers. These types are normally fixed values (there's around 15 types now). For each type would be Grammage costs(weight). Client wants to adjust each grammage cost manually. The next column then would be the £/tonne price(Set Cost). Client is told 5 prices from suppliers that matches up with 5 types and the rest of the £/tonne price are autocalculated based on these 5 prices.

It's a bit of a complex situation but I need to build this table in Access as the values would be used as a lookup matrix to determine other pricing components. It's easily done in Excel as I can see here but I wonder how to store/update these values in Access...

Maybe I can create a form that restricts data entry into certain record for £/tonne and make them autocalculate based on other values instead? This would mean I would have to create a lot of textboxes though instead of using the spreadsheet input...