Quote Originally Posted by ben.oates
Hi icthus,

It would be much easier to list them seperately. To do this, you need to use "Group By" and "Sum" SQL functions.

I created an example table for this so you can check that it works before fiddling with your own:

SchoolNum Cost Status
1 ?1,500.00 Pending
1 ?200.00 Pending
1 ?4,800.00 Final
1 ?800.00 Final
2 ?15,000.00 Final
3 ?52,800.00 Final
3 ?2,600.00 Pending


And the SQL I used is:

SELECT SchoolTest.SchoolNum, Sum(SchoolTest.Cost) As [Total Cost], SchoolTest.Status FROM SchoolTest GROUP BY SchoolNum, Status;

Which gave me:

SchoolNum Total Cost Status
1 ?5,600.00 Final
1 ?1,700.00 Pending
2 ?15,000.00 Final
3 ?52,800.00 Final
3 ?2,600.00 Pending

As you can see, it has added the values for school 1 together but kept results for Final and Pending seperate. By the way, I originally entered the schools in a random order in the table but it returned them in School order. Don't worry about the order of your table. It makes no difference.

Hope this helps.

[Edit because tables don't display properly on here]
Thanks, but that doesn't really work for what I want.

I've got to produce a report you see, and on the report I have one column which is called "Amount Committed" this is the sum that is not pending and I have another column which is "Amount in Progress" which is the amount pending.