If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Best way to set up Query to get a percentage
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? |
#3
|
|||
|
|||
Best way to set up Query to get a percentage
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? |
#4
|
|||
|
|||
Best way to set up Query to get a percentage
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? |
#5
|
|||
|
|||
Best way to set up Query to get a percentage
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? |
Thread Tools | |
Display Modes | |
|
|