**Dynamically* include fields (in a query) where record count <> NULL

[skydivetom]

Hello:

I would like some recommendations with creating a very fundamental query. I have done quite a few queries, but never had the need for this new requirement.

Background first:
- I have multiple tables some of them having more than, e.g., 80+ fields.
- Any of these tables could have as many as 50,000 records each.
- However, depending on the table, many of the fields only contain only NULL values.

Ok, let me first alleviate the need for some questions I already see coming:
1. No need to ask/state: "Why some many fields in a table? You should consider to normalize."
2. No need to ask: "Why do you have so many fields with NULL values in a table?"

I have attached a *generic* example DB which contains 1 table and 1 query:
a. The sample table contains 1 [ID] field and 20 data fields.
b. The sample table 50 records.
c. Out of the 20 data fields (excluding autonumber [ID] field), only 4 fields contain values. The remaining 16 data fields only contain NULL values.
d. Query "qryExample" is a GROUP_BY query showing the total count of records with values. Attached JPG (columns B:C) is a transposed view of the query.

Now, here's what I need some help with:
- For sake of argument, let's say I have such query (containing, e.g., 80 fields) but only 10 out of the 80 fields have some values.
- What I don't want is to having to scroll left-right-left (and up/down) to identify the fields that contain values.
- So, ultimately, I'd like to use a query which only brings in the **fields** that do have records (where record CNT <> NULL). Per the JPG (columns E:F), I ultimately want to see the output of FIELD_01, FIELD_07, FIELD_15, FIELD_20 (it does NOT matter whether fields are transposed or not).

My question(s):
1a. What is the SQL that would allow me to generate a **DYNAMIC** output (based on [tbl_Example]) which would only show me the non-empty fields? So, today the query should include 4 fields, but tomorrow it may include 5 fields (w/o me having to manually add the new field containing data based on another run).
1b. Ideally, I would actually need two queries... one that would show the count (e.g., 50, 50, 25, 30) and a 2nd query that would give me those 4 fields (plus admin field [ID]) showing the actual 50 records.
2. Based on 1a & 1b, what SQL would do this directly by querying on [tbl_Example]? Or do I need to use [qryExample] as a "Helper Query" to identify the fields with & without a NULL value first?

Thank you,
Tom

[kd2017]

Dynamically build the queries using vba.

[skydivetom]

kd2017 -- thank you... I kinda figured VBA would be the best way forward.

Do you have any pointers as to where I might find a framework for this scenario?

[kd2017]

maybe something like this:

Public Sub test()
    Debug.Print build_query("tbl_Example")
End Sub

Public Function build_query(table_name As String) As Variant
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qry As String
    Dim rslt As Variant
    
    rslt = Null
    
    qry = "SELECT "
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT TOP 1 * FROM " & table_name)
    For Each fld In rs.Fields
        If DCount(fld.Name, table_name) > 0 Then
            qry = qry & "[" & fld.Name & "], "
        End If
    Next
    rs.Close
    
    If qry <> "SELECT " Then
        qry = Left(qry, Len(qry) - 2) & " FROM " & table_name & ";"
        rslt = qry
    End If

ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    build_query = rslt
    Exit Function
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler

End Function

[skydivetom]

kd2017 -- thank you for the code... definitely going in the right direction.

I included a form and added a line to pass selected listbox value into string. Presently though, a query is not generated. What might be missing in the code?

Thank you,
Tom

[orange]

Tom,

This creates a temp query "TempZXZ" each time it is called. Should be helpful.

Option Compare Database
Option Explicit
' ----------------------------------------------------------------
' Procedure Name: whichFldsHaveValues
' Purpose: To identify which short text fields in table/recordset have values
'                 and create and run a tempquery to count the total records with values in these fields
' Procedure Kind: Function
' Procedure Access: Public
' Parameter sTableName (String): table whose fields to check
' Author: Jack
' Date: 02-Jul-21
' ----------------------------------------------------------------
Function whichFldsHaveValues(sTableName As String)
10    On Error Resume Next
20    DoCmd.DeleteObject acQuery, "TempZXZ"   'Delete the temp query if it exists
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim tmpQDef As DAO.QueryDef
          Dim FinalSQL As String
          Dim fld As DAO.Field
          Dim sresult As String
          Dim QString As String
          Dim i As Integer
          Dim FieldsSQL
          Dim createSQL1 As String  'start sql
          Dim createSQL2 As String  'end sql
