Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    61

    Post Unanswered: Merge 2 records into one

    I have 64 records in a table. These records are actually 32 sets of 2 records which belong to eachother which can be identified by a unique number ( so there are 32 unique numbers) What I want is to merge the information from two records that belong to eachother into one record which simply has more columns. So eventually there should remain only 32 records which hold the same information as the original 64 records. Is this possible????
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Kabuki_jo
    I have 64 records in a table. These records are actually 32 sets of 2 records which belong to eachother which can be identified by a unique number ( so there are 32 unique numbers) What I want is to merge the information from two records that belong to eachother into one record which simply has more columns. So eventually there should remain only 32 records which hold the same information as the original 64 records. Is this possible????
    Yes it is ... How would you like to do it?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Yes, possible.

    I created a table, Table1 with RowID(1,1,2,2), Name1(a,b,c,d)
    then created a newID (autonumber)(1,2,3,4)
    then created these 2 queries.
    To get a "primary" newID, store as Query2:
    SELECT Table1.RowID, Min(Table1.NewID) AS MinOfNewID
    FROM Table1
    GROUP BY Table1.RowID;

    Then run this:
    SELECT Table1.NewID, Table1.RowID, Table1.Name1, Table1_1.Name1
    FROM Query2 INNER JOIN (Table1 INNER JOIN Table1 AS Table1_1 ON Table1.RowID = Table1_1.RowID) ON (Query2.MinOfNewID = Table1.NewID) AND (Query2.RowID = Table1.RowID)
    WHERE (((Table1.NewID)<>[Table1_1].[NewID]));

    It will return:
    1,1,a,b
    3,2,c,d

    BR & GL

  4. #4
    Join Date
    Oct 2003
    Posts
    61
    no longer relevant
    Last edited by Kabuki_jo; 06-03-04 at 08:23.
    Nobody dies a virgin because life screws us all!

  5. #5
    Join Date
    Aug 2013
    Posts
    14
    I know this thread is old but it fixes a problem I have exactly however with one complication. I have more than 1 duplicate with extra info. Can the script be changed to do that.

    Basically I have

    Field1
    John
    Harry
    Smith

    And I would like

    Field1 Field2 Field3
    John Harry Smith

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This thread is from 2004, but assuming that you're running a relatively recent version of MS-Access you can now do this using a Crosstab query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Aug 2013
    Posts
    14
    Hi, thanks for the reply. I looked up how to do that and it worked. I just got the newest version and I may be trying to over reach. I am not exactly an expert and think I may be going about things the long way.

    In a follow up query do you know if I can build an expression that says something along the lines of

    if field4 is present then display it, if not display the query anyway.

    At the moment it obviously gives me an error if it tries to run when that field doesn't exist in a table.

    Thanks for your help so far!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm afraid that I can't visualize your problem, but maybe someone else can. If not, please humor an old man with a small example. Either post the code that is currently not quite working and a set of sample data, or put it into a sample file and upload it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Aug 2013
    Posts
    14
    Hi, thanks again. But I managed to work around it and reading back my question the next day I realise it made no sense. sorry about that.

    It was because sometimes there might be 3 record other times there may be 5 or 10 so it was to make the cross tab query work with whatever it had. I managed to just set a fixed number of records instead.

    Thanks again for your help! I was struggling massively.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Great! I'm glad that I could be of... er... ummm... help!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Aug 2013
    Posts
    14
    If you are really bored I posted another thread about sorting a set of concatenated fields...

Posting Permissions

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