Extract list on Fields with Data

D

Daniel

Hello,

I have been given a table with several hundred fields. is there a simple
way to construct a listing of fields which have information in them. I
trying to find a way of identifying which fields are used vs. those that are
not to try and simplify the db design.

Thanks,

Daniel P
 
J

Jerry Whittle

Create a new query based on the table.
Bring down all the fields in the QBE grid.
Go up to View and select Totals.
Change all the "Group By" to "Count".
- As you have a lot of fields, it might be faster to do a find and replace
on the
SQL statement.
Run the query.

That should give you an idea of what fields aren't used or used much. There
are a few things like empty strings and fields with just spaces that might
mess things up a little. Nulls should be fine though.

BTW: Access can only have a maximum of 255 fields.
 
J

John W. Vinson

Hello,

I have been given a table with several hundred fields. is there a simple
way to construct a listing of fields which have information in them. I
trying to find a way of identifying which fields are used vs. those that are
not to try and simplify the db design.
Click to expand...

A Query with a criterion of

IS NOT NULL

will find all records which have data in the field. If the query returns no
records than the field has no data.

Access tables are limited to 255 fields, so I am curious how you can have a
table with "several hundred". Is this an Access table? a text file? an
external file such as a dBase database? or what?

John W. Vinson [MVP]
 
A

Aaron Kempf

CORRECTION!

Access Tables are NOT limited to 255 fields

Access MDB Tables are limited to 255 fields.
Access Data Projects are NOT limited in this fashion


this is the single reason that I moved to ADP 10 years ago
 
J

Jerry Whittle

You never figured out Relational Database Management Systems or Normalization?
 
T

Tony Toews [MVP]

Daniel said:
I have been given a table with several hundred fields. is there a simple
way to construct a listing of fields which have information in them. I
trying to find a way of identifying which fields are used vs. those that are
not to try and simplify the db design.
Click to expand...

Open the table in datasheet view. Click on a column heading. Click
on the Z-A/Sort Descending button. (Or right click in a column and
choose Sort Descending.) All null entries will go to the bottom.
Delete fields as appropriate.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
J

James

hi, i'am james and i would like ask on how to make a listing out of several
datas...
i found out that "=vlookup()" is used to lookup single data from several
datas but i wanted to automatically show all the datas that is match with
what i'm looking for.

For example,
House kWHr
1 100
2 10
3 100
4 59
5 100
6 75

What is the command or formula used to show the list of houses with the 100
kWHr that looks this:

House
1
3
6
 
J

James

hi, i'am james and i would like ask on how to make a listing out of several
datas...
i found out that "=vlookup()" is used to lookup single data from several
datas but i wanted to automatically show all the datas that is match with
what i'm looking for.

For example,
House kWHr
1 100
2 10
3 100
4 59
5 100
6 75

What is the command or formula used to show the list of houses with the 100
kWHr that looks this:

House
1
3
6
 
J

james

hi, i'am james and i would like ask on how to make a listing out of several
datas...
i found out that "=vlookup()" is used to lookup single data from several
datas but i wanted to automatically show all the datas that is match with
what i'm looking for.

For example,
House kWHr
1 100
2 10
3 100
4 59
5 100
6 75

What is the command or formula used to show the list of houses with the 100
kWHr that looks this:

House
1
3
6
 
L

Larry Linson

VLOOKUP is a Spreadsheet command. This newsgroup is dedicated to discussion,
questions, and answers about Microsoft Access Database software. You will
stand a better chance of getting the answer you need if you go back to the
online user interface that misled you here, scroll down and repost your
question in a Microsoft Excel newsgroup.

Larry Linson
Microsoft Access MVP
 
J

Jon Ament

James said:
hi, i'am james and i would like ask on how to make a listing out of several
datas...
i found out that "=vlookup()" is used to lookup single data from several
datas but i wanted to automatically show all the datas that is match with
what i'm looking for.

For example,
House kWHr
1 100
2 10
3 100
4 59
5 100
6 75

What is the command or formula used to show the list of houses with the 100
kWHr that looks this:

House
1
3
6
Click to expand...
 
Top