Results 1 to 5 of 5

Thread: sql problem

  1. #1
    Join Date
    Jun 2002
    Posts
    72

    Unanswered: sql problem

    Is there an easy way using sql to find all records in a table where the value in the same field is identical and to update the record at the same time?

    For example I have a table which has 2 fields:

    Table = invoice
    Fields = Description & Cost

    The values in the description fields may be identical - if they are then I want to modify the description with the highest cost.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If those are the only two fields in the table, why would you want to change two of the records to be identicle?

    Also, what do you mean by "modify the description with the highest cost". If you have three records with the same description and different costs, which one gets augmented and with what information?

    If you want a list of the highest costs for a given description, you could use something like:

    SELECT description, MAX(cost)
    FROM yourTable
    GROUP BY description
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2002
    Posts
    72
    I want to modify the description field of one of the identical records so it becomes different from the other.

    If 3 records are the same then only the record with the highest value will be modified.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Still don't get it. Please post an example dataset before and after alteration.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2005
    Posts
    119
    Maybe you could use the "find duplicates" query wizard to get what you're looking for?

Posting Permissions

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