Query To Calculate 12 month rolling total 1

[tongaman]

As a new Access user, I was hoping if someone could help me write a query to calculate a running 12 month write-off total based on the table details below. If someone could assist me I would be greatly appreciated.

Current Table:
[Date] [Write-off Amount]
1 Jan 01 $10.00
1 Feb 01 $20.00
1 Mar 01 $30.00
1 Jan 02 $40.00
1 Feb 02 $50.00
1 Mar 02 $60.00
1 Jan 02 $70.00
1 Feb 02 $80.00
1 Mar 02 $90.00

Desired Result
12 Month Rolling Write-off example:
$40.00(1 Jan 02) + $10.00(1 Jan 01)=$50.00

[Date] [Write-off Amount] [12 month rolling write-off]

1 Jan 01 $10.00 0
1 Feb 01 $20.00 0
1 Mar 01 $30.00 0
1 Jan 02 $40.00 $50.00(40 + 10)
1 Feb 02 $50.00 $70.00(50 + 20)
1 Mar 02 $60.00 $90.00 (60 + 30)
1 Jan 03 $70.00 $110.00(70 + 40)
1 Feb 03 $80.00 $130.00(80 + 50)
1 Mar 03 $90.00 $150.00 (90 + 60)

 

[TonyJollans]

Hi tongaman,

More than one way to do this; here's one of them:

SELECT Format([

YourTable

].[Date],"d mmm yy"), 
       [

YourTable

].[Write-Off Amount], 
       [

YourTable

].[Write-Off Amount]+[

YourTableCopy

].[Write-Off Amount] 
           AS [12 Month Rolling Write-Off] 
FROM [

YourTable

] 
         LEFT JOIN [

YourTable

] AS [

YourTableCopy

]
             ON DateAdd("yyyy",-1,[

YourTable

].[Date]) = [

YourTableCopy

].[Date];

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Tony,

Thankyou for your help the query worked great. On a similar note, are we able to modify the query to calculate a rolling write-off total based on the the sum of the totals for the 12 months. (refer below)

[Date] [Write-off Amount] [12 month rolling write-off]

1 Jan 01 $10.00 0
1 Feb 01 $20.00 0
1 Mar 01 $30.00 0
1 Jan 02 $40.00 $100.00(40 + 30 + 20 + 10)
1 Feb 02 $50.00 $140.00(50 + 40 + 30 + 20)
1 Mar 02 $60.00 $180.00(60 + 50 + 40 + 30)
1 Jan 03 $70.00 $220.00(70 + 60 + 50 + 40)
1 Feb 03 $80.00 $260.00(80 + 70 + 60 + 50)
1 Mar 03 $90.00 $150.00(90 + 80 + 70 + 60)

 

[TonyJollans]

Hi tongaman,

I'm not quite sure that your example is exactly as your description, but this should do as per your description:

SELECT Format([

YourTable

].[Date],"d mmm yy"), 
       [

YourTable

].[Write-Off Amount], 
       SUM([

YourTableCopy

].[Write-Off Amount]) 
           AS [12 Month Rolling Write-Off] 
FROM [

YourTable

] 
         LEFT JOIN [

YourTable

] AS [

YourTableCopy

]
             ON  DateAdd("yyyy",-1,[

YourTable

].[Date]) <= [

YourTableCopy

].[Date]
             AND [

YourTable

].[Date] >= [

YourTableCopy

].[Date]
GROUP BY [

YourTable

][Date], 
         [

YourTable

][Write-Off Amount];

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Tony,

Apologies for being vague in my examples but your solutions have been great and now I am able to complete my first report on ACCESS.

As a matter of interest, if I was to add a [product] field to the table, how would I modify the sql code above to provide me with a rolling total based on product(refer to table below).

[Date] [Product] [Write-off Amount] [12 mnthr w-off]

1 Jan 01 TRUCK $10.00 0
1 Feb 01 TRUCK $20.00 0
1 Mar 01 TRUCK $30.00 0
1 Jan 02 TRUCK $40.00 $100.00
1 Feb 02 TRUCK $50.00 $140.00
1 Mar 02 TRUCK $60.00 $180.00
1 Jan 03 TRUCK $70.00 $220.00
1 Feb 03 TRUCK $80.00 $260.00
1 Mar 03 TRUCK $90.00 $150.00
1 Jan 01 CAR $10.00 0
1 Feb 01 CAR $20.00 0
1 Mar 01 CAR $30.00 0
1 Jan 02 CAR $40.00 $100.00
1 Feb 02 CAR $50.00 $140.00
1 Mar 02 CAR $60.00 $180.00
1 Jan 03 CAR $70.00 $220.00
1 Feb 03 CAR $80.00 $260.00
1 Mar 03 CAR $90.00 $150.00

