Percent Increase Field

[Bonediggler1]

Hi-

I am trying to build a query that has a date field, an amount field, and a field showing the percent change from one month to the next. It would look something like this

06/2009 1
07/2009 2 100%
08/2009 3 50%

I cannot figure out how to calculate the percent change field.

Thanks for your help!

 

[MajP]

dtmDate	intAmount
9/8/2009	1
9/9/2009	2
9/10/2009	3
9/11/2009	8
9/12/2009	10
9/13/2009	20
9/14/2009	10
9/15/2009	8
9/16/2009	7
9/17/2009	10
9/18/2009	100

qryPreviousAmt

SELECT A.dtmDate, A.intAmount, (select top 1 tblDates.intAmount from tblDates where A.dtmDate > tblDates.dtmdate order by tblDates.dtmDate DESC ) AS amtPrevious
FROM tblDates AS A;

dtmDate	intAmount	amtPrevious
9/8/2009	1	
9/9/2009	2	1
9/10/2009	3	2
9/11/2009	8	3
9/12/2009	10	8
9/13/2009	20	10
9/14/2009	10	20
9/15/2009	8	10
9/16/2009	7	8
9/17/2009	10	7
9/18/2009	100	10

SELECT qryPreviousAmt.dtmDate, qryPreviousAmt.intAmount, qryPreviousAmt.amtPrevious, ([intAmount]-[amtPrevious])/[amtPrevious] AS Change
FROM qryPreviousAmt;

dtmDate	intAmount	amtPrevious	Change
9/8/2009	1		
9/9/2009	2	1	100.00%
9/10/2009	3	2	50.00%
9/11/2009	8	3	166.67%
9/12/2009	10	8	25.00%
9/13/2009	20	10	100.00%
9/14/2009	10	20	-50.00%
9/15/2009	8	10	-20.00%
9/16/2009	7	8	-12.50%
9/17/2009	10	7	42.86%
9/18/2009	100	10	900.00%

 

[Bonediggler1]

Thanks MajP.

I see where you are going with this, but I am getting the error "At most one record can be returned by this subquery", which occurs only after throwing the alias into the WHERE clause (i.e. where A.dtmDate > tblDates.dtmdate)-- otherwise no records are returned in the amtPrevious field (of course because dtmDate will never be greater than itself).

I am wondering how you can refer in a subquery to a table that is outside of the suquery?

Am i missing something?

My SQL is:

SELECT A.[invoice date], A.[invoice amount], (select top 1 [invoice amount] from tblMaster_Data where A.[invoice date] > [invoice date] order by [invoice date] desc) as Prev_Amt

from tblMaster_Data A

 

[MajP]

I think you may need to positively id these
[invoice date] order by [invoice date]
to
tblMaster_Data.[invoice date] order by tblMaster_Data.[invoice date]