Calculating Percentages by year for a group and total

[sxschech]

I used a self join query and made it a union query to calculate the percentages by year and gender and then overall by year for various ethnicities. Hope it is usefull...

[tt]
-------------------
Data from source table or query
-------------------

Area EnterGrad Gender Yr NonRes ...
North Enter F 1995 5
North Enter F 1996 8
North Enter ...
North Grad F 1995 2
North Grad F 1996 3
North Grad ...
North Enter M 1995 10
North Enter M 1996 6
North Enter ...
North Grad M 1995 7
North Grad M 1996 1
North Grad ...

------
Query Results
-------
North Detail F 1995 40%
North Detail F 1996 38%
North Detail M 1995 70%
North Detail M 1996 17%
North Total 1995 60%
North Total 1996 29%
[/tt]

SELECT IPGrad.Area, "Detail Percent" AS EnterGrad, IPGrad.Gender, IPGrad.Yr, IIf(Sum([IPGrad].[NonRes])=0,Null,Sum([IPGrad].[NonRes])/Sum([IPEnter].[NonRes])) AS [NonRes], IIf(Sum([IPGrad].[Afam])=0,Null,Sum([IPGrad].[Afam])/Sum([IPEnter].[Afam])) AS [Afam], IIf(Sum([IPGrad].[Native])=0,Null,Sum([IPGrad].[Native])/Sum([IPEnter].[Native])) AS [Native], IIf(Sum([IPGrad].[Asian])=0 Or Sum([IPEnter].[Asian])=0,Null,Sum([IPGrad].[Asian])/Sum([IPEnter].[Asian])) AS Asian, IIf(Sum([IPGrad].[Hispanic])=0,Null,Sum([IPGrad].[Hispanic])/Sum([IPEnter].[Hispanic])) AS Hispanic, IIf(Sum([IPGrad].[White])=0,Null,Sum([IPGrad].[White])/Sum([IPEnter].[White])) AS White, IIf(Sum([IPGrad].[Unknown])=0,Null,Sum([IPGrad].[Unknown])/Sum([IPEnter].[Unknown])) AS [Unknown], IIf(Sum([IPGrad].[Total])=0,Null,Sum([IPGrad].[Total])/Sum([IPEnter].[Total])) AS Total
FROM qryWebGradRate_DetailP1 AS IPGrad INNER JOIN qryWebGradRate_DetailP1 AS IPEnter ON (IPGrad.Area = IPEnter.Area) AND (IPGrad.Gender = IPEnter.Gender) AND (IPGrad.Yr = IPEnter.Yr)
WHERE (((IPGrad.EnterGrad)="Grad") AND ((IPEnter.EnterGrad)="Enter"))
GROUP BY IPGrad.Area, IPGrad.EnterGrad, IPGrad.Gender, IPGrad.Yr
UNION SELECT IPGrad.Area, "Total Percent" AS EnterGrad, Null AS Gender, IPGrad.Yr, IIf(Sum([IPGrad].[NonRes])=0,Null,Sum([IPGrad].[NonRes])/Sum([IPEnter].[NonRes])) AS [NonRes], IIf(Sum([IPGrad].[Afam])=0,Null,Sum([IPGrad].[Afam])/Sum([IPEnter].[Afam])) AS [Afam], IIf(Sum([IPGrad].[Native])=0,Null,Sum([IPGrad].[Native])/Sum([IPEnter].[Native])) AS [Native], IIf(Sum([IPGrad].[Asian])=0 Or Sum([IPEnter].[Asian])=0,Null,Sum([IPGrad].[Asian])/Sum([IPEnter].[Asian])) AS Asian, IIf(Sum([IPGrad].[Hispanic])=0,Null,Sum([IPGrad].[Hispanic])/Sum([IPEnter].[Hispanic])) AS Hispanic, IIf(Sum([IPGrad].[White])=0,Null,Sum([IPGrad].[White])/Sum([IPEnter].[White])) AS White, IIf(Sum([IPGrad].[Unknown])=0,Null,Sum([IPGrad].[Unknown])/Sum([IPEnter].[Unknown])) AS [Unknown], IIf(Sum([IPGrad].[Total])=0,Null,Sum([IPGrad].[Total])/Sum([IPEnter].[Total])) AS Total
FROM qryWebGradRate_DetailP1 AS IPGrad INNER JOIN qryWebGradRate_DetailP1 AS IPEnter ON (IPGrad.Yr = IPEnter.Yr) AND (IPGrad.Gender = IPEnter.Gender) AND (IPGrad.Area = IPEnter.Area)
WHERE (((IPGrad.EnterGrad)="Grad") AND ((IPEnter.EnterGrad)="Enter"))
GROUP BY IPGrad.Area, "Total Percent", IPGrad.Yr;

 

[cranebill]

What were you trying to do, based on your examples, it seems the percentages are off and calculating based on year I do not see any of them getting close to 100%

 

[sxschech]

Sorry if my data examples aren't working. I didn't use the actual numbers and did a quick manual calc, so may not have correct results in demo. The calculations are by year for Male and Female, those starting and those graduating and then the percentage of the combined male female grad divided by the combined male female that started in that year. (The query was duplicating what had been previously done in excel)