I appreciate your help!

 

[TonyJollans]

Hi tongaman,

To limit your output to a single product, just add a WHERE Clause to the SELECT. After the FROM Clause and before the GROUP BY, include, for example, ..

WHERE [

YourTable

].[Product] = "TRUCK"

To include everything but grouped by Product as well, add the Product field to the list of fields in t he GROUP BY Clause.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Tony,

I added a product field to my table and modified my sql code, as per your suggestion above, to give me a 12 month running write-off total by TRUCK. However, I found that the the 12 month running total was also adding the write-off total for the CAR. Any suggestions?


Here is my sql code:


SELECT Format([t1b].[Date],"mmmm yyyy&quot, [t1b].[Amount], SUM([t2b].[Amount]) AS [12 Month Rolling Write-Off], ([t1b].[Product])
FROM t1b LEFT JOIN t1b AS t2b ON (DateAdd(&quot;yyyy&quot;,-1,[t1b].[Date]) <= [t2b].[Date]) AND ([t1b].[Date] >= [t2b].[Date])
WHERE [t1b].[Product] = &quot;Truck&quot;
GROUP BY [t1b].[Date], [t1b].[Amount], [t1b].[Product];

 

[TonyJollans]

Hi tongaman,

Sorry, the JOIN must also be on Product ..

FROM t1b LEFT JOIN t1b AS t2b ON (.. AND .. AND [t1b][Product]=[t2b][Product])

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Tony,

Thankyou very much for your help, my report is looking great. If your ever come down to Melbourne- ustralia, drop me an email and I'll buy you a beer!

Cheers,

tongaman

 

[TonyJollans]

Hi tongaman,

I've never been outside Europe but still hope one day to travel some, so you never know!

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Hi Tony,

I've been asked to modify my report to give a true 12 month rolling total. Do you have any suggestions how I might do this. I have detailed below the current results on my report and a desired report results.

Current Report Results
Date] [Product] [Write-off Amount] [12 mnthr w-off]

1 Jan 01 TRUCK $10.00 10.00***
1 Feb 01 TRUCK $20.00 30.00***
1 Mar 01 TRUCK $30.00 60.00***
1 Jan 02 TRUCK $40.00 $100.00
1 Feb 02 TRUCK $50.00 $140.00
1 Mar 02 TRUCK $60.00 $180.00
1 Jan 03 TRUCK $70.00 $220.00
1 Feb 03 TRUCK $80.00 $260.00
1 Mar 03 TRUCK $90.00 $150.00
1 Jan 01 CAR $10.00 10.00***
1 Feb 01 CAR $20.00 30.00***
1 Mar 01 CAR $30.00 60.00***
1 Jan 02 CAR $40.00 $100.00
1 Feb 02 CAR $50.00 $140.00
1 Mar 02 CAR $60.00 $180.00
1 Jan 03 CAR $70.00 $220.00
1 Feb 03 CAR $80.00 $260.00
1 Mar 03 CAR $90.00 $150.00

Current Report Results i.e, amounts with *** above should be zero.
Date] [Product] [Write-off Amount] [12 mnthr w-off]

1 Jan 01 TRUCK $10.00 0
1 Feb 01 TRUCK $20.00 0
1 Mar 01 TRUCK $30.00 0
1 Jan 02 TRUCK $40.00 $100.00
1 Feb 02 TRUCK $50.00 $140.00
1 Mar 02 TRUCK $60.00 $180.00
1 Jan 03 TRUCK $70.00 $220.00
1 Feb 03 TRUCK $80.00 $260.00
1 Mar 03 TRUCK $90.00 $150.00
1 Jan 01 CAR $10.00 0
1 Feb 01 CAR $20.00 0
1 Mar 01 CAR $30.00 0
1 Jan 02 CAR $40.00 $100.00
1 Feb 02 CAR $50.00 $140.00
1 Mar 02 CAR $60.00 $180.00
1 Jan 03 CAR $70.00 $220.00
1 Feb 03 CAR $80.00 $260.00
1 Mar 03 CAR $90.00 $150.00

 

[TonyJollans]

