Percentage Help

[mcclunyboy]

Hi,

Its been a while since I played with Access and I am helping a colleague out..unfortunately not very successfully at the minute.

We have a query that returns 5 columns from a much larger table. 1 column is titled "phase" and is one of three options. Another column is "appealed" and is either yes / no.

The report shows all the records but we are hoping to list three text boxes (or labels or whatever) that simply show three percentages. 1 percentage of all phase 1 records which have been appealed (yes), and 2 more along the same lines for the other phases....

Does that make sense..???

Hope you can help,
Alex

 

[plog]

Does that make sense..???

A little. What would help is sample data from your table along with what you want the query to produce based on that sample data.

 

[mcclunyboy]

ID                                   Name                Score            Phase        Appealed
1                                      bob                  101                1               No
2                                      bob                 50                   2              Yes                    
3                                      bob                  101                2                  No
4                                      bob                  50                  1                  No
5                                      bob                  101                1                  No
6                                      bob                  50                  1                  Yes
7                                      bob                  101                1                  No
8                                      bob                  50                  Continuation Yes        
9                                      bob                  101                Continuation Yes
10                                     bob                  50                 1                   Yes

Thats a very rough guide...

Sample results:

Phase 1 records appealed = 6 records / 2 appealed = 33%
Phase 2 records appealed = 2 records / 1 appealed = 50%
Continuation records appealed = 2 records / 2 appealed = 100%

EDIT - we want to display this in the header (or footer) of a report.

 

[plog]

The below SQL will provide you with the data you want:

SELECT YourTableNameHere.Phase, Count(YourTableNameHere.Phase) AS TotalRecords, Sum(IIf([Appealed],1,0)) AS RecordsAppealed, Format(IIf(Count([Phase])=0,0,DCount("[Phase]","YourTableNameHere","[Appealed]=True AND [Phase]='" & [Phase] & "'")/Count([Phase])),"Percent") AS PercentAppealed
FROM YourTableNameHere
GROUP BY YourTableNameHere.Phase;

Be sure to replace everyinstance of 'YourTableNameHere' with the name of your actual table.

 

[mcclunyboy]

thanks...

i was hoping I could avoid using more queries...is there anyway to incorporate the percentage using the data in a report!? I know I can use a total record count for one of the values, but can you count another conditionally?

That way I don't need 4 queries for one report...If there is no alternative no problem - I appreciate the help.

 

[plog]

I'm sure in the footer you could add a conditional control to achieve the same thing as this bit of code:

Sum(IIf([Appealed],1,0)) AS RecordsAppealed, Format(IIf(Count([Phase])=0,0,DCount("[Phase]","YourTableNameHere","[Appealed]=True AND [Phase]='" & [Phase] & "'")/Count([Phase])),"Percent") AS PercentAppealed

Sum and Count in the footer area will work on all the data in the previous section.