Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: Append if Record doesn't Exist

    I'm new to MS Access and I've been struggling over this for a couple of day's and have finally conceded that I need help.

    I have a table , USERS, made up of ID(PK), UserName, Password

    For test purposes I have the table populated like this

    ID UserName Password
    1........1...............1
    2........1...............2
    3........1...............3
    4........2...............1
    5........2...............2
    6........2...............3

    I have tried to build a test query that will check if the record does NOT exist,
    ie Username = 3 and Password = 1
    and if not, insert YES1 for Username and YES2 for Password into the table.

    Code:
    INSERT INTO Users ( UserName, [Password] )
    SELECT DISTINCT "YES1" AS Uname, "YES2" AS Pword
    FROM USERS
    WHERE ((([Input1]) Not In (select UserName from USERS)) AND (([Input2]) Not In (select Password from USERS)));
    Maybe I'm trying to be too clever.
    The following input combinations should be accepted as a legitimate append
    1,4 2,4 3,1

    These all fail and the first combination to be accepted is 4,4

    The query appears to be carrying out an 'OR' for each column and not an 'AND'

    Can anyone point out where I'm going wrong please. This is just a test query for something a little more complex I've got planned.

    Thanks in advance
    Last edited by Eejit; 11-12-09 at 11:37. Reason: Formatting and spelling

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    rather than the code; I'm baffled by the logic: you write

    "I have tried to build a test query that will check if the record does NOT exist,

    and if not, insert YES1 for Username and YES2 for Password into the table."

    If the record does not exist - then - there is no record in which to insert. One would be using an append query....

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    Sorry, my wording should have been a little better.

    If the record does not exist, then I wish to append a new record.

    I thought that was what I was doing with the 'Insert into' part of the query.
    As mentioned earlier, I'm new to Access and maybe I'm trying to run before I can walk

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    INSERT INTO Users ( UserName, [Password] )
    SELECT DISTINCT "YES1" AS Uname, "YES2" AS Pword
    FROM USERS
    WHERE ((([Input1] & [Input2]) Not In (SELECT UserName & Password from USERS)));
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    Hi Sinndho,
    You're an absolute star!!!
    This works a treat. It ends my banging my head against a brick wall for now.

    Thanks very much.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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