Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #3
    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. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #5
    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. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #7
    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. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #9
    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. #10
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •