Results 1 to 11 of 11

Thread: Update Query

  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: Update Query

    I have a contact list that contains 19 fields for data, so far I have approximately 500 names added on the list. I get updated contact list from clients and want to create an update query to see of any changes have been made month to month. First I will create a form to copy and past all the new data into a table, then I will need to run a query to cross reference the fields. This is where I am stuck. I want the the new data too look at all the fields i already have and only update if something is new. I am not sure on how to write a query to check a field and if the data is the same, then go to the next field or if the field is different then update and go to the next field. The main identifier will be the Customer name.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    From a logic view point - why not update them all. If the fields you are updating are the same as they were then this remains the same. If they are different they will then be changed.

  3. #3
    Join Date
    Feb 2008
    Posts
    27
    Ya i can do that, but my contact list is summed up of approx 4 different lists that I get, so i would need to write a query that says if name=name, then update or if new contact name is not on old one, add. I am stuck on how to write something like that.

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Is this an append or an update query.

    So could the contact be a new record? and or an existing contact that needs fields changed against that record?

  5. #5
    Join Date
    Feb 2008
    Posts
    27
    I guess I would have to run both. If the name is already in the system then it would be an update, but if the name is not there then it would need to be an append.

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Make sure the table that holds your contacts will not accept duplicates.

    Run the append query first. Then the update. You will get some error messages coming up that may confuse users. So I would turn the error messages off before you run the append query and then turn it on afterwards.

    DoCmd.SetWarnings False
    DoCmd,RunQry "qryName"
    DoCmd.SetWarnings True

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make sure the table that holds your contacts will not accept duplicates.
    Duplicate WHATs????

    The problem is that without something unique to identify each record, how are you going to cope with two John Smith's? Or if Sue Jones gets married and changes to Sue Fauntleroy? Unless you use a PKF and the four sources use the same PKF, then identifying who is new and who is not won't be as easy as you might think.
    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

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    How about an additional field that has a contact no/ref. This uses the first 3 characters of the name followed by a number.

    John Smith SMI001 - if another John Smith comes along we can check if he is the same or not. If the same any updates are then made. If he is another John Smith we give him Contact no/ref SMI002.

    Or
    It may be possible to use addresses as well
    John Smith
    Australia

    John Smith
    UK

    Would be ok and not come up as a dupe.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    According to post 3, the data is being collected from external sources. This is the data that is the problem.

    If you are going to add an additional field, it should be just a unique key, an autonumber for example. If those external data sources can comply with using the autonumber to identify the data, then it's possible. Otherwise it's going to be problematic at best.

    we can check if he is the same or not
    How? John Smith could have moved (changed address) which changes his phone number and just about everything else. DOB might be able to be used, but that isn't perfect either, and we don't even know if that field is present.

    The DOB could be one of the updated fields... perhaps it was originally recorded incorrectly... then what?
    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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Let me get this straight; we want an "UPSERT" (that's an UPDATE if exist, INSERT if not) query? Or a SELET to compare two differnt resultsets?
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2008
    Posts
    27
    Exactly "UPDATE if exist, INSERT if not" - All of the data can change within the fields except for the name, but I do not want to have duplicate names either. I currently have ID's attached to each name, so that is unique and will stay the same with an update, but a new ID will need to be added if there is an addition to the table. The ID can be automatic though, there is no specific criteria.

Posting Permissions

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