Creating Compound summations etc.

[ohthesilhouettes]

Hi again!



I have a series of records each with a count of treaties. I want to either add or subtract the number of counts to the previous record grouped by the count of treaties, the country that is involved, and whether they joined or left the treaty.

TreatyCount; Country; YearDate; Action
10; USA; 1975; joined
1; USA; 1980; left
3; USA; 1980; joined
1; USA; 1990; joined
5; France; 1980; joined
2; France; 1981; joined
3;France; 1982; joined
1;France 1985; left

I need to make a table that shows at any given year, adds the number of treaties a country joins from the previous year and subtracts the number of treaties if a country leaves.

-Note the countries begin at different time points (USA started joining treaties in 1975 while France started joining treaties in 1980)
-Also note that in one year a country can join treaties and leave treaties all in one year (in 1980 USA left 1treaty and joined 3 others)
-Lastly, there are years (not listed) in which the country neither joined nor left a treaty. I will assume that during these in-between years, their treaty numbers haven't changed(ie USA did not join or leave any treaties between 1976-1979 and therefore the treaty count is the same as it was in 1975)

The resulting table should look like this

TreatyTotals; Country; YearDate; Action
10; USA; 1975; joined
9; USA; 1980; left
11; USA; 1980; joined
12; USA; 1990; joined
5; France; 1980; joined
7; France; 1981; joined
10;France; 1982; joined
9;France 1985; left

I know this is probably a complicated query and may need a lot of intervening steps. Does the layout of my table even allow these calculations to work?

I have been looking into IFF functions but have not been able to sum within the group of records I am interested in.

My guess is that the iff function would say, "If Action = "joined", Add TreatyCount from the previous record Grouped by same Country, If not then subtract TreatyCount from the previous record Grouped by same Country"

I got this far: TreatyTotals: Iff(Action = "joined", Sum(TreatyCount)........)

I also tried using the domain aggregate functions (Dsum) but ran into the same problem.

If anyone can give me some pointers on how to do this I would very much appreciate it. Thanks a bunch!

[Bob Fitz]

Hi

Is this a table or the results of a query.

TreatyCount; Country; YearDate; Action
10; USA; 1975; joined
1; USA; 1980; left
3; USA; 1980; joined
1; USA; 1990; joined
5; France; 1980; joined
2; France; 1981; joined
3;France; 1982; joined
1;France 1985; left

The reason I ask is because the numbers in the first column look as though they should be coming from a query not a table. Calculations should NOT normally be stored in table fields.

[ohthesilhouettes]

The first table did come from a query previously but I made the query output a table for me using SELECT INTO. Now it is a table and I want to make a query FROM this table to create the second table.
Hope this makes sense. Thanks

-Michelle