# Thread: How do I calculate % change of invoice amounts year over year?

1. Registered User
Join Date
Sep 2012
Posts
6

## Unanswered: How do I calculate % change of invoice amounts year over year?

I am a newbie in using MS Access and I would like to ask help on how to create a query showing the % change in Invoice Amounts. I want to see all ‘Suppliers’ that had year over year change of 20% or more in invoices (\$) for any given month.
I have a main table that contains the following columns: ID, Supplier, Invoice Date, Invoice Amount.

I want to create a query with this input:
ID Supplier Month 2011(\$) 2012(\$) Change
1 BBB111 January \$10,000 \$20,000 50%
2 AAA222 March \$25,000 \$40,000 37.5%

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
Hi
I think I would base the query on two sub queries something like this

Sub 1
SELECT MainTable.SupplierID, Month([InvoiceDate]) AS CurrentMonth, Year([InvoiceDate]) AS CurrentYear, Sum(MainTable.InvoiceAmount) AS CurrentAmount, Format([InvoiceDate],"mmmm") AS [InvoiceMonth]
FROM MainTable
GROUP BY MainTable.SupplierID, Month([InvoiceDate]), Year([InvoiceDate]), Format([InvoiceDate],"mmmm");

Call this qryCurrentYear

Sub 2
SELECT MainTable.SupplierID, Month([InvoiceDate]) AS PreviousMonth, Year([InvoiceDate])+1 AS PreviousYear, Sum(MainTable.InvoiceAmount) AS PreviousAmount, Format([InvoiceDate],"mmmm") AS [InvoiceMonth]
FROM MainTable
GROUP BY MainTable.SupplierID, Month([InvoiceDate]), Year([InvoiceDate])+1, Format([InvoiceDate],"mmmm");

Call this qryPreviousYear

Sub 3 to calulate Pct Change > 20%
SELECT qryCurrentYear.SupplierID, qryCurrentYear.InvoiceMonth, qryPreviousYear.[PreviousAmount], qryCurrentYear.CurrentAmount, IIf([CurrentAmount]=0,0,([CurrentAmount]-[PreviousAmount])/[CurrentAmount])*100 AS [Pct Change]
FROM qryCurrentYear INNER JOIN qryPreviousYear ON (qryCurrentYear.SupplierID=qryPreviousYear.Supplie rID) AND (qryCurrentYear.CurrentMonth=qryPreviousYear.Previ ousMonth) AND (qryCurrentYear.CurrentYear=qryPreviousYear.[PreviousYear])
WHERE IIf([CurrentAmount]=0,0,([CurrentAmount]-[PreviousAmount])/[CurrentAmount])*100 > 20;

HTH

I have used SuplierID as I assume you have supllier Info in another table??

MTB

3. Registered User
Join Date
Sep 2012
Posts
6
Wow! Thanks MTB. I tried it and it totally worked. However, I made some changes because each table would show all the amounts for 2011 and 2012.

For Sub1 (Current year):

SELECT [Main Table].SupplierID, Month([Invoice Date]) AS CurrentMonth, Year([Invoice Date]) AS CurrentYear, Sum([Main Table].[Invoice Amount]) AS CurrentAmount, Format([Invoice Date],"mmmm") AS InvoiceMonth
FROM [Main Table]
GROUP BY [Main Table].SupplierID, Month([Invoice Date]), Year([Invoice Date]), Format([Invoice Date],"mmmm")
HAVING ((Year([Invoice Date])=Year(Date())));

Sub2 (Previous Year):

SELECT [Main Table].SupplierID, Month([Invoice Date]) AS PreviousMonth, Year([Invoice Date]) AS PreviousYear, Sum([Main Table].[Invoice Amount]) AS PreviousAmount, Format([Invoice Date],"mmmm") AS InvoiceMonth
FROM [Main Table]
GROUP BY [Main Table].SupplierID, Month([Invoice Date]), Year([Invoice Date]), Format([Invoice Date],"mmmm")
HAVING ((Year([Invoice Date])=Year(Date())-1));

Was this right? The problem is, I can't make sub 3 work if I change the two tables. Can you help me again with this?

AJ

4. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
Hi AJ

In Sub2 (PreviousYear) Change

Year([Invoice Date]) AS PreviousYear

back to

Year([Invoice Date])+1 AS PreviousYear

and then change this line

HAVING ((Year([Invoice Date])=Year(Date())-1));

to this

HAVING ((Year([Invoice Date])+1=Year(Date())));

