Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Unanswered: Easy way to do an IF statement in Access?

    I want to do this:

    Table 1
    code|user
    PE | 156
    GE | 189
    TE | 205
    SE | 265

    Table 2
    code|user
    PE | 156
    GE | 189
    TE | 205
    SE | 265
    BE | 356
    WE | 115

    And then get a query that says If a user matches code in Table 2 compared to Table 1, create a new field and mark a 1 in in. If it doesn't match a code (like WE and BE for example), mark 0 in that field.

    So the end result would be:
    code|user|result
    PE | 156 | 1
    GE | 189 | 1
    TE | 205 | 1
    SE | 265 | 1
    BE | 356 | 0
    WE | 115 | 0

    Thanks!
    Last edited by agalloch; 06-25-10 at 10:29.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look up SWITCH(). You could also use IIF(), but I prefer SWITCH().

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    make a query ; put both tables in the query; join them 2x - each column to its same named column; then add all 4 columns to the query;

    now you can easily included a calculated field to the query for the solution you want...

  4. #4
    Join Date
    Jun 2010
    Posts
    9
    I tried using both IIF and SWITCH and it just seems to be taking forever, and then I get like an error at the end saying Max database size achieved (it's only 500 MB) or temporary storage space....

    This are the queries I tried:

    Switch([table1].[code]=[table2].[code],"1",[table1].[code]<>[table2].[code],"0") AS Result

    IIF ([table1].[code]=[table2].[code], 1, 0 ) AS Result

    Soooo, what I did is I got all distinct values of code in table1, so I'm going to try to just put them all in a query. I tried like this (there's 195 unique ones BTW):

    IIf([table2].[code]="PE" OR "GE" OR "TE" OR "SE",1,0) AS Result

    I'm thinking I can't just use OR that way or I'm missing some brackets?

    Quote Originally Posted by NTC View Post
    make a query ; put both tables in the query; join them 2x - each column to its same named column; then add all 4 columns to the query;

    now you can easily included a calculated field to the query for the solution you want...
    I'm not sure if that would work. Table1 only has like 600 total, and Table2 is like 300k or so. So it would have to be like where table1.code=table2.code, but how would it match them up?

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    How about something like this?

    Code:
    SELECT T2.code, T2.user, IIf(IsNull(T1.user),0,1) AS result FROM T2 LEFT JOIN T1 ON ((T2.code = T1.code) AND (T2.user = T1.user));
    I don't mean to argue with Pootle, who is one of the most knowledgeable Access programmers on this forum, but this is my style.

    SL

  6. #6
    Join Date
    Jun 2010
    Posts
    9
    Quote Originally Posted by Sam Landy View Post
    How about something like this?

    Code:
    SELECT T2.code, T2.user, IIf(IsNull(T1.user),0,1) AS result FROM T2 LEFT JOIN T1 ON ((T2.code = T1.code) AND (T2.user = T1.user));
    I don't mean to argue with Pootle, who is one of the most knowledgeable Access programmers on this forum, but this is my style.

    SL
    Thanks. However, that won't work for me since I need it to match only based off the code, not user. The users aren't the same in the two tables. It basically needs to say any code that exists in the table1 (PE, GE, TE, SE) needs to be 1 in table2's new "Result" field, and all other codes should be 0's. Although 0's aren't really even necessary, they could stay null. I tried removing the user = user, but that gave me like twice as many results as there was in the original table. Weird.

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by agalloch View Post
    Thanks. However, that won't work for me since I need it to match only based off the code, not user. The users aren't the same in the two tables. It basically needs to say any code that exists in the table1 (PE, GE, TE, SE) needs to be 1 in table2's new "Result" field, and all other codes should be 0's. Although 0's aren't really even necessary, they could stay null. I tried removing the user = user, but that gave me like twice as many results as there was in the original table. Weird.
    No prob. Just change the query to read
    Code:
    SELECT T2.code, T2.user, IIf(IsNull(T1.code),0,1) AS result FROM T2 LEFT JOIN T1 ON T2.code = T1.code;
    I think that should work.

    SL

  8. #8
    Join Date
    Jun 2010
    Posts
    9
    Awesome, that worked perfectly. Thanks a bunch!

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is minor: semantically the same, but faster:
    Code:
    SELECT T2.code, T2.user, IIf(T1.code IS NULL,0,1) AS result FROM T2 LEFT JOIN T1 ON T2.code = T1.code;

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by agalloch View Post
    Awesome, that worked perfectly. Thanks a bunch!
    You' welcome.

    SL

Posting Permissions

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