calculated fields, Iif is iffy, looking for a better way (1 Viewer)

[peterod]

Hi All

I am attempting to make a bill of quantities database for a self-build project. Its so I can sort out the management side of my house build.

I am now on re-design 3 as I learn more about access, i find better ways of doing the job. I now have various tables with various data sets.


The things I would now like help with would be the calculated field i am using to calculate the quantities.

Because each material is calculated using a different calculation i was intending on using a calculated field with an iif statement

i.e iif(id=1,quantity calc for material1,iif(id=2,quantity calc for material2,0)


However, After doing the first 3 materials it was evident that I needed to change this method to something else as the iff statement was getting stupid.

What I would really like to do is have a calculation field for each material that would allow me to input what variables need to be considered to produce the quantity. Any ideas.

That's my first question!

Second question

How do you go about pulling individual values from specific records in different tables to the one that you are doing the calculation?

for example

I have a questions table which has 2 fields

Questions and answers

I have a variables table which has 2 fields

Variabledesc & variable

I want to take the answer to question 1 and multiply it variable 1. Can I do this and how do I go about it?

 

[Galaxiom]

There is the Switch function which allows a set of tests to be determine which of the to use. Alternative you could make a custom function in VBA.

However I wonder if the data could be restructured to make the formula consistent by storing more of the calculation parameters in tables.

Question two is done in a query by joining the tables on their common field.

 

[peterod]

Can i have a query result as a value in a field? can i have different query results in the records in the same field?

I will look at the database design and see if I can find common values for the answers.

this might be the way to go as im either working out volume, areas, or linear meter-age

I will go through all the questions and see if i can group the answers up a bit.

Anyone have any good links to the "switch" and how i would use it in this case?

 

[Galaxiom]

Maybe you could do something like this.

Say you had Length, Width and Height as fields but only have values where relevant leaving Null in the other fields. The Nz function substitutes a value for Nulls.

 Result: [Length] * Nz([Width],1) * Nz([Height],1)

(Ideally choose other names as Width and Height are property names)

Here is some stuff on Switch.
http://www.techonthenet.com/access/functions/advanced/switch.php