Count Null Values in Totals Query

[erind]

All of my data have a "Number of Positions" filled in on the table, but when I run this query only the items that have Elected=y and Inc=I show a Count of Number of Positions. Can I get a Count to equal 0 if it doesn't meet the criteria? Even with this IIf statement (in red), there are no null values showing in my query results.

SQL reads:
SELECT TblTrustees.[Authority Code], TblTrustees.[Authority Name], Count(TblTrusteesPerAuthority.[Number of Positions]) AS [CountOfNumber of Positions], TblTrustees.[Incumbent (I) New (N) Vacant (V)], TblTrustees.Elected, IIf([CountofNumber of Positions] Is Null,0,[CountofNumber of Positions]) AS ElecInc
FROM TblTrustees INNER JOIN TblTrusteesPerAuthority ON TblTrustees.[Authority Code] = TblTrusteesPerAuthority.[Authority Code]
GROUP BY TblTrustees.[Authority Code], TblTrustees.[Authority Name], TblTrustees.[Incumbent (I) New (N) Vacant (V)], TblTrustees.Elected
HAVING (((TblTrustees.[Incumbent (I) New (N) Vacant (V)])="i") AND ((TblTrustees.Elected)="y"));

 

[tweedle]

Are we sure [CountofNumber of Positions] is null, and not 0(zero)?

Is it possible for them to be in TblTrusteesPerAuthority if their [CountofNumber of Positions] is null|zero?

I would try loosening the JOIN to an OUTER and if I still wasn't happy, I'd flip the tables around.

 

[erind]

What is the difference between Null and 0? I thought these were the same.
Changing JOIN to OUTER JOIN didn't change my results.

Every entry in the TblTrusteesPerAuthority has a value in the Number of Positions column greater than 0. What I need in this query is for the absolute value to show up in one column (the total Number of Positions, unchanging. I have added a column Abs([Number of Positions]) AS [Number Of Trustees] to get this to show the absolute value) and the number of positions where Elected=y AND Incumbent=i to show in another column. I can get these numbers to show, except that if there are no Elected Incumbents, the Number of Positions doesn't show either, even though it's absolute value is greater than 0.

Is it possible to do this if I take out the Elected=y and Inc=i columns in the design, and just incorporate them into an IIF statement? Any other ideas?

 

[james_lankford]

null means a value has never been entered or the field has been specifically been set to NULL
null is not zero, null is not a blank string
null is the vast emptiness of the distant void of the universe
null cannot be compared to anything
it cannot be = anything
it cannot be > anything
it cannot be < anything

and I don't understand your original question
this
Elected=y and Inc=I
eliminates all rows that do not meet that criteria
you will never see rows that do not meet that criteria, so you can never count them, abs them or iif them

 

[erind]

Sorry, I'll try and clarify.

TblTrusteesPerAuthority has 4 columns: ID, Auth Code, Auth Name and Number of Positions.

TblTrustees has the same information, but there are more columns including Elected (values will be yes, no or n/a) and INV (Values will be I, N or V), so if [TblTrusteesPerAuthority.Number of Positions]=5, there are 5 rows in the TblTrustees (1 to represent each position). The 2 tables are connected by Auth Code.

I am trying to create a query that will show the rows where Elected=y and INV=I, in relation to the Number of Trustees. If there are 5 positions, and 3 are occupied by Elected Incumbents, columns will show Number of Trustees=5 & ElecInc=3. If there are 5 positions and they are either not elected or not incumbent (say if INV=i, but Elected=n ) I want the columns to show up as Number of Trustees=5, ElecInc=0. The Number of Trustees should never change, just the number of trustees who fit the added criteria.

Right now, as you say, it will not count the rows where the criteria isn't met, so it only returns values for the Number of Trustees (and it does give the absolute value, not the count value) where Elec=y and Inc=i, even though Number of Trustees for the other rows>0. I have set the SQL to LEFT OUTER JOIN the tables, and this didn't change my results.

 

[james_lankford]

ok, I can't explain how to do it, but this sounds perfect for a pivot table
try using this sql and then have the results returned as a pivot table and then sum and total things up

SELECT 
  TblTrustees.[Authority Code], 
  TblTrustees.[Authority Name], 
  TblTrustees.[Incumbent (I) New (N) Vacant (V)], 
  TblTrustees.Elected, 
  Count( TblTrustees.[Incumbent (I) New (N) Vacant (V)] ) AS [CountOfNumber of Incumbent], 
  Count( TblTrustees.[Authority Code] ) AS [CountOfNumber of Authority Code], 
  Count( TblTrustees.Elected ) AS [CountOfNumber of Elected], 
FROM 
  TblTrustees
GROUP BY 
  TblTrustees.[Authority Code], 
  TblTrustees.[Authority Name], 
  TblTrustees.[Incumbent (I) New (N) Vacant (V)], 
  TblTrustees.Elected