how to filter fields?

[Guest]

I have a table with 60 fields. How can I eliminate the fields that contain
null value in all records? (no point to keep these fields any more).

 

[Guest]

Hi Cynthia,

Open the table in design view, and delete the appropriate fields. Make sure
that you retain a good backup copy of your database, before doing this, just
in case you accidently delete the wrong field.

You may need to alter queries, forms, reports, macros, modules, and data
access pages, if any of these objects reference a field that you want to
delete. If you are not sure whether a given field is needed, you could always
turn Name Autocorrect off, rename the field, and then start testing your
database to see if any errors show up in queries, forms, reports, etc. There
are advanced software tools available that are helpful in locating where in a
database that a given field is used:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora: http://www3.bc.sympatico.ca/starthere/findandreplace/ (Free)
Total Access Analyzer: http://www.fmsinc.com/products/analyzer/index.html

I use SpeedFerret and Total Access Analyzer, myself. I don't have any
experience using the Find & Replace utility or the UCora utility (I believe
you can use these to Find all occurances). In addition, you can do the
following as a way of producing a searchable document to find references to
various objects, including fields:

Use the build-in database documenter in Access, by clicking on Tools >
Analyzer > Documenter. Select the form(s) and report(s) in question. Click on
the Options... button. Make sure to place a check in the option to include
Properties and Code under the section that reads "Include for Form" (or
"Include for "Report), and Names and Properties under the section that reads
"Include for Sections and Controls". Generate the documentation. This may
take some time, and will produce a fairly verbose report. Do not close the
report when it is first displayed. Instead, use File > Export. Choose Rich
Text format (*.rtf) as the output format. Generate a new .rtf document. You
can open this document in Microsoft Word or WordPad, and use the search
feature (F5) to search for the name of the field in question.

Hope this helps


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Thanks a lot Wickerath.

The problem is, I am importing the table. So, each time the No. of fields or
the name of fields are different. I want to eliminate those dummy fields that
do not contain data....is there anyway that these fields could be eliminated
automatically? (I couldn't delete the fields manually all the time).

 

[Guest]

Hi Cynthia,

Rather than import a new table each time, I would think that you would be
better off to link to the new tables. Then, run an append query to append
just the fields of interest to a more permanent table. When you are finished,
drop the link. That way, you don't need to worry about deleting a bunch of
fields.

Is there anything consistent each time you receive a new table? For example,
are the fields that contain data of interest to you named the same each time?
This sounds like a rather unique situation. I'm sure that some VBA code could
be written to determine how many fields are present each time, and to cycle
through each field identifying those fields in a listing that contained no
data. Can you provide any additional information, such as examples of field
names that do and do not contain data? Would these be dates as field names?
For example, one field for each day, like 6_25, 6_26, 6_27, 6_28, 6_29, etc.?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________