30        Set db = CurrentDb
40        Set rs = db.OpenRecordset(sTableName)
50        For Each fld In rs.Fields
60            If fld.Value > " " Then
70                sresult = sresult & fld.Name & " "
80            End If
90        Next
100       sresult = Mid(sresult, 1, Len(sresult) - 1)
110       FieldsSQL = Split(sresult, " ")
          
          'Create the query sql
120       createSQL1 = "Select "
130       createSQL2 = " from " & sTableName
140      For i = LBound(FieldsSQL) To UBound(FieldsSQL)
150       QString = QString & "Count (" & FieldsSQL(i) & ") as Cnt" & FieldsSQL(i) & ", "
160       Next i
          
170       FinalSQL = createSQL1 & Mid(QString, 1, Len(QString) - 2) & createSQL2 & ";"
180        Set tmpQDef = db.CreateQueryDef("TempZXZ", _
                FinalSQL)
          
190             DoCmd.OpenQuery "TempZXZ", acViewNormal  'query result in query window
End Function

This is how the routine is called from immediate window

?whichFldsHaveValues("tbl_example")


Result:

[kd2017]

maybe something like this:

Private Sub cmdCreateQuery_Click()
    Dim qdef As QueryDef
    Dim qname As String
    Dim sql As String
    
    qname = "some_query_name"
    sql = build_query(Me.lstSourceFile)
    
    'delete the query if it already exists
    For Each qdef In CurrentDb.QueryDefs
        If qdef.Name = qname Then
            CurrentDb.QueryDefs.Delete qname
            Exit For
        End If
    Next
    
    'save the new query
    Set qdef = CurrentDb.CreateQueryDef(qname, sql)
    
    'open the query
    DoCmd.OpenQuery qname

    
End Sub

Public Function build_query(table_name As String) As Variant

On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qry As String
    Dim rslt As Variant
    
    'Add line to pass value of table name (from listbox) into SQL
    'table_name = Forms!F01_MainMenu!lstSourceFile
    
    
    rslt = Null
    
    qry = "SELECT "
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT TOP 1 * FROM " & table_name)
    For Each fld In rs.Fields
        If DCount(fld.Name, table_name) > 0 Then
            qry = qry & "[" & fld.Name & "], "
        End If
    Next
    rs.Close
    
    If qry <> "SELECT " Then
        qry = Left(qry, Len(qry) - 2) & " FROM " & table_name & ";"
        rslt = qry
    End If

ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    build_query = rslt
    Exit Function
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler

End Function

[skydivetom]

Jack:

Your solution is awesome... please see attached sample DB incl. the form's listbox passing the selected table value into the function.

I generate the CNT query exactly as I hoped to do.

I envision to add a radio buttons (option group for CNT query vs. list all records). I'd image as I only have to place the mid part of the SQL into the CASE statement, right? That is, if radio button value = 1 then use CNT SQL; alternatively, if radio button value = 2 then use SQL without count.

I'll play w/ that later on this evening... gotta run here for a moment. Thank you very much though for providing this code... totally awesome.

If you don't mind, I'll keep this post open up a bit longer in case of follow-up question. Fair enough?

Cheers,
Tom

[skydivetom]

kd2017 -- your VBA works beautifully too!!! Awesome sauce!!

Thank you both, kd2017 and jack for providing a wonderful solution. I'm thrilled about this method!!!

[orange]

Tom,

In your database with the form, I changed this line

Private Sub cmdCreateQuery_Click()

   ' Debug.Print build_query("tbl_Example")
  Call whichFldsHaveValues("tbl_example")  '<===============
End Sub

This gives the counts. I haven't looked further yet.

[skydivetom]

Jack -- thanks for the update. I will use the listbox parameter to pass the table name into the function. So I won't hardcode the table name in the code.

I will get to later on or early tomorrow morning. I will provide an updated example with 1 or 2 additional sample tables.

Thanks again.

[orange]

OK,

I have adjusted the form to offer a choice -details/summary counts.
I did not deal with listbox selections etc.

[skydivetom]

Jack -- sorry, the attached ZIP file does NOT include the form w/ the option group. Could you pls post the version w/ radio buttons as illustrated? Thank you.

[skydivetom]

All -- attached DB includes the solution to this process. Again, data is only for demo purposes but it's the process that counts.

Thank you for sharing this code... it'll will definitely make the analysis of more extensive tables (WRT # of fields AND records) much easier.

Cheers,
Tom

[orange]

Tom,

I have created another zip (attached) with related form and code.

Update: Just saw your database in the zip file in post#14. That version of the whichFldsHaveValues
function has been updated considerably since first posted.

Look at the code in the attached file.