Convert Long Excel Formula into Access Expression

[ALewis06]

I have the following formula from Excel 2007 and I need to convert it into Access 2007 (yes I know--it kills me that the company I work for is still on 2007!). Please no VBA script--I'm just not there yet. Thanks



=IF(MONTH(Period0)=1,W2,IF(MONTH(Period0)=2,SUM(W2 :X2),IF(MONTH(Period0)=3,SUM(W2:Y2),IF(MONTH(Perio d0)=4,SUM(W2:Z2),IF(MONTH(Period0)=5,SUM(W2:AA2),I F(MONTH(Period0)=6,SUM(W2:AB2),IF(MONTH(Period0)=7 ,SUM(W2:AC2),IF(MONTH(Period0)=8,SUM(W2:AD2),IF(MO NTH(Period0)=9,SUM(W2:AE2),IF(MONTH(Period0)=10,SU M(W2:AF2),IF(MONTH(Period0)=11,SUM(W2:AG2),SUM(W2: AH2))))))))))))

[pbaldy]

We would need to know your data structure, and if it mimics Excel with monthly values in fields, you've violated normalization rules:

Fundamentals of Relational Database Design -- r937.com

[CJ_London]

if your table is constructed something like

myTable
...
Period integer
Sales currency
...
...

Period...Sales
1..........1000
2..........2500
3..........1500
...
...

then you would use what is called a running sum query which would be something like

SELECT PERIOD, (SELECT SUM(Sales) FROM myTable AS T WHERE Period<=myTable.Period) AS CumulativeSales
FROM myTable
ORDER BY Period

[June7]

Looks like non-normalized to me. This will cause you lots of headaches. The expression will likely be too long and will need custom VBA function.

Either normalize your tables or manipulate them to emulate normalized structure. A UNION query could rearrange the data to a normalized structure that could then be used as the datasource for subsequent queries as suggested by Ajax.