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

[amorosik]

"..but exclude those methods.."
No, you are writing things that are not true
What I exclude is cycling hundreds of fields to compose a query, but what I have shown above is a very different thing, it is a single query that returns as many records as there are fields that make up the indicated table

"..As with some of your other threads, you are just being contentious.."
No, i'm asking a specific thing, and on a tech forum, I would expect an answer to the question

"..there is no 'column_name' functionality in access sql.."
I demonstrated above a single query to read the field names of a table
Are you telling me you don't know how to run a passthrough query?

 

[amorosik]

You are asking for a query to list all unused data fields and you don't write that you are using SQL server as backend?
But then there would be a possibility to solve this only with SQL.

And who said I'm using Sql Server?
I only made one example, on Sql Server, to answer the user cheekybuddha
But the question asked is a query on a generic db, then Sql Server will have its own system to read the fields of a table, Postgresql will have another system, Firebird will have its own system, this is not the important thing
The important thing is how to set up the query to get the names of the fields never used (which I think are the ones that return all null in all rows)

 

[amorosik]

If you had said that you are working with SQLServer tables then we could have given you a query that might do what you want.

You might still need some code (VBA or T-SQL) to get the information about which fields contain no data - I can't remember whether SQLServer keeps those statistics on individual fields.

In MySQL there are such statistics, but they are not always current.

Ok to simplify things, since Sql Server is probably a db familiar to many users of this forum, let's suppose we are dealing with Sql Server

 

[cheekybuddha]

I only made one example, on Sql Server, to answer the user cheekybuddha
But the question asked is a query on a generic db, then Sql Server will have its own system to read the fields of a table, Postgresql will have another system, Firebird will have its own system, this is not the important thing

I answered this in Post #34

In Access, you query the TableDefs collection object (using VBA)

Ok to simplify things, since Sql Server is probably a db familiar to many users of this forum, let's suppose we are dealing with Sql Server

See here on StackOverflow

See here on StackOverflow

 

[Pat Hartman]

@amorosik Did you bother to try the database I uploaded back in #25? It works against ANY db whether the tables are local or linked. If you have a FE that is linked to SQL server, select that. Otherwise, if the BE is jet or ACE, select that. The code does ALL the tables in the linked database and produces a concensed output rather than the other examples which output using dbug print and are hard to work with.

The table I built to hold the output only holds 20 columns for "empty" column names. Just add more if you have wide tables that have more than 20 empty columns. Just use the same naming pattern so you won't break the code. You can go up to 253. I used two columns for other things.

 

[amorosik]

@amorosik Did you bother to try the database I uploaded back in #25? It works against ANY db whether the tables are local or linked. If you have a FE that is linked to SQL server, select that. Otherwise, if the BE is jet or ACE, select that. The code does ALL the tables in the linked database and produces a concensed output rather than the other examples which output using dbug print and are hard to work with.

The table I built to hold the output only holds 20 columns for "empty" column names. Just add more if you have wide tables that have more than 20 empty columns. Just use the same naming pattern so you won't break the code. You can go up to 253. I used two columns for other things.

Yes I saw, thank you
But since the work is essentially done via vba by scrolling through the fields and composing the query, I don't consider it useful for the purposes of the current request

 

[CJ_London]

I'm going to drop out, this is a complete waste of everyone's time.

The initial post implies a single table in Access
Around post 35 - the OP says he never uses the JET/ACE databases with the implication it is actually sql server. Leaving everyone barking up the wrong tree for 33 posts.

and the code he/she/they/them quotes just lists all the fields in a table, not those without any values which is the requirement of the original post

 

[The_Doc_Man]

But the question asked is a query on a generic db,

In the case of a generic DB, you cannot depend on vendor-specific extensions of SQL. Which means you are limited to ANSI SQL Standards. For that case, the answer is NO you cannot do this in a simple, generic-SQL query.

The reason is that generic SQL does not deal with metadata, and the questions you are asking are based on metadata.

IF you name a specific SQL engine, you certainly might find that some metadata extensions have been built into the vendor's SQL. But each case is different. So for the generic question, No - you can't do what you asked.

 

[amorosik]

In the case of a generic DB, you cannot depend on vendor-specific extensions of SQL. Which means you are limited to ANSI SQL Standards. For that case, the answer is NO you cannot do this in a simple, generic-SQL query.

The reason is that generic SQL does not deal with metadata, and the questions you are asking are based on metadata.

