[AccessD] Thanks Helen! (Thanks Susan! ...)

MAJOR BOOK (and AUTHOR) ENDORSEMENT:


We had a problem come up here at work which involved importing raw (unformatted)
Excel spreadsheet data into Access.  Importing the data was easy - a piece of
cake.  What was not so easy was the fact that one of the [text] fields in the
spreadsheet contained repeating groups; a violation of First Normal Form.  What
our customer wanted was a breakdown (parsing) of the data so that there were no
repeating groups in this particular field.  (Our customer wanted the data
records to have a single discreet "SHOP" value for each record.)  Basically,
this meant creating a child table, setting up a one-to-many relationship from
parent to child, and then parsing out all the "SHOP" records and writing them to
the child table - the kind of thing we've all done a million times.  However,
due to the fact that the "SHOP" data was contained in a 255 character field,
(some of the records contained 30-40 five-character "SHOP" values in the SHOP
field), this was not such an easy task.

Last Friday afternoon our customer was discussing this problem with the Senior
Systems Analyst.  The Senior Systems Analyst, (who thinks he's the greatest
programmer in the world - and constantly reminds people of this "fact"), decided
to dump this turkey on me.  He proceeded to announce (quite vocally) that it
would take Alan "three weeks" to solve the problem.  I took this pronouncement
as something of an insult!  I had some ideas on how to attack the problem, but
since I am (not yet) an Access expert; I turned to the books.

I recall our dear friend, Susan Harkins, mentioning that she had colloborated
with Helen Feddema on the book "Microsoft Access 2002: Inside Out" and had
written some of the chapters.  On a hunch, I started looking thru Helen and
Susan's book and discovered a section of the book, (i.e. "Using Queries to
Reformat Imported Mainframe Data"), in Chapter 16.  This turned out to be just
what the doctor ordered!  I didn't solve my particular problem in exactly the
same manner as Helen and Susan suggest, (I preferred writing a Visual Basic
subroutine to do the actual SHOP field parsing), but the ideas and techniques
presented in Chapter 16 got me going in the right direction.

"OK, cut to the chase, Alan!"  I demonstrated the nice clean (fully parsed) data
to our customer this morning.  And, much to the surprise of the Senior Systems
Analyst, it didn't take me three weeks!  (I may be old, but I'm not over the
hill - not yet anyway!)

Helen and Susan's book cost over $40.00, but it was money well spent.  Some of
the higher-up bosses remembered the Senior System Analyst's "three weeks"
comment.  (It turns out they had been wanting this problem solved for the last
TWO MONTHS but he kept putting them off ...)  It took me three days to solve
this "three week" problem!

So now I'm a "hero" - or at least I ain't quite as dumb as some people think ...
Anyway, I want to extend a heartfelt "Thanks!" to Susan (and Helen Feddema) for
including the material on how to "clean up" raw imported mainframe data in
Chapter 16.  It was a Godsend.

Thanks Susan (and thanks Helen!) for helping me look good.


Alan C. ("Super Programmer") Lawhon   :-)))


       

      


 (0.) Thursday, July 31st:  Send Susan Harkins a very complimentary email
       tonight thanking her (and Helen Feddema) for the information on
       "Using Queries to Reformat Imported Mainframe Data" in Chapter 16
       of Helen's "Microsoft Access 2002: Inside Out" book.  I didn't
       parse and "clean up" a large download of Excel spreadsheet data
       quite the way that that you and Helen recommended it should be done,
       but there were enough "good ideas" in Chapter 16 to get me going
       in the right direction.  Anyway, I got the data "cleaned up" in
       about a day's time.  (The "Senior Systems Analyst" around here,
       an older guy who thinks he's just about the greatest programmer
       in the world, was telling a bunch of people Friday that it would
       take Alan "about three weeks" to get the spreadsheet data parsed.
       I showed him the final output a little while ago.  He was a bit
       surprised.  (I may be old, but I'm not over the hill yet!)

       Anyway, "Thanks!" (very much) to you and Helen for helping me
       look good.