an iif statement that would change the value to multiple fields

[Hallel86]

Okay, so basically, I have a problem that I can't seem to solve in SQL for my access 2010 query.

Let's say I have a the price of and for every year.
In the tables I have A building number, a building type, and electricity and water predictions for this year and many upcoming years (up to 40 years)

I need to apply a change to any building with the building type BRT to show only 10 percent of the electricity and water for ALL years (up to year 2052). So building 5 would show 8.5, 5.4, 9.5, 7.4, and so on.

If this is possible, is it possible without using VBA? I am not against VBA, but I am not YET familiar with it at this moment. If it is only possible with VBA, how would this work? Thank you.

 

[Galaxiom]

Your data structure is incorrect. WIth the right structure your update query would be simple.

Remove the fields that form repeating groups. Add a field to indicate Electricity or Water and another field to record the year. Have only one field to hold the amount. Each value will be a different record.

B_TYPE should not be in this table but rather in a table that records information specific to the building.

I strongly recommend you make this change or you will continue be faced with problems.

 

[Hallel86]

Thank you for the advice. I have come to terms that my source data is going to have to be restructured. I have restructured it and have found the calculations I need. Thanks!