Query that returns the column names that have never been used (1 Viewer)

amorosik

amorosik

Member
Local time
Today, 15:57
Joined
Apr 18, 2020
Messages
541
I need a query that returns, given the name of a table in the db, all the names of the fields that have never been filled in any row of the data present
Basically the fields I could also delete because at the moment they don't contain any information
How to make a unique query that return the field name as above?
 
don't you mean

never been filled in any column of the data?

assuming you do, then I don't see how you could just display the column name unless you use a union query

SELECT "fieldname1" FROM myTable Where count([fieldname1])=0
UNION SELECT "fieldname2" FROM myTable Where count([fieldname2])=0
UNION SELECT "fieldname3" FROM myTable Where count([fieldname3])=0
etc

not tested but might need to use Having rather than Where

alternative would be a VBA routine to loop through the fields
 
CJ_London said:
don't you mean

never been filled in any column of the data?

assuming you do, then I don't see how you could just display the column name unless you use a union query

SELECT "fieldname1" FROM myTable Where count([fieldname1])=0
UNION SELECT "fieldname2" FROM myTable Where count([fieldname2])=0
UNION SELECT "fieldname3" FROM myTable Where count([fieldname3])=0
etc

not tested but might need to use Having rather than Where

alternative would be a VBA routine to loop through the fields
Click to expand...

A field never been filled in any ROW of the data
( if visualizzation is by grid, an empty column,)

Already now I have a routine that iterates through every single field, and checks if it has ever been valued within the table
But it's incredibly slow
So I'm looking for an alternative solution, with better performance
 
Code: 
Public Sub LogEmptyColumns()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim rsNull As DAO.Recordset
  Dim fld As DAO.Field
  Dim tdf As DAO.TableDef
  Dim FieldName As String
  Dim TableName As String
  Dim recordcount As Long
  Dim strSql As String
  Set db = CurrentDb
 
  On Error GoTo ErrLabel
 strSql = "Delete * from tblEmptyColumns"
 For Each tdf In db.TableDefs
    TableName = "[" & tdf.Name & "]"
    For Each fld In tdf.Fields

      FieldName = "[" & fld.Name & "]"
      strSql = "Select " & FieldName & " FROM " & TableName & " WHERE " & FieldName & " is not null"
      Set rs = db.OpenRecordset(strSql)
      If Not rs.EOF And Not rs.BOF Then
        rs.MoveLast
        recordcount = rs.recordcount
      End If
     ' Debug.Print TableName & " " & FieldName & " " & rs.recordcount
      If recordcount = 0 Then
        
        strSql = "Insert into tblEmptyColumns (TableName,FieldName) values ('" & TableName & "', '" & FieldName & "')"
        'Debug.Print strSql
        CurrentDb.Execute strSql
        
      End If
      recordcount = 0

    Next fld
  Next tdf
  Exit Sub
ErrLabel:
  If Err.Number = 3831 Then
   MsgBox FieldName & " " & " in " & TableName & " is a multivalue field. You need to verify this seperately."
   strSql = "Insert into tblEmptyColumns (TableName,FieldName,IsMultiValueField) values ('" & TableName & "', '" & FieldName & "', True)"
   CurrentDb.Execute strSql
    Err.Clear
    recordcount = 0
    
    Resume Next
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

It is fast, but you have to verify Multi Value Fields seperately. I log them into the table and identify them.

tblEmptyColumns
--TableName
--FieldName
--IsMultiValueField

I include the system tables, but you can query them out once logged.
 
I do not feel like wasting my time on MVFs. So I left it to the user to check those manually. Which is their fault for using them, anyways.

The issue with MVF is this line fails with an MVF
Code: 
  strSql = "Select " & FieldName & " FROM " & TableName & " WHERE " & FieldName & " is not null"
Untested but for an MVF I think it gets modified to
Code: 
  strSql = "Select " & FieldName & " FROM " & TableName & " WHERE " & FieldName & ".value is not null"

Then you could make a function from the following code to determine if a field is a complex field

Access – VBA – Find Attachment and MultiValued Fields

Demonstrating how we can use a VBA function to loop through a database's tables to identify Attachment and MultiValued Fields.
www.devhut.net

FYI, I know of no way to check if a field has "ever" been used. This only checks if the all records are currently null for a field.
 
MajP said:
Code: 
Public Sub LogEmptyColumns()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim rsNull As DAO.Recordset
  Dim fld As DAO.Field
  Dim tdf As DAO.TableDef
  Dim FieldName As String
  Dim TableName As String
  Dim recordcount As Long
  Dim strSql As String
  Set db = CurrentDb

  On Error GoTo ErrLabel
