Results 1 to 2 of 2

Thread: Subquery

  1. #1
    Join Date
    Apr 2002

    Unanswered: Subquery

    I have a table like this :

    ID_1 ID_2
    A 1
    A 2
    B 3
    C 4


    ID_1 has duplicate values. What I wanna do is separate those two categories into 2 tables.

    First table : ID_1 ID_2
    A 1
    A 2

    Second table : ID_1 ID_2
    B 3
    C 4

    Any idea on how to do this ? Thanks

  2. #2
    Join Date
    Jun 2002
    Central New Jersey

    I took this one step further just for clarification. I added 2 ID_1 records for "D" with ID_2 values of "5" and "6" respectively.

    I then created a SELECT query that displays only those records with multiple instances of ID_1.

    Here's the SQL code:

    SELECT Table3.ID_1, Table3.ID_2, Count(Table3_1.ID_1) AS CountOfID_1
    FROM Table3 INNER JOIN Table3 AS Table3_1 ON Table3.ID_1 = Table3_1.ID_1
    GROUP BY Table3.ID_1, Table3.ID_2
    HAVING (((Count(Table3_1.ID_1))>1));

    If you add the 2 extra records to your table that I did, and you create a query and paste in this SQL, you should get this result:

    ID_1 ID_2
    A 1
    A 2
    D 5
    D 6

    If this is correct, you can use this SELECT query to create a MAKE TABLE query to separate out the items, and then to create a DELETE query to delete these items from the main table if desired.

    Hope this helps.

    "There's never enough time to do it right, but there's always enough time to do it over!"

Posting Permissions

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