Summing Fields

[NoahR]

In a database I have a bunch of records about people, and each person is designated by a unique ID code. There are multiple records for each person, though (each record represents one person for a particular year).

What I want to do is sum the data over all the years for each particular person in a new table (or a new database). Anybody have any ideas? Maybe a script?

[DanBrill]

So each record for a person has the same person-ID number attached to it, regardless of the year? I'm assuming yes.

If so, you want to create a self-join relationship. If your table is called people, create another table occurence (TO) called People2. Relate them by the ID number. THen create a calc field for your sum:

sum(People2::YourDataField)

This will give you the sum for each person across all years for that data.

HTH,

Dan

[NoahR]

Yep, that works beautifully, thanks.

[Tanner]

I have a similar setup on one of my databases so I tried this suggestion. It worked great, except when the total is a negative number. For example if I have two records that should add up to -150 it says -250. Any ideas what might cause this?

-Tanner

[Tanner]

Just a quick update. The negative numbers actually are double what they should be. So if the number is supposed to be -25 it reads as -50. Any ideas would be greatly appreciated.