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
|
|||
|
|||
Yes/No Field
can anyone help me how to count the number of YES from my Yes/No Field in my
parameter query, is this possible? I have my SQL here where can I put my criteria so that it displays the number of YES from my YES/No Field.. SELECT [Data Entry].Location AS [Site Location], [Data Entry].[Drwg Typ] AS [Drawing Type], [Data Entry].[Drawing Title], Count([Data Entry].[Drwg Typ]) AS [Total Records], Sum([Data Entry].[Qty of Sheets]) AS [Total Documents], Sum([Data Entry].Eqv_A_Size) AS [Eqvlnt A Size], Sum([Data Entry].[Earned ASize]) AS [EEqvlnt A Size], Avg([Data Entry].[Physical Prog]) AS [Physical Progress], [Data Entry].[Rd Line] AS [Red Line], [Data Entry].AsBuilt AS [As Built] FROM [Data Entry] GROUP BY [Data Entry].Location, [Data Entry].[Drwg Typ], [Data Entry].[Drawing Title], [Data Entry].[Rd Line], [Data Entry].AsBuilt HAVING ((([Data Entry].[Drwg Typ])=[Enter Drawing Type1] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type2] Or (([Data Entry].[Drwg Typ])=[Enter Drawing Type3] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type4]) Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type5] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type6] Or (([Data Entry].[Drwg Typ])=[Enter Drawing Type7] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type8] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type9] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type10] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type11]) Or (([Data Entry].[Drwg Typ])=[Enter DrawingType12] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type13] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type14] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type15] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type16]))); any help will be much appreciated thanks denver |
#2
|
|||
|
|||
Yes/No Field
Use a formula
Abs(SUM([YesNoField])) as CountOfYes OR Count(IIF([YesNoField]=True,1,Null)) The difficulty is that Count returns a count of the non-null values. A Yes/no field always has a value of True (-1 in Access) or False (0). That being the case you end up with a count of rows. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Denver wrote: |
#3
|
|||
|
|||
Yes/No Field
thanks it really works, i just make some modify but it really works..
thanks again "John Spencer" wrote: Use a formula Abs(SUM([YesNoField])) as CountOfYes OR Count(IIF([YesNoField]=True,1,Null)) The difficulty is that Count returns a count of the non-null values. A Yes/no field always has a value of True (-1 in Access) or False (0). That being the case you end up with a count of rows. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Denver wrote: |
Thread Tools | |
Display Modes | |
|
|