Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Unanswered: Edit/Alter or Delete part of a text string to create a query

    Hi,

    I have two tables in an MS access 2003 database. These tables are populated from downloads and imported via .txt files.

    Table1 has a field name: Ref_No and a sample of the data would be a text string e.g. E 123456789 but the length of this string could be longer.

    Table2 has a field name: Event and a sample of the data would be 123456789 or in this case the download does not include the letter 'E followed by a space' as a prefix.

    What I would like to do is create a query that finds results in table1 that are matched in table2. So I would like to either find out the best way to delete the prefix E space from table1 or alternatively edit table2 by adding an 'E space' in front of the text string in the field.

    I thought if I could get the text strings to match each other I could then find results I am looking for to show me which people from table1 also had involvements in table2.

    The existing records in the database are in the thousands, so I was also wondering if it is possible to run code that changes the field after it is imported in one of the tables so that the text then matches the other imported data.

    Regards,

    Tim

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its easy enough to change, run an update query after the event, or run the import as part of a VBA process (gives better error handling and recovery options)

    UPDATE mytable SET mycolumn = MID(mycolumn,3) WHERE ISNUMERIC (LEFT(mycolumn,2)) = FALSE

    ..the mid function returns characters 3.....n
    ..the left function returns the first 2 characters
    ..the isnumeric tests to see if those two characters are numeric, and if not they are included in the scope of the query


    you could do a join in a(ny) query, however it may be a performance slug

    where mytable1.mycolumn = mid(mytable2.mycolumn,2)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Thanks very much, I appreciate the quick reply.

Posting Permissions

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