Best way to set up Query to get a percentage
in [Queries]
|
From: Marc on 24 Feb 2010 13:36 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?
From: Daryl S on 24 Feb 2010 14:38 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?
From: Duane Hookom on 24 Feb 2010 14:41 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?
From: Marc on 24 Feb 2010 16:20 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?
From: Duane Hookom on 25 Feb 2010 00:13 What numbers are in different fields? The query I suggested has one numeric field. -- Duane Hookom MS Access MVP "Marc" <Marc(a)discussions.microsoft.com> wrote in message news:AEA8E2B8-4F6F-4186-9293-CB39BA2D278C(a)microsoft.com... > 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?
|
Pages: 1 Prev: Query to get information that doesn't match by date Next: List Macro Actions |