Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: TNM Conversion Table, IIF (Statement.....)

    The system used to describe the growth and spread of non-small cell lung cancer (NSCLC) is the TNM staging system. T stands for tumor (its size and how far it has spread within the lung and to nearby organs),N stands for spread to lymph nodes, and M is for metastasis (spread to distant organs). In TNM staging, information about the tumor, lymph nodes, and metastasis is combined and a stage is assigned to specific TNM groupings

    The grouped stages are described using the number 0 and Roman numerals from I to IV (1 to 4). Some stages are subdivided into A and B.

    Once the T, N, and M categories have been assigned, this information is combined (stage grouping) to assign an overall stage of 0, I, II, III, or IV. Patients with lower stage numbers have a better prognosis

    I have a table [Tbl Staging] which contains the 3 fields [T], [M], [N], which is the classification of the tumour stage. I've added a fourth field called [Stage]. For Each patient I would like to work out the grouped stages based on the attached .jpg file. And sample data below.

    thank you for help in advance

    T N M Stage
    T1 N0 M0 Stage 1a
    T2 N0 M0 Stage 1b
    T1 N1 M0 Stage 2a
    T2 N1 M0 Stage 2b
    T3 N0 M0 Stage 2b
    T1 N2 M0 Stage 3a
    T3 N1 M0 Stage 3a
    T3 N2 M0 Stage 3a
    T2 N2 M0 Stage 3a
    T1 N3 M0 Stage 3b
    T2 N3 M0 Stage 3b
    T3 N3 M0 Stage 3b
    T4 N0 M0 Stage 3b
    T4 N1 M0 Stage 3b
    T4 N2 M0 Stage 3b
    T4 N3 M0 Stage 3b
    T2 N0 M1 Stage 4
    T1 N0 M1 Stage 4
    T1 N3 M1 Stage 4
    T2 N1 M1 Stage 4
    T2 N2 M1 Stage 4
    T3 N0 M1 Stage 4
    T4 N3 M1 Stage 4
    T3 N2 M1 Stage 4
    T3 N3 M1 Stage 4
    T4 N0 M1 Stage 4
    T4 N1 M1 Stage 4
    T4 N2 M1 Stage 4
    T2 N3 M1 Stage 4
    Attached Thumbnails Attached Thumbnails Stagingclassification.jpg  

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Colors

    Sounds like possibly a crosstab (or Pivot table?) in some way. Not sure where you're going with this but I just wanted to let you know that instead of the *.jpg file, once you get the fields onto the report in the way you need them displayed, you can utilize the Format - Conditional Formatting to set the colors depending on the values of that particular field (although it is limited to the number of conditions you can enter - otherwise you can set the colors via code through the On_Format section for the report). This will get you part way there with the colors. Hope this helps in some way. Otherwise, I'm not sure of your setup. Others will probably be of better help in what you're trying to do as I don't quite fully understand and may be way off base on this. I just wanted to let you know of the Format - Conditional Format way to do some of the coloring which may or may not be of some help to you.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2003
    Posts
    6
    The field [stage] is what I'm trying to populate automatically in my patients table.

    I've populated the attached table manually to show the required output. However I've got 1000's of records to populate in the patients table.

    The *.jpg is just for reference to help with the conversion process, it's not the final format.

    I've decided to use the attached table as a lookup as I have all the combinations of stage I-V required, and create outer joins to my main [patient table].

    SELECT tblPatients.PatientID, tblPatients.OtherInfo,
    tblPatients.T, tblPatients.N, tblPatients.M,
    tblStaging.Stage
    FROM tblPatients LEFT JOIN tblStaging
    ON (tblPatients.M = tblStaging.M)
    AND (tblPatients.N = tblStaging.N)
    AND (tblPatients.T = tblStaging.T);

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You've got the right idea. A couple things: Don't store the stage for a given patient in the patients table. Given T, N and M, you can derive the stage whenever you need it. Storing the stage with the patient creates a possibility for integrity loss.

    Second, you may want to consider attaching a primary id to your stage lookup table. This will prove most useful when analayzing the various elements that comprise your stage.

    Overall, I'd say you hit on the best structure all by yourself. You just have to have a bit of confidence in it and figure out how to make it work for you.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2003
    Posts
    6
    Teddy - thanks for your help - I've taken your points into account and implmented them. Stage 1-4 is returned from the join to the lookup table. Now some live data has come my way, I need to restructure the query, depending on the two types of lung cancers,as the staging lookup is different for both.

    SMALL CELL = Limited/extensive staging
    Non Small Cell = TNM Staging

    Non small cell cancers (Part A)
    ----------------------

    If TNM is blank return Unspecified.
    If any part of TNM is missing return Incomplete
    if a X is present in either TNM return Unknown

    Staging: IIf(Len([Tbl_patients]![T] & [Tbl_patients]![N] & [Tbl_patients]![M] & "")=0,"Unspecified",IIf(IsNull([Tbl_Tbl_patients]![T]+[Tbl_Tbl_patients]![N]+[Tbl_Tbl_patients]![M]),"Incomplete",IIf([Tbl_Tbl_patients]![T] Like "?X*" Or [Tbl_Tbl_patients]![N] Like "?X*" Or [Tbl_Tbl_patients]![M] Like "?X*","Unknown",[Stage])))


    Small Cell Cancers (Part B)
    -------------------
    Staging2: IIf(IsNull([Tbl_patients]![Extensive/Limited]+[Tbl_patients]![Tumour Group]="Bronchus/Lung small cell"),"Unspecified",[Extensive/Limited])


    Combined = Both SQL statements work. see attached spreadsheet inside .zip file - column F & J.

    However can I wrap the above into a single SQL statement, if possible,

    i.e. IF ![Tumour Group]="Bronchus/Lung small cell" do - part A, else

    IF ![Tumour Group]="Bronchus/Non Small Cell do - Part B.
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2003
    Posts
    6
    solved it

    Staging: IIf([Tumour Group]="Bronchus/Lung small cell",[Staging],[Staging2])

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're still thinking to hard.

    Your lookup table should contain all of the information presented in your graphic. Then you do the "lookup" by filtering the where clause. Use the table structure to define what constitutes a given stage, not iif() statements. You'll save yourself a lot of grief.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jan 2003
    Posts
    6
    everything I've now done - works perfect -

Posting Permissions

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