Best way to set up Query to get a percentage

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

I have a "classified" field that is a yes/no check box to determine if a
supplier has been classified or not. I need to get the sum of suppliers that
are and are not classified and then I need to get a percentage of suppliers
who is and who isn't classified from the total suppliers. I also want to be
able to chart this without exporting to Excel.

Any ideas on the best way to set this up?
 
Marc -

You can do that in a query. Change the fields and table names to be yours:

SELECT Sum(IIf([Classifed],1,0)) AS Yes_Count, Count(SupplierID) AS
Total_Count, Sum(IIf([Classifed],0,1)) AS No_Count,
Sum(IIf([Classifed],1,0)/DCount("[Classifed]","[Suppliers]")) AS Pct_Yes,
Sum(IIf([Classifed],0,1)/DCount("[Classifed]","[Suppliers]")) AS Pct_No
FROM [Suppliers];
 
Create a query like:
Select IIf(Classified,"Classified","Not Classified") As IsClass, Count(*) As
NumOf
FROM tblSuppliers
GROUP BY IIf(Classified,"Classified","Not Classified");

You can use this query as the Row Source of a chart.
 
Thanks the query worked great!

The only problem I'm having is with charting now. I would like the chart to
be stacked. I'm having dificulties there because the numbers are in different
fields. Any ideas?
 
Back
Top