Calculating difference from previous record

Question

May I ask for your help with the following please ?

I am trying to calculate a change from one record to the next in my results. It 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 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 row for '17/01/2012', 'TChange' has a value of 5 as the 'TAccounts' has increased from previous 526 to 531. And the 'FChange' would be based on the 'Funded' field. I guess something to be aware of is the fact that the previous row to this example, is dated '13/01/2012'. What I mean is, there are some days where I have no data (for example over weekends).

I think I need to use a SubQuery but I am really struggling to figure out where to start. Could you show me how to get the results I need please ?

I am using MS Access 2010

Many thanks for your time.

Johnny.

Answer 1

Here is one approach 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 a Group By I've used subquerys to get both TAccounts and Funded, as well as the Previous Audit Date, which is then used on the main SELECT statement to get TAccounts and Funded again but this time for the previous date, so that any required calculation can be done against them.

But I would imagine this may be slow to process