Hi tongaman,

I don't entirely follow. I appreciate you are probably showing a subset of data, but ..

When date = Feb 03, you want the total of Feb 03, Jan 03, Mar 02, Feb 02
When date = Feb 02, you want the total of Feb 02, Jan 02, Mar 01, Feb 01

and

When date = Feb 01, you want the total of Nothing

What criteria are you supposed to use to determine that? All I can see is that Feb 00 doesn't exist; if that's the case then it looks a bit awkward and I shall ponder on it. Can you let me know?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Tony,

You are correct. As their are no values for Jan, Feb, and Mar 00, the total values for Jan, Feb, and Mar 01 should be zero. Currently my sql code( below) returns a total for Jan, Feb and Mar 01.

SELECT Format([t1b].[Date],&quot;mmmm yyyy&quot, [t1b].[Amount], SUM([t2b].[Amount]) AS [12 Month Rolling Write-Off], ([t1b].[Product])
FROM t1b LEFT JOIN t1b AS t2b ON ([tlb].[Product] = [t2b].[Product]) AND (DateAdd(&quot;yyyy&quot;,-1,[t1b].[Date]) <= [t2b].[Date]) AND ([t1b].[Date] >= [t2b].[Date])
GROUP BY [t1b].[Date], [t1b].[Amount], [t1b].[Product];

 

[TonyJollans]

Hi tongaman,

Well, I'm not sure I like it but this should work ..

SELECT Format([t1b].[Date],"mmmm yyyy"), 
       [t1b].[Amount],

       SUM([t2b].[Amount]) 
           * NOT IsNull(SELECT [Date] 
                        FROM [t1b] AS [t3b] 
                        WHERE [t1b].[Date] = DateAdd("yyyy",1,[t3b].[Date])
                        AND   [t1b].[Product] = [t3b][Product])
           * -1 
         AS [12 Month Rolling Write-Off],

       ([t1b].[Product])

FROM t1b LEFT JOIN t1b AS t2b ON ([tlb].[Product] = [t2b].[Product]) AND (DateAdd(&quot;yyyy&quot;,-1,[t1b].[Date]) <= [t2b].[Date]) AND ([t1b].[Date] >= [t2b].[Date])
GROUP BY [t1b].[Date], [t1b].[Amount], [t1b].[Product];

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Tony,

I added the new section you suggested to my sql code below but Im getting an error stating &quot;argument count mismatch&quot;. I am not sure what this referring to.

SELECT Format([t1b].[Date],&quot;mmmm yyyy&quot, [t1b].[Amount], SUM([t2b].[Amount])
* NOT IsNull(SELECT [Date]
FROM [t1b] AS [t3b]
WHERE [t1b].[Date] = DateAdd(&quot;yyyy&quot;,1,[t3b].[Date])
AND [t1b].[Product] = [t3b].[Product])
* -1 AS [12 Month Rolling Write-Off], ([t1b].[Product])
FROM t1b LEFT JOIN t1b AS t2b ON ([t1b].[Date] >= [t2b].[Date]) AND (DateAdd(&quot;yyyy&quot;,-1,[t1b].[Date]) <= [t2b].[Date]) AND ([t1b].[Product] = [t2b].[Product])
GROUP BY [t1b].[Date], [t1b].[Amount], [t1b].[Product];

 

[TonyJollans]

Hi again,

OK, I posted without trying it fully - sorry

It needs some extra parentheses to make it work ..

SELECT Format([t1b].[Date],&quot;mmmm yyyy&quot, [t1b].[Amount], SUM([t2b].[Amount])
* (NOT IsNull((SELECT [Date]
FROM [t1b] AS [t3b]
WHERE [t1b].[Date] = DateAdd(&quot;yyyy&quot;,1,[t3b].[Date])
AND [t1b].[Product] = [t3b].[Product])))
* -1 AS [12 Month Rolling Write-Off], ([t1b].[Product])
FROM t1b LEFT JOIN t1b AS t2b ON ([t1b].[Date] >= [t2b].[Date]) AND (DateAdd(&quot;yyyy&quot;,-1,[t1b].[Date]) <= [t2b].[Date]) AND ([t1b].[Product] = [t2b].[Product])
GROUP BY [t1b].[Date], [t1b].[Amount], [t1b].[Product];

Hope that's right !!

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tongaman]

Hi Tony,

Thankyou once again, it works perfectly!

Cheers,
Tongaman