strSql = "Delete * from tblEmptyColumns"
For Each tdf In db.TableDefs
    TableName = "[" & tdf.Name & "]"
    For Each fld In tdf.Fields

      FieldName = "[" & fld.Name & "]"
      strSql = "Select " & FieldName & " FROM " & TableName & " WHERE " & FieldName & " is not null"
      Set rs = db.OpenRecordset(strSql)
      If Not rs.EOF And Not rs.BOF Then
        rs.MoveLast
        recordcount = rs.recordcount
      End If
     ' Debug.Print TableName & " " & FieldName & " " & rs.recordcount
      If recordcount = 0 Then
       
        strSql = "Insert into tblEmptyColumns (TableName,FieldName) values ('" & TableName & "', '" & FieldName & "')"
        'Debug.Print strSql
        CurrentDb.Execute strSql
       
      End If
      recordcount = 0

    Next fld
  Next tdf
  Exit Sub
ErrLabel:
  If Err.Number = 3831 Then
   MsgBox FieldName & " " & " in " & TableName & " is a multivalue field. You need to verify this seperately."
   strSql = "Insert into tblEmptyColumns (TableName,FieldName,IsMultiValueField) values ('" & TableName & "', '" & FieldName & "', True)"
   CurrentDb.Execute strSql
    Err.Clear
    recordcount = 0
   
    Resume Next
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

It is fast, but you have to verify Multi Value Fields seperately. I log them into the table and identify them.

tblEmptyColumns
--TableName
--FieldName
--IsMultiValueField

I include the system tables, but you can query them out once logged.
Click to expand...

I thank you
I already have the routine that does the necessary work
But I wanted to understand if it is possible to get answer from a query
 
Code: 
Already now I have a routine that iterates through every single field, and checks if it has ever been valued within the table
But it's incredibly slow
And mine is incredibly fast!!
 
I wrote something similar - just outputs to Immediate Window:
Code: 
Function FindEmptyFieldsInTables(ParamArray tbls()) As Boolean

  Dim i As Integer, db As DAO.Database
 
  Set db = CurrentDb
  With db.TableDefs
    If UBound(tbls) < 0 Then
      For i = 0 To .Count - 1
        Call FindEmptyFields(.Item(i), db)
      Next i
    Else
      For i = 0 To UBound(tbls)
        Call FindEmptyFields(.Item(tbls(i)), db)
      Next i
    End If
 
  End With
  Set db = Nothing
  FindEmptyFieldsInTables = Err = 0
 
End Function

Private Function FindEmptyFields(tdf As DAO.TableDef, dbPass As DAO.Database) As Boolean

  Dim i As Integer, strSQL As String
 
  With tdf
    Debug.Print "Table:", .Name
'    If Len(.Connect) = 0 Then
      For i = 0 To .Fields.Count - 1
        With .Fields(i)
          Select Case .Type
          Case dbText, dbMemo, dbChar
            strSQL = strSQL & ", SUM(IIf(Len([" & .Name & "] & '') > 0, 1, 0)) AS [count_" & .Name & "]"
          Case dbAttachment, dbComplexByte, dbComplexDecimal, dbComplexDouble, dbComplexGUID, dbComplexInteger, dbComplexLong, dbComplexSingle, dbComplexText
            ' ignore attachment and multi-valued fields
          Case Else
            strSQL = strSQL & ", SUM(IIF([" & .Name & "] IS NOT NULL, 1, 0)) AS [count_" & .Name & "]"
          End Select
        End With
      Next i
      strSQL = "SELECT COUNT(*) AS Rows, " & Mid(strSQL, 3) & " FROM [" & .Name & "];"
'      Debug.Print strSQL
      With dbPass.OpenRecordset(strSQL)
        Debug.Print "Rows:", .Fields(0)
        For i = 1 To .Fields.Count - 1
          If .Fields(i) = 0 Then
            Debug.Print "Field:", Mid(.Fields(i).Name, Len("count_") + 1) & " is empty"
          End If
        Next i
        .Close
      End With
'    End If
    Debug.Print ' blank line
  End With

  FindEmptyFields = Err = 0
 
End Function
If you don't pass any table names to FindEmptyFieldsInTables it will go through all tables in db.

There are commented out lines for remote tables only because I tested on a database that linked to an SQLServer and I couldn't remember the password!)
 
I'm looking for an alternative solution, with better performance
Click to expand...
Don't know if the union query would be any quicker, you could still build in vba if you are talking about more than one table
 
Can you do it with one query?
Select RecID where Max(fld1) Is Null or Max(fld2) Is Null or Max(fld3) Is Null, ......

However, if you allow ZLS in any string field, you would need to change it to accommodate zls
 
Pat Hartman said:
Can you do it with one query?
Click to expand...
The example I showed does it in a single query for each table (uncomment the Debug.Print strSQL line to see the query) and handles ZLS and gives the table's record count to boot!
 
SQL: 
SELECT
   COUNT(T.FieldA) AS FieldA,
   COUNT(T.FieldB) AS FieldB,
   COUNT(T.FieldC) AS FieldC
FROM
   TableX AS T
This single record can be evaluated using VBA, look at 0-Values.
If the unknown table contains 200 fields or so, you can also create the SQL statement using VBA.
If VBA handling is too strenuous for you, you can also do it by hand with diligence.
 
Count works as well as Max but has the same problem with ZLS.
 
Pat Hartman said:
Count works as well as Max but has the same problem with ZLS.
Click to expand...
Hence the 'lot of code'! 😂

