Gary Kjos
garykjos at hotmail.com
Thu Jul 31 11:55:19 CDT 2003
High Fives all around !!!!
Great to solve a problem that some may think unsolvable. And if you can
stick it to a smarty pants at the same time - all the better.
Good job Alan.
Gary Kjos
garykjos at hotmail.com
>From: "Lawhon, Alan C Contractor/Morgan Research" <alan.lawhon at us.army.mil>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Thanks Helen! (Thanks Susan! ...)
>Date: Thu, 31 Jul 2003 11:34:45 -0500
>
>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.
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus