Delete Columns with 0 value

[musicloverlch]

I have been given a database with 2,944 tables in it. Each table has
35 rows and columns labeled Dur, 20, 21, 22, 23, 24, 25, 26 ..... 64,
65. They contain insurance rates by age and band.

Currently all the tables are the same length and width. I need to go
through all 2,944 and delete out any colmn with a zero value. For
some that might be columns 64 and 65, for some 50 through 65, or any
other possible combination.

If I have to open 2,944 tables individually, I'll cry. Is there a way
to cycle through the tables and look for columns with a 0 value and
delete them with VB?

Thanks in advance,
Laura

[Tom van Stiphout]

Re: Delete Columns with 0 value

On Sat, 10 May 2008 08:28:51 -0700 (PDT), musicloverlch
<lhowey@gmail.c omwrote:

That's a HORRIBLE database design, but that aside. Off the cuff you
can do something like this:
public sub FixIt()
dim db as dao.database
dim td as dao.tabledef
dim fld as dao.field
set db=currentdb
for each td in db.tabledefs
for each fld in td.fields
DeleteIfNeeded td, fld
next fld
next td
end sub

private sub DeleteIfNeeded( td as tabledef, fld as field)
if dcount(fld.name , td.name, fld.name & "<>0") 0 then
td.fields.delet e fld.name
end if
end sub

>I have been given a database with 2,944 tables in it. Each table has
>35 rows and columns labeled Dur, 20, 21, 22, 23, 24, 25, 26 ..... 64,
>65. They contain insurance rates by age and band.
>
>Currently all the tables are the same length and width. I need to go
>through all 2,944 and delete out any colmn with a zero value. For
>some that might be columns 64 and 65, for some 50 through 65, or any
>other possible combination.
>
>If I have to open 2,944 tables individually, I'll cry. Is there a way
>to cycle through the tables and look for columns with a 0 value and
>delete them with VB?
>
>Thanks in advance,
>Laura

[rkc]

Re: Delete Columns with 0 value

musicloverlch wrote:

I have been given a database with 2,944 tables in it. Each table has
35 rows and columns labeled Dur, 20, 21, 22, 23, 24, 25, 26 ..... 64,
65. They contain insurance rates by age and band.
>
Currently all the tables are the same length and width. I need to go
through all 2,944 and delete out any colmn with a zero value. For
some that might be columns 64 and 65, for some 50 through 65, or any
other possible combination.
>
If I have to open 2,944 tables individually, I'll cry. Is there a way
to cycle through the tables and look for columns with a 0 value and
delete them with VB?

You should cry anyway because what you have been given is not a
database, it's a big pile of dung. Your current task is apparently
to pile it higher.

From the tiny bit of information in your post and what little I know
about the insurance business I would say you should be looking at a
single table with 4 columns to hold the whole pile.

InsuranceRates( Age, Rate, PercentAbove, PercentBelow)

Getting the information from your 2944 tables with 35 rows and a
ridiculous number of columns into that one table could in fact be scripted.

That would make your life much easier and probably triple your currently
available thumb twiddling time.

The answer to your original question is yes, but I wouldn't want to show
you how because it's a waste of time.

[Tom van Stiphout]

Re: Delete Columns with 0 value

On Sat, 10 May 2008 12:40:10 -0400, rkc <rkc@rkcny.yabb a.dabba.do.com>
wrote:

I fully agree, but figured that if they got to 2944 without realizing
this, we might have to chalk them up in the Lost Cause column.

-Tom.

<clip>

>
>You should cry anyway because what you have been given is not a
>database, it's a big pile of dung. Your current task is apparently
>to pile it higher.
>
From the tiny bit of information in your post and what little I know
>about the insurance business I would say you should be looking at a
>single table with 4 columns to hold the whole pile.
>
>InsuranceRates (Age, Rate, PercentAbove, PercentBelow)
>
>Getting the information from your 2944 tables with 35 rows and a
>ridiculous number of columns into that one table could in fact be scripted.
>
>That would make your life much easier and probably triple your currently
>available thumb twiddling time.
>
>The answer to your original question is yes, but I wouldn't want to show
>you how because it's a waste of time.
>
>
>
>
>
>