Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I have almost finished normalising data from an Excel sheet. I have for example, clients, who can have many accounts, and those accounts can have many transactions..

    I have duplicate entries at client level though. I have isolated them in a table but I am now struggling to break them down appropriately. So I have eg Darren, anytown with ID of 1 then one account listed with it's many transactions. Then have the same Darren but with an ID of 2 with 1 account listed and it's many transactions. I am looking to consolidate, so dDrren 1 and 2 are merged to perhaps Darren, with account 1 below him with it's transactions and account 2 below him with its transactions. Is there a way to do this using a combination of update and append queries, dropping and adding new PKs etc?


    Thanks, Darren.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Have you run a duplicates query against the table to identify the actual number of duplicates? If so, pick one of the duplicates and run update queries for any accounts that belong to duplicates other than the one you picked, and set their link to the client table to the one you have chosen to keep. Then delete the duplicate clients you chose not to keep. It's a tedious process at best - and depending on the number of records you might want to do some of it manually and some with queries. But it can be done.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='773797' date='05-May-2009 15:41']Have you run a duplicates query against the table to identify the actual number of duplicates? If so, pick one of the duplicates and run update queries for any accounts that belong to duplicates other than the one you picked, and set their link to the client table to the one you have chosen to keep. Then delete the duplicate clients you chose not to keep. It's a tedious process at best - and depending on the number of records you might want to do some of it manually and some with queries. But it can be done.[/quote]


    Thanks, WendllB? This works ok, is there no way perhaps with SQL that we can say update ID to the row below or something like?

    Regards, Darren.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='iksotof' post='773893' date='06-May-2009 04:56']Thanks, WendllB? This works ok, is there no way perhaps with SQL that we can say update ID to the row below or something like?

    Regards, Darren.[/quote]
    Unfortunately, using SQL there isn't any easy way to refer to a next or previous row. To do that sort of thing you would have to resort to Visual Basic and either DAO or ADO to update the recordsets you are working on.
    Wendell

Posting Permissions

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