Running Balance

[kshelton]

Is there a simple way to show balances to date when viewing a list of records, i.e. just like a bank statement? The most recent dated record shows current balance but other records show the balance applicable at the record date.

Thanks

Keith

[comment]

How about a running total summary field?

[kshelton]

That works fine but I now have another query on this.

I have a portal linked to a line items file. Portal shows Feb 09 record at the top and let's say Mar 08 at the bottom, simply because I always want most recent month at the top, rather than having to scroll.

Now the Running Total Summary is working from Feb 09 down to Mar 08 and I obviously want it to be reversed so that Feb 09 shows Summary to date and Mar 08 is the first month.

Is it possible to reverse this?

Thanks

Keith

[comment]

I don't think so. You'll need to move to some other method, e.g. lookup the previous balance through a self-join.

[kshelton]

Can you elaborate please?

[comment]

You first.

[kshelton]

Understand look up and self join but unsure exactly what you are suggesting.

Advise if you need more info.

Thanks.

Keith

[comment]

I am suggesting a self-join that makes the previous record the first related record (all in the child table). I don't know what your tables are and what the relationship between them is. In a generic model of Parent -< Child, the self-join would be:

Child::ParentID = Child 2::ParentID

AND

Child::ChildID > Child 2::ChildID

The related records from Child 2 need to be sorted by ChildID, descending (this assumes records are created in the correct chronological order - otherwise you'd need to use a date instead of ChildID).

Each Child record calculates its balance by:

PreviousBalance + Amount

PreviousBalance looks up its value from Child 2::Balance.

[torifile]

I am suggesting a self-join that makes the previous record the first related record (all in the child table). I don't know what your tables are and what the relationship between them is. In a generic model of Parent -< Child, the self-join would be:

Child::ParentID = Child 2::ParentID

AND

Child::ChildID > Child 2::ChildID

The related records from Child 2 need to be sorted by ChildID, descending (this assumes records are created in the correct chronological order - otherwise you'd need to use a date instead of ChildID).

Each Child record calculates its balance by:

PreviousBalance + Amount

PreviousBalance looks up its value from Child 2::Balance.

So if you enter the data our of chronological order, the date will be the deciding factor? Are there any downsides to that approach?

[comment]

If you enter data out of order, you must follow it by a relookup in later-date records.

The downside of this method is storing redundant data, with all its associated problems (incl. the need to relookup). A straightforward report using summary fields is preferable whenever possible.