Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Group (Ak2)

    Good Afternoon,

    I have somewhat of a difficult problem and was hoping someone could help me.
    In the table,tbl1, is a field, DXCodes (text) that holds ICD9 Codes and DXCategory (which is blank) ---the ICD9 Codes are listed as follows:
    Code DXCategory
    110.00
    110.1
    110.2
    110.3
    140.1
    140.3
    etc.

    In another table, tbl2, is a field, DXGroup (text) and DXCategory (text). With the group and Category as follows:
    Code DXCategory
    110-115 "Neoplasm"
    140-150 "Germs"

    What I'm trying to do is "Update" DXCategory field in tbl1 with the data that is in DXCategory in tbl2.

    There isn't a way to join these two tables...so I thought to create a Module...

    <hr>Function DXCodes(strTitle) As String'Dim DXCodes As String
    Select Case strTitle
    Case "001-139.9"
    DXCodes = "Neoplasm"
    Case "140-239"
    DXCodes = "Germs"
    Case "240-279.9"
    DXCodes = 3
    Case "280-289"
    DXCodes = 4
    Case "290-319.9"
    DXCodes = 5
    Case "320-389"
    DXCodes = 6
    Case "390-459.9"
    DXCodes = 7
    Case "460-519"
    DXCodes = 8
    Case "520-579.9"
    DXCodes = 9
    Case "580-629"
    DXCodes = 10
    Case "630-679"
    DXCodes 11
    Case "v20-V22.2"
    DXCodes 11
    Case "760-764.99"
    DXCodes = 11
    Case "680-709"
    DXCodes = 12
    Case "710-739.9"
    DXCodes = 13
    Case "740-759"
    DXCodes = 14
    Case "765-779"
    DXCodes = 15
    Case "780-79"
    DXCodes = 16
    Case "800-999.9"
    DXCodes = 17
    Case "V01-V06"
    DXCodes = 18
    Case "V07-V09"
    DXCodes = 19
    Case "V10-V19"
    DXCodes = 20
    Case "V20-V29"
    DXCodes = 21
    Case "V30-39
    End Select
    End Function
    <hr>

    and use the function in a query to update the table because the data needs to be "in Category Groups" rather than each indivivual ICD9 code. This should work in the line of an IIF Statement...i.e. IIf([DXGroup]=001-13.9, "Neoplasm","")

    However, it's not working.

    I was hoping if someone had a suggestion or tell me what is wrong with my code, I would be greatful.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group (Ak2)

    You don't really need the DXCategory field in tbl1, since it can be derived from tbl2, so it is redundant information. You can create a query that returns the appropriate category for each code, and use that in forms and reports. See qryCombined in the attached database.
    If you really want to populate the DXCategory field in tbl1 with derived information, you can use an update query - see qryUpdate in the attached database.
    I have assumed that all codes have 3 characters before the point.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Group (Ak2)

    Hans,

    After working with this for just a short time, I believe it will work just fine....1 small issue though and I certainly don't know how to write the code for it.....

    It may be that, including the "." there could be 5 char's. 1 digit following the "." or 2 digits following the ".". In one group it may be something like:
    110.01
    110.04
    110.40

    or

    110.5
    121.6
    121.60

    How can I eliminate this problem. For the Lo I, want to capture "everything" to the left of the "." for the Hi, I want to capture everything after the "."

    Thanks for your patience.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group (Ak2)

    Roberta,

    Open qry2 in design view and change the definition of DXHi to
    <code>
    DXHi: Mid([DXGroup],InStr([DXGroup],"-")+1,3)
    </code>
    This should work as long as the first 3 digits of groups are distinct. There would be a problem if there is overlap, for example if one group extends to 139.5 and the next group starts at 139.6.

Posting Permissions

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