If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Math calculation
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! |
#2
|
|||
|
|||
Math calculation
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 "Ac" wrote in message ... 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! |
#3
|
|||
|
|||
Math calculation
.... 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 "Michel Walsh" wrote in message ... 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 "Ac" wrote in message ... 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! |
#4
|
|||
|
|||
Math calculation
Thanks! I will try.
"Michel Walsh" wrote: ... 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 "Michel Walsh" wrote in message ... 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 "Ac" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|