Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace Text In Table (A2k)

    Not sure if this is possible , but here go's.

    I have a table containing thousands of records.
    One of the fields [INS_NME] contains names of insurers.

    The names can be slightly different, which makes a grouped report fragmented.

    The insurer names can be:

    Allied Insurance
    Allied Insurance Ltd
    Allied Insurance PLC Ltd etc etc

    Proceeding alphabetically through to Z

    Zurich Insurance Company
    Zurich Insurance Ltd etc etc

    I can use Find and Replace and search for :

    Zurich* and replace with Zurich Municipal Insurance, which will select anything starting with Zurich.

    This obviously takes alot of time and was wondering if anyone has a module of some description I can use to automate this.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Replace Text In Table (A2k)

    Is there some reason you actually keep the text in that field instead of a numeric insurerID? That would allow you to make use an Insurers table for the definitive version of the insurer's name and would be the simplest way to eliminate the problem. The most automated way of updating it would be to have a table of InsurerAliases, which could be populated with distinct insurer names from the text file and would include a field for the InsurerID. Once you populate that table, you can join the main table to the InsurerAliases table on the name and update the original table automatically..
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Alas Charlotte, if things were so easy, but in the real world we have to improve things manually.

    The table is imported from an ODBC source with records going back over 10 yrs or so.
    During that time, Insurers have merged, branched etc, forcing us to edit their names over the years.

    If you can think of a solution, I would be very greatful.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Dave,

    Is this a one time operation ? Then I should use update queries.
    If you have to do this on regular basis, I could think of a list box with multi select where you select all the names you want to change to one other and build the update sql statement to do the changes.
    If you want, attach the table (with only the insurance name field) and I'll try to build a sample.
    Francois

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Francois, that's very kind of you.

    Attatched as you have requested.
    Unfortunately, the make Table query(s) have to be run once a month via ODBC.
    This way I never inter act with the source database and populate my Access database for reporting only.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Dave,

    Attached, the sample db.
    Open form1 and select the names you want to change in the left listbox. In the right listbox, select one name you want to use to replace the others and click on the Update button.
    Francois

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Well I never !!

    Just Awesome ??

    Thankyou Francois, absolutely brilliant !!!!!!!!!!!!!!!!!!

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Dave,

    This is another approach of your text replacement problem.
    What I don't like in the previous db is the fact that every month you have to loop through the 400 and more lines change to the exact replacement.
    In the attached db I create a table tblReplacement with two fields, Client and txtReplaceName. In this table I store all the existing names and the replacements for them.
    To do this I run an append query in the on open event of form2. Up to you to enter the wanted replacement names in the right column. This is, in this demo, not limit to the list from the existing names.
    When this is done, click on the upgrade client names button to update the table. Names with nothing in the txtReplaceName will not been modified.
    Next month, you'll have only to enter the new name replacements as the append query will only add names that don't exist in the tblReplacement table. If you entered a txtReplaceName for each name, the previous month, you'll only have to care about the one with a empty txtReplaceName. If you have a lot of names that are the same as the ones of the previous months, this will save you a lot of input.
    Up to you to decide what's the best method for you.
    Let me know what you think about it.
    Francois

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Francois

    Thank you for the extended attention you have given to this post.

    I can't test it right now, the machine I'm on hasn't got office on it.
    At the moment, I've just done a re-install for my Father in Law and just testing the Internet connection.
    I'll put it on disk and take it home though.

    I'm away for a couple of weeks so if I don't reply immediately, I shall when I'm back.

    Thanks again Francois, your help over the last couple of weeks has been amazing.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Francois.

    I'm just getting around to looking into the update you sent me.
    For clarification, I've had to modify some things, (tables queries) so here is the current state of the db.

    My report groups on "Client" from Table "tblEST"
    The whole report has one query "qryClientTransactions"

    Which queries from your example are required specifically for me to make modifications. ?

    Regards
    Dave

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace Text In Table (A2k)

    Francois is not online at the moment. You should import everything from Francois' database into your own database. You can delete the records in tblInsNames, they will be created anew the first time you open Form2.

    The design of qryInsName assumes that the name of the insurer field in tblEST is INS_NME. If it is named differently in your current version, change the expression

    Client: IIf(Len(Trim([INS_NME]))=0 Or IsNull([INS_NME]),"Un-Specified Name",[INS_NME])

    accordingly. You shouldn't have to modify anything else. Open Form2 to see a list of insurer names in tblEST, then assign the name to be used to each. The first time it will be a lot of work, in the future you will only have to do this for insurer names that have been added in the meantime.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Thanks Hans

    Just needed a push in the right direction.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    Dave,

    Sorry, I couldn't be online this afternoon.
    I suppose you can go ahead with Hans's answer. If not, post back.
    Francois

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Text In Table (A2k)

    No problem Francois, I sorted it out and is working fine.

    A couple of tweaks solved a couple of issues, mainly because I re-wrote alot of stuff and gave different names to fields and tables.

    Thanks for the demo which gave me a great start.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •