Query sum 2

[nkll]

Probably a simple task for most, but not for a rookie.

I have a table with a division field in it. The division field has entries such as (minor, major, pro). I would like to create a summary query/report that would look something like this:

Division Total Percent of Total
Minor xx xx%
Major xx xx%
Pro xx xx%

Any help would be great.
Thanks

 

[Golom]

Try
[tt]
Select Division, Sum(FldValue} As [Total],

(Sum(FldValue) /
(Select Sum(FldValue) From tbl)) As [PercentOfTotal]

From tbl

Group By Division
[/tt]

 

[nkll]

Golom,
sorry... i guess i need more clarification. where is the code placed into a vb built expression or in a query that has a calculated field??

thanks

 

[lespaul]

You could paste Golom's SQL directly into a query (Query -> New -> SQL View) and it would work after you change the table name and field names.

 

[nkll]

Hello, looks like am almost there.
This code provides the following results:
======================================================
Select Division, Sum(FldValue) As [Total],

(Sum(FldValue) /
(Select Sum(FldValue) From QryRegistered2004)) As [PercentOfTotal]

From QryRegistered2004

Group By Division;
========================================================
No totals are included... any help ????

Thanks....

======================================================
Division Total PercentOfTotal

CoachPitch
Jr
MachinePitch
Majors
Minors
TBall
=======================================================

 

[Golom]

Do you mean that the "Total" and "PercentOfTotal" fields are empty or do you mean that only one field is generated. If its the former then you probably have nulls in your database. Try upgrading to
[tt]
Select Division, Sum(NZ(FldValue,0)) As [Total],

(Sum(NZ(FldValue,0)) /
(Select Sum(NZ(FldValue,0)) From QryRegistered2004)) As [PercentOfTotal]

From QryRegistered2004

Group By Division
[/tt]

 

[nkll]

now this returns:
also it asks for parameter "Fldvalue"

Division Total PercentOfTotal

CoachPitch 0 #ERROR
Jr 0 #ERROR
MachinePitch 0 #ERROR
Majors 0 #ERROR
Minors 0 #ERROR
TBall 0 #ERROR

...thanks

 

[lespaul]

did you change the statement so that YOUR fieldname is in the query instead of "FldValue"?

leslie

 

[nkll]

FldValue being either "coachPitch, Jr, MachinePitch ...?

I was thinking FldValue was a keyword... so no I was not replacing it but when I did I got the same results.

SELECT QryRegistered2004.Division, Sum(NZ(Minors,0)) AS Total, (Sum(NZ(Minors,0))/(Select Sum(NZ(Minors,0)) From QryRegistered2004)) AS PercentOfTotal
FROM QryRegistered2004
GROUP BY QryRegistered2004.Division;

thanks...

 

[Golom]

The other possibility is that ALL the values are NULL so the divisor
[tt]
(Select Sum(NZ(FldValue,0)) From QryRegistered2004)
[/tt]
is returning zero (and, since TOTAL is always zero, that's what is happening). The #ERROR flag is therefore telling you that you are dividing by zero. When you actually get some data loaded into the table, this should disappear.

Guess you could do something like
[tt]
Select Division, Sum(NZ(FldValue,0)) As [Total],

(Sum(NZ(FldValue,0)) /

IIF ( (Select Sum(NZ(FldValue,0)) From QryRegistered2004) = 0,
1,
(Select Sum(NZ(FldValue,0)) From QryRegistered2004))
) As [PercentOfTotal]

From QryRegistered2004

Group By Division
[/tt]