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

How about a running total summary field?

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

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

Can you elaborate please?

You first.

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

Advise if you need more info.

Thanks.

Keith

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.

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?

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.