The SQL statement would look like

SELECT User,
Count(User) as Total,
Abs(Sum([Status Field]="Active")) as Active,
Abs(Sum([Status Field]="Closed")) as Closed,
Abs(Sum([Status Field]="Active")) /Count(User) as [PerCent]
FROM tblAppeal1
GROUP BY User

Use your field names as appropriate.

Colin:

One alternative is to use the DCount function.  The third parameter of the
DCount function allows you to specify criteria for the count that is
returned.  For example, to determine the count per user you could set the
Control Source property of the textbox to:

=DCount("[User]", "[tblAppeal1]", "User=[User]")

This assumes that the report Record Source has the a field named "User" and
that the tblAppeal1 also has a field named "User."  Similarly, you can get
the Active or Closed count by adding a criteria for this field (I'll call
this field "Status").  For example,

=DCount("[User]", "[tblAppeal1]", "User=[User] AND Status='Active' ")

For the percentage, one alternative is to have a textbox (hidden or visible)
which totals all the Active counts, and then use this textbox name in the
percentage calculation.  For example,

=(txtActiveUser/txtActiveTotal)*100

A second alternative would be to use the DCount function again to count the
total of all Actives.  For example,

=(txtActiveUser/DCount("[User]", "[tblAppeal1]", "Status='Active' "))*100

You can find more information on the DCount function here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/ac
fctDCount_HV05187157.asp

I have a table called 'tblAppeal1' with various fields in but there are two
fields I wish to total on a report. Firstly, I would like to count how many
records per 'User', Secondly, how many are still 'Active' or 'Closed, and
finally, what percentage of the total (Active) they have. I don't know the
formula in the record to do these functions. Can anybody help?

When the report is run it should show something like this:

User:         Colin
Active:        64
Closed:     26
Total:         90
Percent:    15%

User:         Danny
Active:        82
Closed:     43
Total:         125
Percent:    28%

Cheers in advance

Colin