formula refer to header field

[poire]

Hi,

I'm starting to built a planning sheet from Excel in Access. Most of the problems I can solve, but one thing doesn't work (yet). The example below illustrates the problem in Excel. For example the second row in Excel I have the year of construction and every 2 years the costs are 1000 euro's. In the red cells only a value is given when there are two years expired, so 2012, 2014,2016 etc will have 1000 euro maintenance costs, the other years not. To calculate if a year has the costs or not, I use the following formula in the red cells:
=IF(MOD(D$1-$A2;$B2)=0;$C2;"")

The formula checks if the difference between the year of construction and the year of the collom (header) devided by the cycli is a whole number. If this is true, than the price will be taken. For example (2013-2012)/2 is not a whole numbre, so this cell will not get any costs.

The problem is that the function refers to the header of the cell (e.g. 2013). How can I use the reference to the header in Access?

Thanks in advance!

year of construction	cycli	Price/year	2012	2013	2014	2015	2016	2017	2018	2019	2020
2012	2	€ 1.000,00	€ 1.000,00		€ 1.000,00		€ 1.000,00		€ 1.000,00		€ 1.000,00
2012	3	€ 500,00	€ 500,00			€ 500,00			€ 500,00
2012	1	€ 100,00	€ 100,00	€ 100,00	€ 100,00	€ 100,00	€ 100,00	€ 100,00	€ 100,00	€ 100,00	€ 100,00

[June7]

Access 2010 introduced a new field type - Calculated. It does allow some calculations to take place within a table. My limited experimentation indicates cannot use functions in expression. Calculations can perform simple mathematic operations with fields, like: Cost * Quantity

What you show is not a normalized data structure for a relational database. It is a spreadsheet. If you try to use Access table as a spreadsheet, you will encounter much frustration.

[poire]

thanks. I experience that already Do you have a solution to reach the same result. Finally I want to make sums. For example the total maintenance costs for the year 2013. Another option I tried was to filll the coloms with the next year maintenance have to take place. But by this way I could not calculate the sum over e.g. the year 2013, because 2013 for one row stands in the third column and for another row in the fourth column.

[June7]

First, answer the question - Is Access the correct tool for my need?

To help you answer, review the 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/.