Best way to set up Query to get a percentage

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];

--
Daryl S


"Marc" wrote:

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?

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.

--
Duane Hookom
Microsoft Access MVP


"Marc" wrote:

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?

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?

"Duane Hookom" wrote:

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.

--
Duane Hookom
Microsoft Access MVP


"Marc" wrote:

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?

What numbers are in different fields? The query I suggested has one numeric
field.

--
Duane Hookom
MS Access MVP


"Marc" wrote in message
...
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?

"Duane Hookom" wrote:

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.

--
Duane Hookom
Microsoft Access MVP


"Marc" wrote:

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?