CODEWITHSUNDEEP
sqlms-access
Leroy
Leroy

Reputation: 644

Count number of table columns that are not null - MS Access SQL

I have a table which I have shown a simplified example of below:

ID | Item1 | Item2 | Item3 | Item4 | Item5
------------------------------------------
A  | NULL  | NULL  | YES   | YES   | NULL
B  | NULL  | NULL  | NULL  | YES   | NULL
C  | NULL  | NULL  | NULL  | NULL  | NULL

I want to return the following data set:

ID  | Count
------------
A   | 2
B   | 1
C   | 0

I.e. I want a count of how many of the columns are NOT NULL for that ID

One potential solution would be

SELECT
  ID,
  SUM(
    IIf(Item1 is NULL,0,1)
    +
    IIf(Item2 is NULL,0,1)
    +
    IIf(Item3 is NULL,0,1)
    +
    IIf(Item4 is NULL,0,1)
    +
    IIf(Item5 is NULL,0,1)
    ) 'Count'
FROM 
   tableName
GROUP BY 
   ID

However in practice the real table I am using has over a hundred columns and I would prefer to avoid having to write out the names of each column. Is there a simpler way to do this?

Upvotes: 0

Views: 2060

Answers (3)

Minty
Minty

Reputation: 1626

Your data is not normalised and therefore you are having to perform gymnastics in your code to work around the problem.

Your data should be stored vertically not horizontally;

ID | ItemNo | Value
---------------------
A  |  2     | 1     
A  |  3     | 1  
B  |  4     | 1

This would make your query a simple total query, and allow for any number of items. You are also only storing data when you have some not for every case.

Edit: This will loop through the fields

Dim Rst As Recordset 
Dim f As Field 

Set Rst = CurrentDb.OpenRecordset(TableName) 
For Each f In Rst.Fields 
   Debug.Print (f.name) 
Next 
Rst.Close

Upvotes: 2

Don George
Don George

Reputation: 1328

You can use VBA to loop through every record and field:

Function CountFields()
Set db = CurrentDb()
db.Execute ("delete * from ItemCounts")
Set RS = db.OpenRecordset("select * from [DataTable]")
RS.MoveFirst
Do While Not RS.EOF
    Id = RS.Fields("ID").Value
    Count = 0
    For Each Item In RS.Fields
        If (Item.Name <> "ID" And RS.Fields(Item.Name).Value <> "") Then Count = Count + 1
        Next Item
    db.Execute ("insert into ItemCounts (ID,[count]) select " & Id & "," & Count)
    RS.MoveNext
    Loop
MsgBox ("done")
End Function

This puts the counts in a table called ItemCounts, which needs to be set up before the VBA is executed. The fields in that table are ID and Count.

And, if you can reformat the source data, I agree with Minty - but I know that's not always feasible.

Upvotes: 2

Gustav
Gustav

Reputation: 55806

You can reduce it a little:

SELECT
  ID,
  ABS(SUM((Item1 is Not NULL)+(Item2 is Not NULL)+(Item3 is Not NULL)+(Item4 is Not NULL)+(Item5 is Not NULL))) As [Count]
FROM 
   tableName
GROUP BY 
   ID

Upvotes: 1

Related Questions