Math calculation

[Ac]

Hi,

I have the Excel work sheet to calculate the data (about 25 columns and 300
rows) that are the data for making a chart in the Excel now. Because the
current Excel is not user friendly environment, I try to put every thing in
the Access. I used queries to calculate each cell’s value and some of cells
referring other cell’s value which are not on the same row (before or after
the current row). How can I write the queries to get referring row’s value?

The example like:
1. The equation is on the cell E8: E8 = X7 * C8
2. This is on the cell H8: H8 = H7+D8-Q7
3. And this is on the cell K8=(CalculatedResults!F14-H8)*($K$4/($I$4+$K$4))

Could you advise me how to handle this problem? Is this the right way to do
it? Thanks!

 

[Michel Walsh]

You need to refer to the table with a different 'alias' for each row.

As a very simple example, if the table has a field 'SerialNumber' with
consecutive values without holes in the sequence, then:


SELECT actual.someField,
previous.someField AS someFieldPreviousRow,
nextOne.someField AS someFieldNextRow


FROM (myTable As actual
LEFT JOIN myTable AS previous
ON actual.serialNumber + 1 = previous.serialNumber)
LEFT JOIN myTable AS nextOne
ON actual.serialNumber - 1 = nextOne.serialNumber





can be a solution. Note that if the 'actual' row is the first one, then
previous (our alias for previous row) will only supply NULLs values, for any
of its fields, since such a row does not exist (under that context). That is
made possible by the left outer join.



Vanderghast, Access MVP

 

[Michel Walsh]

.... have to exchange the +1 and -1 :



ON actual.serialNumber -1 = previous.serialNumber)
LEFT JOIN myTable AS nextOne
ON actual.serialNumber +1= nextOne.serialNumber



Vanderghast, Access MVP

 

[Ac]

Thanks! I will try.

... have to exchange the +1 and -1 :



ON actual.serialNumber -1 = previous.serialNumber)
LEFT JOIN myTable AS nextOne
ON actual.serialNumber +1= nextOne.serialNumber



Vanderghast, Access MVP