Looks like you are building a new table, table1_1 from table1
SELECT Table1.A, Table1.B, Table1.F, Table1_1.A, Table1_1.B, Table1_1.E
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.B = Table1_1.B AND Table1.A = (Table1_1.A-1) AND …!!;
Will the JOIN query handle this problem..(see example statement below) I have rewritten the problem below…
A…… B…. … C……D…….E…… F…………...........G
2001…1…….2……40……80…………………………
2002…1…….3……30……90……110……(110-8)=102
2003…1…….2……60……120…..115……(115-9)=106
2001…2…….5……40……200…………………………
2002…2…….3……50……150…..110……(110-20)=90
2003…2…….4……60……240…..115……(115-15)=100
‘A’ along with ‘B’ are unique identifiers for a particular record and this is what helps identify a current and previous record (‘A’ being the year and ‘B’ a field which we can follow through from year to year).
‘E’ is a calculated field that multiplies ‘C’ and ‘D’
‘F’ is determined from autolookup tables
‘G’ is a calculated field that takes 10% of ‘E’ from previous record and subtracts from current record in ‘F’
‘G’ is the value I wish to calculate using a query/expression
Example written statement is:
Want field ‘B’=2 and Current year ‘A’ = 2003, where calculated ‘F’ = 115.
Previous year ‘A’= ‘A’-1 = 2002, want same field ‘B’ = ‘B-1’ = 2 and calculated ‘E’ = ‘E’-1 = 150
The expression/query wants to put result in field ‘G’ = ‘F’ – ‘E-1’(10%) with respect to ‘A’&’B’
....A......B.....F......A-1....B-1....E-1
G = (2003 2 115) - (2002 2 15)
This problem just does not seem to want to go away
Cheers
