I'm having problem constructing below two variables in Access Query or Access SQL which are dependent on each other, please kindly help!

a(n)=b(n)+c(n); b(n)=if(a(n-1)<>0,a(n-1),d(n)).

n refers to the current record while n-1 refers to the previous record.

Here's the sample data: enter image description here

  • Do you have the case for a(0)? – Anthony Genovese Jul 9 at 22:15
  • What does n refer to? Row Number? Can you please provide a data example? – Anthony Genovese Jul 9 at 22:16
  • 1
    n refers to the current record while n-1 refers to the previous record Does your records are enumerated in separate field? If true, use 2 copies joined by t1.n = t2.n+1, in another case enumerate records in the order you need in separate view (statically saved query) and use 2 copies of it. – Akina Jul 10 at 4:25
  • a(0) is not available but b(1) is a given number. See above revised post for data example. – ScottieY Jul 10 at 13:18
  • 1
    You have labeled c(n) as "Given", so there is no way to calculate that? Is it available in another table? The question show sample data, but what do you start with? If you start with the data given, it's not clear what to do next because one needs the value of c(7), c(8), etc. to continue the pattern. (BTW, is this for homework? If it is, you should be clear about that. Users here are ready to help, but you should show what work you have done and not expect a complete free answer.) – C Perkins Jul 11 at 5:55

I've got an answer from my friend below but it works very slow for my actual database.

Anyone has more efficient way doing it?


| ID n  c  d |
| 1  1  1  2 |
| 2  2  0  2 |
| 3  3  0  2 |
| 4  4  1  2 |
| 5  5  2  2 |
| 6  6  3  2 |
| 7  7 -9  2 |

n=ID but n is number variable sql:

SELECT Table1.n
    ,[b] + [c] AS a
    ,IIf([n] < 2, 2, IIf(DLookUp("[a]", "Query2", "[table1].[n]=" & [table1].[n] - 1) <> 0
         , DLookUp("[a]", "Query2", "[table1].[n]=" & [table1].[n] - 1), [d])) AS b
FROM Table1;
  • DLookUpis slow, designed for easy fetching one value; not to be used in a loop, like you did (every row ofTable1creates at least 1 lookup (2 if result is not stored, but I am not sure about that)). In SQL you should use joins for better performance (of course there is an index onnin Table1) like Akina suggested (comment in question) – ComputerVersteher Jul 12 at 21:10
  • Hi, it seems I can't comment in above section, can you elaborate on yours or Akina's answer? – Scottie Jul 15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.