Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Seperate Duplicate Records? (A2k (9.0.3821) SR-1)

    How to Seperate Duplicate Records?
    A2k (9.0.3821) SR-1

    Looking for technique to create a new table with unique records based on Code and Desc, remove duplicate records except for just one and put the remaining duplicate records in a second table

    Input

    Code Desc Quan Price Autonumber
    101 Red 2 5.00 1
    102 Blue 3 3.00 2
    102 Blue 2 3.00 3
    103 Green 5 7.00 4
    104 Yellow 3 2.00 5
    104 Yellow 2 2.00 6
    104 Yellow 1 2.00 7

    Output

    UniqueRecordTable
    101 Red 2 5.00 1
    102 Blue 3 3.00 2
    103 Green 5 7.00 4
    104 Yellow 3 2.00 5

    AdditionalDuplicateTable
    102 Blue 2 3.00 3
    104 Yellow 2 2.00 6
    104 Yellow 1 2.00 7

    Any help would be appreciated.

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Seperate Duplicate Records? (A2k (9.0.3821) SR-1)

    Hello John,

    First create a new query based on your data table. Add all fields to the grid.
    Make the query into a Totals query. This sets the Totals option for all fields to the default option Group By.
    Leave it that way for Code and Desc, but change it to First for all other fields. Set the alias or caption of these other fields to the original field name (otherwise, they will be labeled FirstOfQuan etc.) Switch to datasheet view to check whether the query selects what you want.
    Back in design view, make the query into a MakeTable query and specify the table name, say tblUnique. Execute the query.
    Close the query (save it for future use).

    Next, create a new query based on tblUnique and on the data table. Link them on the field that is the AutoNumber field in the data table. Make it into an outer join that displays ALL records from the data table.
    Add all fields from the data table, and the link field from tblUnique. Uncheck Display for the link field in tblUnique, and set the criterion for this field to Is Null. Switch to datasheet view to check whether the query selects what you want.
    Back in design view, make it into a MakeTable query, specify the table name and execute it.
    Close the query (save it for future use).

    If this sounds too complicated, see the attached zipped Access 97 database.

    Regards,
    Hans
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Seperate Duplicate Records? (A2k (9.0.3821) SR-1)

    Hi Hans

    You nailed it, was always wondering what first, last was, picture is worth 1000 words

    I can see if there was a time and date stamp or count involved, input could be sequenced and I would have control ove first of last, hi or low, etc.

    I know I can reuse this concept in other situations.

    Thanks for your help.

    John

Posting Permissions

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