I think that should do it?

MTB

5. Registered User
Join Date
Sep 2012
Posts
6
It still doesn't work. By the way, why does the date for the Previous Year put 2012 when I do the changes you indicated above? It should be 2011 right?

6. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
Not Quite sure why it isn't not working for you, but going back the original quotes I have

qryCurrentYear as

SELECT MainTable.SupplierID, Month([InvoiceDate]) AS CurrentMonth, Year([InvoiceDate]) AS CurrentYear, Sum(MainTable.InvoiceAmount) AS CurrentAmount, Format([InvoiceDate],"mmmm") AS [Month]
FROM MainTable
GROUP BY MainTable.SupplierID, Month([InvoiceDate]), Year([InvoiceDate]), Format([InvoiceDate],"mmmm")
HAVING (((Year([InvoiceDate]))=Year(Date())));

and qryPerviousYear as

SELECT MainTable.SupplierID, Month([InvoiceDate]) AS PreviousMonth, Year([InvoiceDate])+1 AS PreviousYear, Sum(MainTable.InvoiceAmount) AS PreviousAmount, Format([InvoiceDate],"mmmm") AS [Month], Year([InvoiceDate]) AS [Actual Year]
FROM MainTable
GROUP BY MainTable.SupplierID, Month([InvoiceDate]), Year([InvoiceDate])+1, Format([InvoiceDate],"mmmm"), Year([InvoiceDate])
HAVING (((Year([InvoiceDate])+1)=Year(Date())));

I have added an extra field Year([InvoiceDate]) AS [Actual Year]
to illustrate what is the Actual Year of the invoice. This is in fact the year before PreviousYear. This is because the "PrevoiusYear" is used to link to the CurrentYear in Sub3 below. So, no Previous Year in Sub2 needs be one year in the future. Hope that helps.

Therefore Sub3 is inchanged ie.

SELECT qryCurrentYear.SupplierID, qryCurrentYear.Month, qryPreviousYear.PreviousAmount, qryCurrentYear.CurrentAmount, IIf([CurrentAmount]=0,0,([CurrentAmount]-[PreviousAmount])/[CurrentAmount])*100 AS [Pct Change]
FROM qryCurrentYear INNER JOIN qryPreviousYear ON (qryCurrentYear.CurrentYear=qryPreviousYear.Previo usYear) AND (qryCurrentYear.CurrentMonth=qryPreviousYear.Previ ousMonth) AND (qryCurrentYear.SupplierID=qryPreviousYear.Supplie rID)
WHERE IIf([CurrentAmount]=0,0,([CurrentAmount]-[PreviousAmount])/[CurrentAmount])*100>20;

MTB

7. Registered User
Join Date
Sep 2012
Posts
6
Thank you very much! It totally worked. I'd like to ask for help for another query. I've made a query just like the above but this time, instead of computing for the Invoice Amount % change, I computed for the Invoice Count (Current Count-Previous Count). I almost did the same sub queries above and it was successful. Now, I want to combine the 2 Sub3 Queries, with the \$ change of Invoice Amount and the #Change In Invoice Count. Here's what I would like it to look like:

Supplier Month 2011(\$) 2011(invoices) 2012(\$) 2012(invoices) Change (\$) Change (invoices)
BBB111 January \$10,000 5 \$20,000 4 (\$10,000) (1)

AJ

8. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
If you have the count() aggregate function working, then I think you now have all the information you need to combe the two types (count and Sum) in the previous queries. Have a go, if you get stuck let us know.

MTB

9. Registered User
Join Date
Sep 2012
Posts
6
Yes I've tried using the previous queries. The problem is, it shows identical data, like it shows the data for January for Supplier BBB111 on various rows rather than showing it only once. This is what I meant:
Supplier Month 2011(\$) 2011(invoices) 2012(\$) 2012(invoices) Change (\$) Change (invoices)
BBB111 January \$10,000 4 \$20,000 5 (\$10,000) (1)
BBB111 January \$10,000 4 \$20,000 5 (\$10,000) (1)
BBB111 January \$10,000 4 \$20,000 5 (\$10,000) (1)
AAA222 February \$5,000 1 \$6,000 3 (\$1,000) (2)
AAA222 February \$5,000 1 \$6,000 3 (\$1,000) (2)
....

10. Registered User
Join Date
Sep 2012
Posts
6
Already got it! I forgot to create relationships between the queries. Anyway, thank you very much for the help. I've already completed my report and you've played a big part in accomplishing it.