Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: One field, two records to two fields, one record

    This is much trickier than I would have thought.

    In Access 2003 I have a table with these columns:

    IDNumber Name Title1 Title2

    currently the data looks like this:


    IDNumber Name Title1 Title2

    12 Jennifer Lopez Actress
    12 Jennifer Lopez Singer
    13 Other Person Other
    14 Someone Else Job

    But I don't want there to be duplicates, so I want the data to look like this:

    IDNumber Name Title1 Title2

    12 Jennifer Lopez Actress Singer
    13 Other Person Other
    14 Someone Else Job


    It's not a problem for me to get a list with only the duplicates, and it's not a problem for me to delete the duplicate record. What I am having trouble doing is getting the second Title1 into Title2. Or the first one--it doesn't matter.

    I'm trying to do this with SQL, since I would prefer if someone besides me could understand it when I leave.

    I would be very grateful if anyone had any ideas.

    Thanks.

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You can do it with 3 queries; 1 Make Table, 1 Update, and 1 Delete. You can add these to a Macro after you see that they work. Make a copy of your Table as a backup (right-click on the table and select SaveAs).

    I didn't know the name of your table so I called it YourTable in this code. Change it to the name of your table.

    1st query - Paste this into a query in SQL view and then run it:

    SELECT YourTable.IDNumber, YourTable.Name, Max(YourTable.Title1) AS MaxOfTitle1 INTO tblTitle2
    FROM YourTable
    GROUP BY YourTable.IDNumber, YourTable.Name
    HAVING (((Count(YourTable.Title1))=2));

    2nd query - Paste this into a query in SQL view and then run it:

    UPDATE tblTitle2 INNER JOIN YourTable ON (tblTitle2.IDNumber = YourTable.IDNumber) AND (tblTitle2.Name = YourTable.Name) SET YourTable.Title2 = [MaxofTitle1];

    3rd query - Paste this into a query in SQL view and then run it:

    DELETE YourTable.*, YourTable.Title1
    FROM YourTable
    WHERE (((YourTable.Title1)=[Title2]));

    That should get you what you asked for.

    TD

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Thank you so much. I guess my problem was that I was trying not to create a new table, and instead use a query. But then I couldn't create an updatable query that would give me the info I wanted. But your way is great, and will work. Thanks for taking the time to help.

Posting Permissions

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