Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: Duplicate records

    I have one table, with two fields. Field 1 contains duplicate records, while field 2 has unique records.
    I would like to turn the duplicates into uniques (ie. thru append query) and then add all the corresponding records from the dupes to the existing records in field 2.

    Example.
    Field 1 Field 2
    Jack 123
    John 222
    Jack 345
    Jack 456
    Jill 232
    John 444

    INTO

    Field 1 Field 2
    Jack 123, 345, 456
    John 222, 444
    Jill 232

    Sorry for the poor explanation, hopefully it's clear enough. Any help would be much appreciated. I can see in theory how this should be done but am unable to do it in practice.

    Thanks,

    Mark

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: Duplicate records

    Originally posted by marko_polo
    I have one table, with two fields. Field 1 contains duplicate records, while field 2 has unique records.
    I would like to turn the duplicates into uniques (ie. thru append query) and then add all the corresponding records from the dupes to the existing records in field 2.

    Example.
    Field 1 Field 2
    Jack 123
    John 222
    Jack 345
    Jack 456
    Jill 232
    John 444

    INTO

    Field 1 Field 2
    Jack 123, 345, 456
    John 222, 444
    Jill 232

    Sorry for the poor explanation, hopefully it's clear enough. Any help would be much appreciated. I can see in theory how this should be done but am unable to do it in practice.

    Thanks,

    Mark
    its clear enough to me, you may need to do some vb code to achieve this (not that complex) but what do you mean by "theory" and 'in practice"? do you mean you need us to write all the codes for you?

    pls get back
    qha_vn

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I agree - through code - create the unique recordset and then loop through for matching records and addnew to the other table

    qh_vn is right - code but anyone doing this needs a better empirical sample -

  4. #4
    Join Date
    May 2003
    Posts
    4
    Thanks for your replies.
    When I meant in theory, I meant as the person above suggested that one should create a unique set. Then IF the NAME (field1 in table 1) matches the Unique NAME (say field 1 in table 2) put the corresponding field 2 (from table 1) into field 2 in table 2 and delimit successive entries by a comma. Alternatively, one could create a new field in table 2 for each new entry. ?

    I, however, am a newbie and do not program as such. I understand how to use access and run basic GUI queries; as such this poses a problem.
    I wouldn't expect you to write the code for me, however, would this be a difficult thing for me to write if I pick up some SQL manuals.

    Thanks,
    Mark

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    It all depends on your affinity for certain processes - I am a fan of the ADO/Dao models but yes sql can do this as well

    This forum is here to assist everyone - so if you need some code or examples - give us more info and someone will take a stab at it - I am sure.

  6. #6
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    agreed

    i didnt mean we would not write the codes for you (sorry if my above words drove you thought so) just meant if you want us to write the codes, you would need to give us more details

    have nice weekend all guys
    qha_vn

  7. #7
    Join Date
    May 2003
    Posts
    4
    Hey
    Thanks for the support.


    Can someone recommend me a solid SQL text for beginners?


    Mark

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Instant SQL Programming
    Joe Celko
    http://amazon.com/exec/obidos/ASIN/1874416508/r937-20

    Practical Web Database Design
    Auld, Kent, Limeback, Stanger, Willis
    http://amazon.com/exec/obidos/ASIN/1904151205/r937-20
    (disclaimer: i'm one of the authors)


    rudy
    http://r937.com/

  9. #9
    Join Date
    May 2003
    Posts
    4
    Can someone explain to me how to delimit each successive (undetermined number) entry in a new column with a comma?

    Say I have a couple of values that I want to insert into a colum, how do i delimit them so that I get ie. 1, 3, 5 ...

    Thanks

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I have a table called table1
    two fields:
    myfield
    myid

    i used the following statement that inserts x followed by a comma followed by y into the filed whose id is 2



    UPDATE Table1 SET Table1.myfield = "x" & "," & "Y"
    WHERE (((Table1.Myid)=2))

    as long as the character is eclosed with double quotes and concantonated properly you can do this

    Caveat:Try to avoid putting double quotes into a filed
    HTH

  11. #11
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Re: Duplicate records

    I had a similar need as you do I attached a sample database to this message with my solution. If you have any questions let me know.
    Attached Files Attached Files
    Last edited by Mark Gambo; 06-04-03 at 21:06.

  12. #12
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Re: Duplicate records

    Originally posted by marko_polo
    I have one table, with two fields. Field 1 contains duplicate records, while field 2 has unique records.
    I would like to turn the duplicates into uniques (ie. thru append query) and then add all the corresponding records from the dupes to the existing records in field 2.

    Example.
    Field 1 Field 2
    Jack 123
    John 222
    Jack 345
    Jack 456
    Jill 232
    John 444

    INTO

    Field 1 Field 2
    Jack 123, 345, 456
    John 222, 444
    Jill 232

    Sorry for the poor explanation, hopefully it's clear enough. Any help would be much appreciated. I can see in theory how this should be done but am unable to do it in practice.

    Thanks,

    Mark
    I had to sort my data in a simliar manner and here is how I did it (See attached db file)

Posting Permissions

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