Results 1 to 4 of 4

Thread: SQL newbie

  1. #1
    Join Date
    Jun 2003
    Posts
    19

    Unanswered: SQL newbie

    Hi Guys,

    First, thank you in advance for any help you offer.

    I've been tinkering with SQL for a MS Access program and have a problem with what I am trying to do.

    This SQL creates a combination of all Name.Name_ID and all Requirement.Requirements_ID. It appends it to my Table called Combo with fields: Name_ID and Requirement_ID. My code always appends the entire combination of IDs to the table instead of just new combinations. I tried making the the ID's in my Combo table to be a single Primary Key, but then I get an error with the combinations that I are the same.

    Please Help


    Here is my code:

    INSERT INTO Combo ( Name_ID, Requirement_ID )
    SELECT DISTINCTROW Name.Name_ID, Requirement.Requirement_ID
    FROM Name, Requirement;

    Thanks,
    Steve

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    Re: SQL newbie

    Here's one workaround: Run a select query on Name and Requirement, run a "Find Unmatched" query (created from the wizard) between that and the Combo table, then run and append query off of the Unmatched query to the Combo table.

    Option 2: If your program design allows it, delete all the records from the Combo table then run your current SQL.
    SteveH

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Smile otherwise try this 1

    INSERT INTO Combo ( Name_ID, Requirement_ID )
    SELECT DISTINCTROW Name.Name_ID, Requirement.Requirement_ID
    FROM Name, Requirement

    WHERE Name_ID , Requirement_ID NOT IN (SELECT Name_ID, Requirement_ID FROM Combo)

  4. #4
    Join Date
    Jun 2003
    Posts
    19

    Thank you

    Thank you guys for you help. I was able to get it to work perfectly.

    For those who may want to use this code, I did need to change one thing to make it work.

    The code I used is:

    INSERT INTO Combo ( Name_ID, Requirement_ID )
    SELECT DISTINCTROW Name.Name_ID, Requirement.Requirement_ID
    FROM Name, Requirement

    WHERE Name_ID & Requirement_ID NOT IN (SELECT Name_ID & Requirement_ID FROM Combo);

    I just replaced the commas in the second section with the '&' sign.

    Thanks Again for your 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
  •