Calculating the difference with the previous record

Question

May I ask you for help with the following request?

I am trying to calculate the change from one record to the next in my results. This will probably help if I show you my current query and results ...

SELECT A.AuditDate, COUNT(A.NickName) as [TAccounts], SUM(IIF((A.CurrGBP > 100 OR A.CurrUSD > 100), 1, 0)) as [Funded] FROM Audits A GROUP BY A.AuditDate; 

The query gives me these results ...

 AuditDate D/M/Y TAccounts Funded -------------------------------------------- 30/12/2011 506 285 04/01/2012 514 287 05/01/2012 514 288 06/01/2012 516 288 09/01/2012 520 289 10/01/2012 522 289 11/01/2012 523 290 12/01/2012 524 290 13/01/2012 526 291 17/01/2012 531 292 18/01/2012 532 292 19/01/2012 533 293 20/01/2012 537 295 

Ideally, the results that I would like to get would be similar to the following ...

 AuditDate D/M/Y TAccounts TChange Funded FChange ------------------------------------------------------------------------ 30/12/2011 506 0 285 0 04/01/2012 514 8 287 2 05/01/2012 514 0 288 1 06/01/2012 516 2 288 0 09/01/2012 520 4 289 1 10/01/2012 522 2 289 0 11/01/2012 523 1 290 1 12/01/2012 524 1 290 0 13/01/2012 526 2 291 1 17/01/2012 531 5 292 1 18/01/2012 532 1 292 0 19/01/2012 533 1 293 1 20/01/2012 537 4 295 2 

Looking at the line for '17 / 01/2012 ', “TChange” has a value of 5 because “TAccounts” has increased from the previous 526 to 531. And “FChange” will be based on the “Funded” field. I assume that you need to know something about the fact that the previous line for this example is dated "01/13/2012". I mean, there are a few days when I do not have data (for example, on weekends).

I think I need to use SubQuery, but I'm really trying to figure out where to start. Could you show me how to get the results that I need?

I am using MS Access 2010

Thanks so much for your time.

Johnny.

Answer 1

Here is one way you could try ...

 SELECT B.AuditDate,B.TAccounts, B.TAccount - (SELECT Count(NickName) FROM Audits WHERE AuditDate=B.PrevAuditDate) as TChange, B.Funded - (SELECT Count(*) FROM Audits WHERE AuditDate=B.PrevAuditDate AND (CurrGBP > 100 OR CurrUSD > 100)) as FChange FROM ( SELECT A.AuditDate, (SELECT Count(NickName) FROM Audits WHERE AuditDate=A.AuditDate) as TAccounts, (SELECT Count(*) FROM Audits WHERE (CurrGBP > 100 OR CurrUSD > 100)) as Funded, (SELECT Max(AuditDate) FROM Audits WHERE AuditDate<A.AuditDate) as PrevAuditDate FROM (SELECT DISTINCT AuditDate FROM Audits) AS A) AS B 

Instead of using Group By I used subqueries to get both TAccounts and Funded, as well as the previous audit date, which is then used in the main SELECT to get TAccounts and Funded again, but this time for the previous date, so any required settlement can be made against them.

But I would suggest that it might be slow to process

Answer 2

It's a shame that MS never made this type of thing easy in Access, how many lines do you work in your report?

If it is under 65K, I would suggest sending the data to an Excel spreadsheet and use a simple formula to calculate different rows.

Answer 3

You can try something like the following (sql is not verified and will require some changes)

SELECT

  A.AuditDate, A.TAccounts, A.TAccounts - B.TAccounts AS TChange, A.Funded, A.Funded - B.Funded AS FChange 

FROM

 ( SELECT ROW_NUMBER() OVER (ORDER BY AuditDate DESC) AS ROW, AuditDate, COUNT(NickName) as [TAccounts], SUM(IIF((CurrGBP > 100 OR CurrUSD > 100), 1, 0)) as [Funded] FROM Audits GROUP BY AuditDate ) A 

INNER JOIN

 ( SELECT ROW_NUMBER() OVER (ORDER BY AuditDate DESC) AS ROW, AuditDate, COUNT(NickName) as [TAccounts], SUM(IIF((CurrGBP > 100 OR CurrUSD > 100), 1, 0)) as [Funded] FROM Audits GROUP BY AuditDate ) B ON B.ROW = A.ROW + 1