Renewal rates

[richary]

I wonder if anyone can help me with this

I have a table, let's call it "Payments"which records customer payments in each period. What I'd like to do it is write a query which shows what percentage of customers who make a payment in period N, also make a payment in period N+1. Basically I want to find the "renewal rate"for each period. Customers are related to payments by the field called "CustomerID"

if it's not possible, or not efficient to show percentages, then just raw numbers would be fine.

I hope that the above information is enough, I didn't want to overload anyone here, but if you need more information, please let me know.

Richard

 

[aleb]

First please define the term period : is it a quarter, month, year.
If the data is structured like this:

Name Period Amount
AAA Period1 100
BBB Period2 100
AAA Period2 100

to show the raw numbers you can use a crosstab query, the result query will show you :

Name Period1 Period2
aaa 100 100
bbb 100

In fact the snapshot of the table with data would be enough to understand what needs to be done.