How can I do this?

[RET]

Lets see if I can describe exactly what I'm Trying to do. I have number amount entered in a field, this could be different from record to record. Example: John owes $50 in the second month. It would pull the total from field: Amt2 but Sue owes $60 in the 5 month. That would be in field: Amt5

The month number is already created automatically for each record; I have a standard report that will list all records that owe money. Anything over a zero balance. But I need to copy the amount only from the month they owe. Example: both John and sue need top be on the report. I think the best way to do this is to collect the total balance form each record base on what month each record is in. but how would the create this calculation?

Can anyone help?

RET

[Lee Smith]

I'm having trouble understanding your field descriptions and how the inter react together. Maybe a descrition of what the fields are would help.

If you were to put this in a spreadsheet, what would it look like? Something like this, or something entirely different.

................January..,,...February.....,.March.........April..........May........

Or................Amt 1..........Amt 2........Amt 3.........Amt 4........Amt5.......

John ............................. 50.00

Sue......................................................................................60.00

How do you determine the start date of the payment(s)

Are these monthy after the first Amt 2, etc.

Lee

[RET]

I used months as an example. but the numbers are really the quarter of each record.

so one record is in the first quater but the next could be in the third quater and the next could be in the second. ect.

so record one is (feildname) Amt1

next record is (feildname) Amt3

next (feildname) Amt2

I just need to find a way to collect the amount owe from only the current active quarter that each record is using into one filed, for my report.

[Fitch]

If you need to see who has a balance, just create a calc that adds them up:

Amt1 + Amt2 + Amt3 + Amt4 + Amt5

[Lee Smith]

I'm still confused. Your first example used an Amt 5 as part of your explaination, Now you are saying that Amt 1, etc. represent quarters. Since there are only four quarters in a year, how does Amt 5 come into play. Is that the 5 quarter since the transaction started? or was that a typo?

Maybe you could go into detail and describe a transactions from start to finish.

Lee

:concused:

[RET]

I think all I need to do is make a field, example: Total amount due

Then have a calculation that will copy the amount from the appropriate filed based on the number used in each record to the "Total amount due"

1=amt due 1

2=amt due 2

3=amt due 3

4=amt due 4

So if record one contain the number "3" in it will copy the amount from field "amt due 3" to the "total amount due" field. Then use this field in my report.

Just to clarify so more,

R

[Lee Smith]

Hi Tom,

That would be correct if all he wants is a total owed. However, I think he's wanting a report of who owes for a particular period (Amt, or month or quarter), which I don't think is a total of all Amt 1, 2, 3, 4, 5.

Lee

[RET]

Exactly Lee! I need it for that quater only not the one before or after.

[Fitch]

OK, you have a field called Quarter that contains a number. Maybe it's a global, maybe it belongs to each record. Whatever. The calc you want is:

Choose(Quarter, "", Amt1, Amt2, Amt3, Amt4, Amt5)

Reminder: the first pair of empty quotes is there because the Choose function always starts with zero.

[Lee Smith]

Hi RET,

I had to leave for a while, I see Tom has given you a great calculation, do you understand it?

Lee