How to find out what columns in a table are never used ? (1 Viewer)

[amorosik]

For example, in a table CUSTOMERS there is 5 column, NAME, SURNAME, ADDRESS, CITY, EMAIL
The CUSTOMERS table already contains thousands of rows
But the CITY field has never been valued on any line
Here, the question is: how to find the columns within a table, which have never been filled in?
Not a vba loop that scrolls through all the columns and tests individually, but I mean a single query that returns me the columns of the table in which not even a value has been stored on all the rows present in the table

 

[ebs17]

COUNT(*) ... counts records
COUNT(City) ... counts contents in the field. NULL content is not taken into account.

 

[amorosik]

COUNT(*) ... counts records
COUNT(City) ... counts contents in the field. NULL content is not taken into account.

"..Not a vba loop that scrolls through all the columns and tests individually.."

 

[ebs17]

Where do I say something about VBA or loop? I thought you could put two and two together.

SELECT
   COUNT(*) AS X,
   COUNT(City) AS xCity
FROM
   TableX

Adding the other fields is manual operation. Certainly affordable with four more.

Why are you creating fields that nobody cares about? You don't have such problems with a Required property.

 

[plog]

This will tell you the "empty" rows for a field (both NULL and empty strings):

SELECT COUNT(Nz([YourFieldHere],'')) AS EmptyRecords
FROM YourTableHere
WHERE Len(Nz([YourFieldHere],''))=0

 

[561414]

SELECT
    'The empty columns in the table are ' & empty_columns AS result
FROM (
    SELECT
        Switch(
            CountOfId = 0, "Id",
            CountOfFirstName = 0, "FirstName",
            CountOfSurName = 0, "SurName",
            CountOfAddress = 0, "Address",
            CountOfCity = 0, "City",
            CountOfEmail = 0, "Email",
            True, ""
        ) AS empty_columns
    FROM (
        SELECT
            Count(customers.Id) AS CountOfId,
            Count(customers.FirstName) AS CountOfFirstName,
            Count(customers.SurName) AS CountOfSurName,
            Count(customers.Address) AS CountOfAddress,
            Count(customers.City) AS CountOfCity,
            Count(customers.Email) AS CountOfEmail
        FROM customers
    )
)

Well, this will find you the first column where everything is empty.

 

[561414]

And this will give you all column names:

SELECT
    'The empty columns in the table are ' & empty_columns AS result
FROM (
    SELECT
Switch(
        CountOfId = 0, "Id",
        True, ""
    ) & Switch(
        CountOfFirstName = 0, ", FirstName",
        True, ""
    ) & Switch(
        CountOfSurName = 0, ", SurName",
        True, ""
    ) & Switch(
        CountOfAddress = 0, ", Address",
        True, ""
    ) & Switch(
        CountOfCity = 0, ", City",
        True, ""
    ) & Switch(
        CountOfEmail = 0, ", Email",
        True, ""
        ) AS empty_columns
    FROM (
        SELECT
            Count(customers.Id) AS CountOfId,
            Count(customers.FirstName) AS CountOfFirstName,
            Count(customers.SurName) AS CountOfSurName,
            Count(customers.Address) AS CountOfAddress,
            Count(customers.City) AS CountOfCity,
            Count(customers.Email) AS CountOfEmail
        FROM customers
    )
)

 

[561414]

This will also work

SELECT
    IIf(CountOfId = 0, "Id", "") & IIf(CountOfFirstName = 0, ", FirstName", "") & IIf(CountOfSurName = 0, ", SurName", "") & IIf(CountOfAddress = 0, ", Address", "") & IIf(CountOfCity = 0, ", City", "") & IIf(CountOfEmail = 0, ", Email", "") AS empty_columns
FROM (
    SELECT
        Count(customers.Id) AS CountOfId,
        Count(customers.FirstName) AS CountOfFirstName,
        Count(customers.SurName) AS CountOfSurName,
        Count(customers.Address) AS CountOfAddress,
        Count(customers.City) AS CountOfCity,
        Count(customers.Email) AS CountOfEmail
    FROM customers
) t

 

[Pat Hartman]

I don't understand the purpose of the Switch. Why not:

Select
            Count(customers.Id) AS CountOfId,
            Count(customers.FirstName) AS CountOfFirstName,
            Count(customers.SurName) AS CountOfSurName,
            Count(customers.Address) AS CountOfAddress,
            Count(customers.City) AS CountOfCity,
            Count(customers.Email) AS CountOfEmail
From YourTable

???

 

[561414]

Additionally, this will just give you a zero if the column has only nulls, which can be better for doing things in VBA:

SELECT
    COUNT(Id) AS CountOfId,
    COUNT(FirstName) AS CountOfFirstName,
    COUNT(SurName) AS CountOfSurName,
    COUNT(Address) AS CountOfAddress,
    COUNT(City) AS CountOfCity,
    COUNT(Email) AS CountOfEmail
FROM customers;

 

[561414]

I don't understand the purpose of the Switch. Why not:

Select
            Count(customers.Id) AS CountOfId,
            Count(customers.FirstName) AS CountOfFirstName,
            Count(customers.SurName) AS CountOfSurName,
            Count(customers.Address) AS CountOfAddress,
            Count(customers.City) AS CountOfCity,
            Count(customers.Email) AS CountOfEmail
From YourTable

???

The switch will output the Column Names, I also posted a simpler version and it's similar to yours, if not equal.