Access97 - First £300 per person

[SHardy]

Hi,

I have a table of data relating to payments made on behalf of members. This is a single table, as I have already summarized the data for the required group of people and for specified contract year.

I need to extract data for the first £300 paid against each person. I also need this to be split down by date paid. If a single payment takes the total from under £300 to over £300, then rather than show the full paid amount, I need it to just make it up to £300 exactly.

For simplicity, just assume the following fields:

PersonID
DatePaid
AmountPaid

This could include many records, with vary numbers and amounts of payments, upto any value possible.

I am looking for a result that may look something like this:

PersonID DatePaid AmountPaid ReportAmount
0001 05/01/04 £270 £270
0001 23/02/04 £120 £30
0002 03/03/04 £570 £300
0003 26/02/04 £50 £50
0004...

You get the idea.

Anyway, I have not quite managed to get my head around the best way to do this. Any ideas/ suggestions would be very much appreciated.

Thanks,
Simon

 

[PaulBricker]

Do you have to do this in a query? It would be fairly simple to do in a Report. Let us know and we'll work it out one way or the other.

Paul

 

[SHardy]

Hi, thanks for looking, however I have figured it out now, and I have just finished putting the queries together, and creating the final report.

I knew it would be possible. Just one of those days where I couldn't quite get my head into gear. I think the usual term is "Monday"!

Thanks again & happy xmas

 

[Golom]

Glad you figured it out. Here's a possible query that addresses the problem (even though you don't need it.)

Select B.PersonID, B.DatePaid,  IIF(X.SA <= 300, B.Amount,300- (X.SA-B.Amount)) As [Amount Paid]

From

 (

SELECT P.PersonID, P.DatePaid, P.Amount,
(Select SUM (Amount) As SA From tblPayments As X
Where X.PersonID = P.PersonID AND X.DatePaid <= P.DatePaid)  As SA
FROM tblPayments As P) As X 

INNER JOIN tblPayments As B ON B.PersonID = X.PersonID AND B.DatePaid = X.DatePaid

Where X.SA - B.Amount <= 300