IF you name a specific SQL engine, you certainly might find that some metadata extensions have been built into the vendor's SQL. But each case is different. So for the generic question, No - you can't do what you asked.

Ok, this is accettable, with Ansi Sql is not possible
With Microsoft Sql Server did you think is possible?

 

[amorosik]

I'm going to drop out, this is a complete waste of everyone's time.

The initial post implies a single table in Access
Around post 35 - the OP says he never uses the JET/ACE databases with the implication it is actually sql server. Leaving everyone barking up the wrong tree for 33 posts.

and the code he/she/they/them quotes just lists all the fields in a table, not those without any values which is the requirement of the original post

"..initial post implies a single table in Access.."
No, only you imply

"..databases with the implication it is actually sql server.."
No, only you imply

"..the code he/she/they/them quotes just lists all the fields in a table.."
Exactly of wich code you are writing?

You assume or implied a little too many things that aren't implied
And all the objections you are highlighting are the consequence of the fact that you take some things for granted
And then you grumble because they are not as you assumed them
The logic would be, if one aspect of the question is not clear (And this may be that I didn't describe the problem well), to ask for explanations
Let's leave barking to dogs, not humans or programmers

 

[The_Doc_Man]

Ok, this is accettable, with Ansi Sql is not possible
With Microsoft Sql Server did you think is possible?

Not an expert on SQL Server. However, the orientation of SQL and the principles of Interrogatory Logic used by SQL suggest that it might be extremely difficult for ANY version of SQL unless you allow for some VBA or other code that falls outside of "pure" SQL syntax. In essence, the way the question was asked is skirting the issues of the Goedel Completeness Theorem. In order for SQL to be correct as an interrogatory language (which means it always gives a definitive answer), it cannot be complete. Being not complete means - among other things - that it should not be able to make self-references or self-examination. Stated another way, the ability to see one's own metadata runs the risk of that old conundrum: "This statement is false." A self-referential statement can lead to such self-contradiction. Which is why SQL doesn't normally have that ability. Vendor extensions that allow SQL self-examination have to be limited because otherwise they would run into the perils of non-finite automata. (I.e. either infinite loops or indeterminate results leading to errors.)

 

[cheekybuddha]

You assume or implied a little too many things that aren't implied
And all the objections you are highlighting are the consequence of the fact that you take some things for granted
And then you grumble because they are not as you assumed them
The logic would be, if one aspect of the question is not clear (And this may be that I didn't describe the problem well), to ask for explanations
Let's leave barking to dogs, not humans or programmers

You post in a website called 'Access World Forums' with a url 'access-programmers.co.uk' on a forum board called 'Microsoft Access Discussion -> Queries' and then complain we might assume your question is about queries in MS Access!

'Effin' troll.

If you continue to play your games of pin the donkey you may soon find that no one bothers to answer any of your questions.

 

[amorosik]

You post in a website called 'Access World Forums' with a url 'access-programmers.co.uk' on a forum board called 'Microsoft Access Discussion -> Queries' and then complain we might assume your question is about queries in MS Access!

'Effin' troll.

If you continue to play your games of pin the donkey you may soon find that no one bothers to answer any of your questions.

"..we might assume your question is about queries in MS Access.."
My question IS about Access queries

This 'Effin' trolls I don't quite understand what it means
I think it's an offense? If so, is it tolerated by the administrators of this forum?

"..play your games .."
I'm really not playing, I asked a question, and if anyone can answer I'll be grateful

If the forum users don't know what to answer, or don't want to help me, that's fine too
The only thing that's not good is that there's someone who thinks they can call others a troll
That's the only thing that's not good
But education and the ability to behave properly with other people is a quality not available to everyone

 

[The_Doc_Man]

OK, guys... As a moderator, I have the right/duty to step in and ask politely for a bit of de-escalation.

Anyone who does not feel they need to participate in this discussion is not obligated to do so. We are ALL volunteers and have the right to pick-and-choose how we spend our forum time. Rather than allow this to develop into a "personalities" issue, anyone who is upset can just bow out gracefully. I request that if you no longer wish to participate in this question, that you just say "I have nothing else to contribute" and leave it at that. I believe @amorosik has detected that some folks are upset, so would not be surprised by such departure.

 

[NauticalGent]

you may soon find that no one bothers to answer any of your questions.

 

[ebs17]

My question IS about Access queries

Access "speaks" generically Jet-SQL (SQL-89), no matter where data is stored. If you want to communicate directly with other DBMS, you would have to explicitly use their SQL dialect. But that was not asked.

If the forum users don't know what to answer

You got the answer, you just don't understand it. Whether you can't or don't want to, I don't know.

Query that returns the column names that have never been used

Hence the 'lot of code'! I thought the OP wanted to do one table but rereading his post, I see that he wants to do some unspecified number. Once you want to do multiples, you're stuck with code but I would still create one query per table rather than one query per field per table. Using Max()...

www.access-programmers.co.uk

I recommend a little more humility and a little less of the over-confidence.

 

[Pat Hartman]

But since the work is essentially done via vba by scrolling through the fields and composing the query, I don't consider it useful for the purposes of the current request

The application, which you didn't bother to try, can examine tables local to or linked to ANY Access FE. It doesn't matter what RDBMS houses the BE or even how many BE's the FE links to or whether they are mixed RDBMS' or just one. The app will work with Jet/ACE, SQL Server, Oracle, DB2, Sybase, Postgre, etc!!!!!!!!!! If your Access app can link to the RDBMS and work with it, then pick the FE from the dialog and that is ALL you need to do.

The output is a table with ONE row per table in the analyzed database that has ANY columns where all the rows are empty. So, if the BE has 100 tables and only two of them have any columns where any column has no value on any row, then the output will be 2 rows with as many columns (up to 20 - which you can easily expand with NO CODE CHANGES (OK, you have to change the number 20 to the new limit or remove the validation))

The only reason I bothered with creating this was because I thought it to be a useful utility but not the way anyone had presented it to date. Having to read through dozens of lines of spaced out text in the debug window was not a useful way to look at the output for anything more than a couple of tables. My altered solution can display results for as many tables as your app is linking to in a format that is concise and easy to understand and use.

You're welcome.

 

[KitaYama]

Anyone who does not feel they need to participate in this discussion is not obligated to do so. We are ALL volunteers and have the right to pick-and-choose how we spend our forum time. Rather than allow this to develop into a "personalities" issue, anyone who is upset can just bow out gracefully.

Doc, I'm less than a year and half since I joined and never doubted your judgment.
But it seems that everything has a first time.

You can see this strugle in every post the OP has started, yet your warning goes only to those who state they're going to drop out?

 

[The_Doc_Man]

@KitaYama - my comment would also apply to the OP if he decides he's had enough of this thread. After all, he has struggled to get an answer as much as we have struggled to give one. But this struggle doesn't really matter. Though it may be causative in the little blow-up we experienced, it is not the issue that counts. Yes, this thread has been like undergoing exploratory surgery with limited anesthetic insofar as there may be a language barrier and there surely is an experience barrier. Yes, getting a clear picture of what is required has been difficult. However, "clarity of statement" is not a rule, it is merely a fervent hope.

The rule that was being violated is the requirement to treat other members with respect. When vulgarities are directed at another member, it is time to cool off, and that is exactly what I requested. If things cool down a bit, I don't need to impose a penalty. I would prefer to not have to do that.

You seem to think I should penalize the OP, but I think failure to communicate such that you can't get your question answered is its own form of punishment. Building a reputation for being difficult is another penalty of its own kind. It is also a self-correcting issue - with just a little bit of patience.

 

[amorosik]

Access "speaks" generically Jet-SQL (SQL-89), no matter where data is stored. If you want to communicate directly with other DBMS, you would have to explicitly use their SQL dialect. But that was not asked.


You got the answer, you just don't understand it. Whether you can't or don't want to, I don't know.

Query that returns the column names that have never been used

Hence the 'lot of code'! I thought the OP wanted to do one table but rereading his post, I see that he wants to do some unspecified number. Once you want to do multiples, you're stuck with code but I would still create one query per table rather than one query per field per table. Using Max()...

www.access-programmers.co.uk

I recommend a little more humility and a little less of the over-confidence.

"..But that was not asked.."
Access also allows you to send queries directly to the db server to which it is connected, and to read the returned results exactly like all other queries, there is no reason to treat them differently, if you re-read the initial request I never restricted the possibility only to "Access Jet-Sql (Sql89)" queries, and if I don't ask, why do you assume it's a given condition?

"..you just don't understand it.."
You're pointing to post #32 where you say it can't be done
Maybe you haven't read post #33 or #35 where I show you how it's possible to do it instead

"..I recommend a little more humility and.."
Thank you very much for the wish
Will you also send it to those who took the liberty of insulting me?