Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: Extracting partial data from access

    I need to be able to extract partial data from one field and have it inserted in another field. See below for details

    Table: Customer Table
    Field; Customer Name

    Currently the customer name field includes both first and last name. I want to update an Field titled Customer Id by having it copy the first 10 characters of the Customer name field. Example: Customer Name= John Smithinhiemer. I want it to update the customer ID feild to be John Smith.

    I would like to use an update query to do so.

    How can I do this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have no idea why you would ever want to do this?!

    I mean, if you have a John Smith and a John Smithson, then they will both have the same "customer id", which don't make a reet lot of sense to me!
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Posts
    2

    Do you have an answer?

    I agree that the example I gave you doesn't make much sense. I used this as an example only to simplify explaining what I am trying to do. Do you have a soultion for me?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Checked out the Left() function?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Let this be a lesson to you - don't try and paraphrase the problem because it just leads to [possibly] unnecessary questions.

    However, I would still not advise you to do this - it sounds like you're violating 3NF, which is never a good thing.

    Explain your problem correctly in full including your table definition and perhaps a decent solution can be advised.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    classically in accounting programs (like sage and others) you create this sort of reference using the surname and a number

    eg: EDE00001, SMITH003 and so on.

    you can get a flavour of that by doing a directory search in a dos command window where directories like "C:\program files" becomes "C:\progra~1"

    persoanlly I'd suggest that you make the PK an autonumber column, and ditch the idea of doing some form of string manipulation. however if you must then consider doing your update using a mix of left() to trim to the relevant size and I'd also consider suign replace (to replace or strip out unwanted characters such as spaces.

    if you do go down that route I think you will then need to devise some mechanism to resolve ties (eg all the John Smith's, David Jones's and Jock McTavish's out there) and that is going to require adding something else to make it unique. adding a number works, but you will need to create your own function to do that.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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