[AccessD] Access vs. Excel - Oy!

Jim Lawrence accessd at shaw.ca
Sat May 19 13:09:38 CDT 2007


Steve:

That is a very interesting story. It sounds like a brew mixed with
incompetence, inexperience, laziness driven by a fear of change and losing
control all spiced with a bit of larceny. You have the start to great
article in the editorial section of the local newspaper.

If I had a suspicious nature I would visualize how easy it would be to add,
remove or change to such a list as who has the time or ability to
cross-reference such a morass of data.    

Jim     

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Saturday, May 19, 2007 4:16 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Access vs. Excel - Oy!

Dear Group,

If any additional evidence were needed by any of you Access heads that
our favorite PC database is furlongs, miles, leagues, light years,
parsecs ahead of Excel as a database tool, I only need relate what
happened with the Neenah voter list I bought on Wednesday.

The City Clerk had downloaded 4 years worth of election history for my
city into four separate Excel worksheets.  I had helped her extract
data from Wisconsin's voter registration system some time back, but
things have changed a bit.  The state IT guys have put together a
certain format for voter history that sucketh big time.

There are two rows for each voter in the history.  The second row has
the city, state, zip and that's all...in one field, too!  Isn't that
handy?  Why didn't I ever think to put all three in one field before?

But much worse: each spreadsheet contains up to four elections: spring
primary, spring general, fall primary, and fall general election.
It's laid out like a COBOL report: the name of the election appears
next to the first voter's name...but after that first record the label
does NOT appear next the the names of ANY other voters who voted in
that election!  All those fields are blank until the block of voters'
names for the next election.

Similarly, the township name is listed next to the voter's name; but
any other voters in the list living in that same township do NOT show
the township name next to their names!

Not knowing this in advance, I went on Wednesday to the City Clerk's
office.  She wanted to show me one of the spreadsheets, so she opened
the File | Open dialog box.  I saw the names of the four spreadsheets
there and I asked her how big the largest one was -- meaning size in
KB.  She thought I meant in number of spreadsheet rows, so she opened
the list for 2004.

I know that her machine, like so many PCs in offices around the
country, is a bit on the under-powered side.  But I wasn't prepared to
wait for 12 minutes!  When the sheet finally opened I saw the layout I
described above and I saw that there were 45,000+ rows and 7 columns.
No problem, I thought; I'll just have her zip them up and I'll deal
with them at home after she emails them to me.

I unzipped the file on my home system and I thought I'd see what the
largest sheet looked like in Excel.  The sheet is 8.5MB or so.  I
revved up Excel 2003 and opened that sheet...and waited 10 long
minutes.  During that time I opened Task Manager and saw that Excel
was consuming 98% of the CPU cycles, but it had only 160 KB or so of
file I/O and was using only 30 MB of RAM.

Once it was open I tried something simple like copying the election
code from the first row to every other row that had a blank election
code.  Geez!  That took a few minutes just for that!  I saved the file
with a new name and exited Excel as a bad business.

I learned my lesson.  I opened Access 2003 and imported the original
spreadsheet.  Within three seconds I saw the first screen of the
Import process where I could tell Access whether the first row of the
sheet contains the field names.  I went through all the screens of the
dialog, allowing Access to assign a key, and in just under 10 seconds
all 45,000+ rows were sucked into a new table!  Halle-flippin'-lujah!

Unbelievable.  The clerk had spent a considerable amount of time
downloading those four sheets from the state database.  Excel is the
most convenient of the choices for data export; but the IT dept. in
charge of the voter list must not want it to be too easy for people
that are purchasing the voter lists to actually USE the data.  A few
months ago when I ran for re-election here in Neenah, I was able to
get a voter history that had a separate group of columns in the
spreadsheet for each election I was interested in.  So if a voter
participated in only two of the six election columns, there would be
the date of the election in the appropriate column.  Also each voters
demographic information was in a single row.

It's a good thing that I had curried a bit of favor with the Clerk.  I
had sat with her in December helping her decipher the options in the
on-line voter registration database.  This was back when she actually
had a choice as to which columns could be selected for her downloads.

I said that the lists are purchased.  This is all made VERY pertinent
since I only paid $15 for these four spreadsheets.  The NEW fees went
into effect on Thursday:  $25 for the setup charge + $5 per thousand
names.  There was a total of almost 49,000 names.  You do the math;
I'm too appalled.

Regards,

Steve Erbach
Neenah, WI
http://TheTownCrank.blogspot.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list