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