Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: HOw to get result in a column

    Auth Count roleid Result
    ZBM007 4 007 NOTMATCH
    ZMX007 4 007 NOTMATCH
    ZPR007 3 007 NOTMATCH
    ZUS007 3 007 NOTMATCH
    ZBM006 4 006 MATCH
    ZMX006 4 006 MATCH
    ZPR006 4 006 MATCH
    ZUS006 4 006 MATCH


    Hi,
    I have above Auth, Count and RoleId in one table, how do I get fourth column using Query as a Reult based on comparision that IF ROLEID is the same and count is not the same for all same roleid, it will show text "NOTMATCH" otherwise it will show text "MATCH"

    Please help...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select auth,count,roleid, iif(count=roleid,"Match","Nomatch") as Result from my table order by Result,Auth
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    Posts
    24
    I tried your query and I got #error as result.

    In fact I am not looking to compare Count with Roleid.

    I am looking to see for same roleid record, count matches. In below case RoleID 0007 has four records but count is 4, 4, 3, 3..which are not the same. In this case result should show NOTMATCH. in short count should be the same for all the record with the same roleid.

    Auth Count roleid Result
    ZBM007 4 007 NOTMATCH
    ZMX007 4 007 NOTMATCH
    ZPR007 3 007 NOTMATCH
    ZUS007 3 007 NOTMATCH
    ZBM006 4 006 MATCH
    ZMX006 4 006 MATCH
    ZPR006 4 006 MATCH
    ZUS006 4 006 MATCH

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why wouldn't the first two be a match. There are 4 x 007 records, the first two have a count of 4, which is a match.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    im guessing that count is numeric and roleid is string so you would need to convert one to the other

    eg
    Code:
    select auth,count,roleid, iif(format(count,"000"=roleid),"Match","Nomatch") as Result from my table order by Result,Auth
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2003
    Posts
    24
    Quote Originally Posted by StarTrekker
    Why wouldn't the first two be a match. There are 4 x 007 records, the first two have a count of 4, which is a match.
    Because same RoleId e.g. 007 with all four record does not have same number of count. It should show MATCH only if all the record with the same role ID has the same count.

    I hope this helps.

    Note: RoleID field is Text Field. Value could be anything.

  7. #7
    Join Date
    Oct 2003
    Posts
    24
    Quote Originally Posted by healdem
    im guessing that count is numeric and roleid is string so you would need to convert one to the other

    eg
    Code:
    select auth,count,roleid, iif(format(count,"000"=roleid),"Match","Nomatch") as Result from my table order by Result,Auth
    Healdem,
    Thank you for you answer. It did fixed #error with your query but actual result is different than expected.

    Comparision should be with RoleID with ROLEID from previous record first.
    If Previous and existing record has the same RoleID, then it will check for count from record of previous count. If count is not the same as previous count than it will show "DONOTMATCH"
    ================================================== ====
    I was able to do this logic very easily in EXCEL using below formula where D1 and D2 is cell for RoleId column and B1 and B2 is cell for Count Column. Actual formula is written in cell E2:

    =IF(D1=D2,IF(B1=B2,"MATCH","DONOTMATCH"),"MATCH")

    But I wanted to do this in access query since i love access.
    Last edited by mamin; 03-25-09 at 16:17.

Posting Permissions

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