How do I calculate % change of invoice amounts from 2011 to 2012?

[ajblue]

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%

[June7]

Try a crosstab query to get the years in columns. Then use crosstab as RecordSource for report and calculate the percent in textbox.

TRANSFORM Sum(Table1.[InvoiceAmount]) AS [Total Of InvoiceAmount]
SELECT Table1.[Supplier], Month([InvoiceDate]) AS MonNum, Format([InvoiceDate],"mmmm") AS MonName
FROM Table1
GROUP BY Table1.[Supplier], Month([InvoiceDate]), Format([InvoiceDate],"mmmm")
PIVOT Year([InvoiceDate]);

Problem with report based on crosstab as that crosstabs are so dynamic, especially when aggregating by date criteria. Building report to be stable from period to period is not easy.

[ajblue]

Thanks June7 for helping on creating the query. But how do I use crosstab as RecordSource for report and calculate the percent in textbox? I'm so sorry, I'm very new on this.

[ajblue]

Owww... I already got the percent change in the report However, how do I set the criteria so that I can only see % change of over 20%?

[June7]

I think the report RecordSource will have to be a SELECT query that uses the crosstab as data source, something like:

SELECT *, ([2012]-[2011])/[2011] * 100 As [PctChange] FROM crosstabquery WHERE ([2012]-[2011])/[2011] * 100 > 20;