If you fancy writing out those expressions (or worse yet, trying to fiddle with copy'n'paste in the query builder) for all the fields in each table then be my guest!
 
ebs17 said:
SQL: 
SELECT
   COUNT(T.FieldA) AS FieldA,
   COUNT(T.FieldB) AS FieldB,
   COUNT(T.FieldC) AS FieldC
FROM
   TableX AS T
Click to expand...
Just as FYI, this won't work as written in Access - stupidly it won't allow the field alias to be the same as the name of a a field used in the expression.

Of course, other less limited database systems can handle that.
 
The variant with count(DateField)=0 from @CJ_London (#2) should work fine.

Tested with NW2-Dev: runtime < 1 sec (incl. system tables)
Used codemodule see attachment.
 

Attachments

Last edited:
[OT]
cheekybuddha said:
Just as FYI, this won't work as written in Access - stupidly it won't allow the field alias to be the same as the name of a a field used in the expression.
Click to expand...
Full qualified with table alias (or name) it will work: COUNT(T.FieldA) AS FieldA
 
With help of @cheekybuddha this handles complex filed types now. You can choose to save the MSYS and check for null string
Code: 
Public Sub testAllTables()
  LogAllEmptyTableColumns True, False
End Sub
Public Sub TestTable()
  CurrentDb.Execute "delete * from tblEmptyColumns"
  LogTableAndFields "T_Brand", False
End Sub

Public Sub LogAllEmptyTableColumns(Optional HandleSystemTables As Boolean = False, Optional HandleEmptyStrings As Boolean = True)
  Dim tdf As DAO.TableDef
  Dim strSql As String
  Dim db As DAO.Database
  Set db = CurrentDb
  Dim start As Long
 
 ' On Error GoTo ErrLabel
 
 strSql = "Delete * from tblEmptyColumns"
 CurrentDb.Execute strSql
 start = Timer
 For Each tdf In db.TableDefs
    If Not (HandleSystemTables = False And Left(tdf.Name, 4) = "MSYS") Then
      LogTableAndFields tdf.Name, HandleEmptyStrings
    End If
 Next tdf
  MsgBox "Elapsed Time " & Timer - start
  Exit Sub
ErrLabel:
    MsgBox Err.Number & " " & Err.Description

End Sub

Public Sub LogTableAndFields(TdfName As String, HandleEmptyStrings As Boolean)
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim tdf As TableDef
  Dim FieldName As String
  Dim TableName As String
  Dim recordcount As Long
  Dim strSql As String
  Dim StrEmpty As String
 
  On Error GoTo ErrLabel
  Set db = CurrentDb
  Set tdf = db.TableDefs(TdfName)
  If HandleEmptyStrings Then
   StrEmpty = " & '' <> ''"
  Else
   StrEmpty = " Is NOT NULL"
  End If
 
  TableName = "[" & tdf.Name & "]"
 
  For Each fld In tdf.Fields
     FieldName = "[" & fld.Name & "]"
      
      Select Case fld.Type
        Case dbAttachment
          FieldName = FieldName & ".[filename]"
         Case dbComplexByte, dbComplexDecimal, dbComplexDouble, dbComplexGUID, dbComplexInteger, dbComplexLong, dbComplexSingle, dbComplexText
           FieldName = FieldName & ".[value]"
       End Select
        
         strSql = "Select * FROM " & TableName & " WHERE " & FieldName & StrEmpty
        ' Debug.Print strSql
         Set rs = CurrentDb.OpenRecordset(strSql)
      
      If Not rs.EOF And Not rs.BOF Then
        rs.MoveLast
        recordcount = rs.recordcount
      End If
     ' Debug.Print TableName & " " & FieldName & " " & rs.recordcount
      
      If recordcount = 0 Then
      'Handle different inserts
        Select Case fld.Type
          Case dbAttachment, dbComplexByte, dbComplexDecimal, dbComplexDouble, dbComplexGUID, dbComplexInteger, dbComplexLong, dbComplexSingle, dbComplexText
            strSql = "Insert into tblEmptyColumns (TableName,FieldName,IsMultiValueField) values ('" & TableName & "', '" & FieldName & "', True)"
          Case Else
           strSql = "Insert into tblEmptyColumns (TableName,FieldName,IsMultiValueField) values ('" & TableName & "', '" & FieldName & "', False)"
        End Select
        CurrentDb.Execute strSql
      End If
      
      recordcount = 0
    Next fld
 
  Exit Sub
ErrLabel:
    MsgBox Err.Number & " " & Err.Description
End Sub
 
You must log in or register to reply here.

Similar threads

D
Solved Nesting IIF Statement with functions that end in (field)
Replies
13
Views
418
arnelgp
arnelgp
D
Query to return one id for all unique combinations of four columns
Replies
4
Views
292
dobseh
D
S
Solved Using a Does Not Contain Criteria in an IIf Statement
Replies
9
Views
421
Pat Hartman
P
C
querying outlook
Replies
2
Views
261
CJ_London
C
109bow
Solved filter results in a query
Replies
3
Views
267
109bow
109bow

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom