Sub query problem

[scubadiver]

Eg data

Live month Current month next month Percentage
Aug 06 5 1 -80%
Sep 06 1

Live month Current month next month Percentage
Aug 06 2
Sep 06 8 2 -75%

But I would prefer the first example to be

Live month Current month next month Percentage
Aug 06 5
Sep 06 1 5 80%


I realise the WHERE statement is wrong but not sure how to modify it.
Subqueries aren't aren't a forte of mine at the moment.

thanks



SELECT E164src.LiveMonth, E164src.Total AS [Current month],

(SELECT
total from E164src AS temp
WHERE
(temp.total<E164src.total)) AS [next month],

(([next month]-[Current month])/[Current month]) AS Percentage

FROM E164src;

 

[scubadiver]

I will add I also have a field for [MonthID] which is a numerical indicator
for each month