Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulating Duplicate Values (2000)

    I have a table with an Area field, ID number field and other fields. Some of the records have matching ID numbers, though the Area data is unique for each record. For such records, the other fields contain duplicate data. What I want to do is generate a new record consisting of the Sum of the Area for the two records that have matching ID numbers, as well as a copy of the other duplicate fields. Then I want to delete the matching records. It would also be fine to end up with one table containing the original records plus a new table containing only unique values plus the new records generated by the Sum operation. I could then just delete the entire old table in favor of the new table.

    Original data:
    ID Area Units New_Units
    1 305 1 2
    2 4000 2 4
    2 250 2 4
    3 1200 1 4

    Desired result:
    ID Area Units New_Units
    1 305 1 2
    2 4250 2 4
    3 1200 1 4

    Is it possible to easily accomplish this or is this complicated?

    I don't know how to program. I know a bit about setting up Expressions in the Query Design window and creating Macros.

    Any help would be appreciated.

    Jeff

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Manipulating Duplicate Values (2000)

    Hi Jeff,
    From what you've described, this sounds relatively straightforward. You need a make-table query which groups on ID, uses Last functions on all the duplicate fields, and Sum on the Area fields.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Duplicate Values (2000)

    Perfect! Thanks for the quick response, I really appreciate it.

Posting Permissions

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