Code Help

[dhaveedh]

I would appreciate any help with code to carry out this task;

I have a table containing information about payments made by different customers thus

TransactionsTable
AccNo PmtDate Amt
101 10/03/2004 10
101 17/03/2004 10
101 24/03/2004 15
103 10/03/2004 10
103 12/03/2004 15
103 17/03/2004 10
104 20/03/2004 10

PayPatternTable
AccNo PmtPattern
101 Wkly
103 Wkly
104 Mthly

Each customer has a payment pattern.I am supposed to look at the TransactionsTable and check for customers who are paying by their pay pattern. e.g for March 2004 which customers on a weekly payment pattern have made the right number of purchases.

Thanks for any help given.



KISS - Keep It Simple Sugar!

 

[JeffTullin]

Heres a start.
Get yourself a view on the transactions table that filters on dates in the range you want.
(eg March 2004)
group by account number.
Take count(Amt)

That would get you
101,3
103,3
104,1

Now you need to compare that with the pay pattern table.
I'd be doing a lookup on Weekly = 4
and Monthly =1
You might do that by having another table holding
Weekly,4
Monthly,1
and joining them together.

As long as the Count(Amt) is equal or greater than the decoded periodicity value, you've got the right amount of transactions.

 

[dhaveedh]

Thanx for ur help. Thats the easy part. The other part is that I want to check that the time between the payments is 7days for weekly payments and 30 or 31 for monthly payments.

That's where i got stuck.

Ta

KISS - Keep It Simple Sugar!

 

[JeffTullin]

For that, I reckon you'll have to open a sorted recordset in code, and iterate through it.
Keep track of which customer you looked at last, know what period they use, and compare THIS date with LAST date looked at.

 

[dhaveedh]

How do i do this?

KISS - Keep It Simple Sugar!