Results 1 to 13 of 13
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    112

    optimizing the CASE code with at least 30 different fields


    I have a code that works fine for 4 fields with zero or grater values. Now, I would like to extend this code to check more fields including null values. And if the values of that field is greater than zero then the results should have all the field names concatenated. background: I have different test performed each day. On a given day we only test 13 targets at the maximum so I am trying to automated the results based on the values.
    Some field names are like m1, m2, m3 , td1,td2, me1, ad1, ad7, ad14,rs1, ev1, ev2, pv1, pv2,rp and soon ( about 32 different fields).
    Only for some fields we need to check all the 4 fields ( m1,m2,m3 rp) to finalize the results

    m1_ct m2_Ct m3_Ct td1_Ct ad1_Ct rp TestNo Results Final
    0 24 0 0 0 12 Test 1 Repeat 0
    0 0 0 0 0 13 Test 1 negative 1
    24 25 23 0 0 25 Test 1 m1, m2, m3 positive 1
    0 0 0 12.3 16 23 Test 1 td1, ad1 positive 1
    12 23 25 0 0 17 Test 1 m1,m2,m3 positive 1
    I am just giving one scenario. Not sure if that is possible. Any one have any thoughts or better solution.
    Current code
    HTML Code:
    Public Function FinalResults(fd1 As Double, fd2 As Double, fd3 As Double, fd4 As Double, strTestNum As String) As String
    
    'fd1 is m1_ct, fd2 is m2_ct, fd3 is m3_ct, fd4 is Rp strTestNum is TestNo
     
    On Error GoTo Err_FinalResults
     
    booFinal = False
     
    Select Case True
     
        Case fd1 = 0 And fd2 = 0 And fd3 = 0 And fd4 > 0
            FinalResults = "m1 m2 m3 Negative"
            booFinal = True
        Case fd1 > 0 And fd2 > 0 And fd3 > 0 And fd4 > 0
            FinalResults = "m1 m2 m3 Positive"
            booFinal = True
           
        Case fd1 = 0 And fd2 = 0 And fd3 = 0 And fd4 = 0
            FinalResults = "Invalid"
            booFinal = True
       
        Case (fd1 = 0 Or fd2 > 0 Or fd3 > 0) And fd4 > 0 And strTestNum = "Test 1"
            FinalResults = "Repeat"
            booFinal = False
        Case (fd1 > 0 Or fd2 = 0 Or fd3 > 0) And fd4 > 0 And strTestNum = "Test 1"
            FinalResults = "Repeat"
            booFinal = False
        Case (fd1 > 0 Or fd2 > 0 Or fd3 = 0) And fd4 > 0 And strTestNum = "Test 1"
            FinalResults = "Repeat"
       
            booFinal = False
       
        Case (fd1 = 0 Or fd2 > 0 Or fd3 > 0) And fd4 > 0 And strTestNum = "Test 2"
            FinalResults = "Inconclusive"
            booFinal = True
        Case (fd1 > 0 Or fd2 = 0 Or fd3 > 0) And fd4 > 0 And strTestNum = "Test 2"
            FinalResults = "Inconclusive"
            booFinal = True
       
        Case (fd1 > 0 Or fd2 > 0 Or fd3 = 0) And fd4 > 0 And strTestNum = "Test 2"
            FinalResults = "Inconclusive"
            booFinal = True
           
        Case (fd1 = 0 Or fd2 > 0 Or fd3 > 0) And fd4 > 0 And strTestNum = "Test 3"
            FinalResults = "Inconclusive"
            booFinal = True
        Case (fd1 > 0 Or fd2 = 0 Or fd3 > 0) And fd4 > 0 And strTestNum = "Test 3"
            FinalResults = "Inconclusive"
            booFinal = True
       
        Case (fd1 > 0 Or fd2 > 0 Or fd3 = 0) And fd4 > 0 And strTestNum = "Test 3"
            FinalResults = "Inconclusive"
            booFinal = True
    End Select
     Exit_FinalResults:
        Exit Function   
    Err_FinalResults:
        MsgBox Err.Description & ". Procedure: FinalResults"
        Resume Exit_FinalResults  
    End Function

    Thank you

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    Need more info on the business behind the testing. Also would like to see tables and relationships.
    When I read your post, this older post/thread came to mind.

  3. #3
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    112
    Thank you for sharing the old post. But mine is little different. we test micro organisms but not measure the sensitivity.
    I am only planning to automate the results field.
    We test microbes. When the plate is set up with patient id and the targets to test( microbes) once the testing is done. We export the machine generated file that looks like the above table( forgot to add pid in that) as .csv file which has pid, target names and their Ct values only.
    I use Access DB to read this .CSV file and compare the pid if tested before in an access table( Table main) to create a results sheet in Excel to show the values in a readable format to analyse. So far I am manually selecting the final results from the Excel template after creating the results sheet. I want to automate this final results field based on the targets. For this I have a import workflow which read the csv file and at the end has update query to calculate the final results based on the above function and then write the pid, targets plu final results to the Excel spreadsheet and simultaneously save them in the Access main table.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    The old post reference was just a "similarity" I was reminded of. If you have some specifics to share and some sample code you are working with, then readers may be able to offer focused responses.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,856
    So what you're asking is for a better way to do this
    Public Function FinalResults(fd1 As Double, fd2 As Double, fd3 As Double, fd4 As Double, strTestNum As String) As String
    for up to 32 fields?

    I think you would want to use a sub that loops over the fields of a recordset and if the field has a value, add it to what will be the output, then move to the next record. Rinse and repeat. That assumes I understand the goal, which is to add the field name (and maybe its value as well) if it meets some sort of test such as not being Null. If that's not it, then you should be able to see that your issue and desired outcome isn't clearly explained.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    700
    What you want can be done.

    If al the "fields" are in one record, the table is designed as a spreadsheet, not a relational database. This will be much harder to do than necessary.

    Are you willing to redesign the table?
    Boyd Trimmell aka Hitechcoach
    Database Architect and Problem Solver
    Microsoft MVP - Access Expert
    25+ years specializing in Accounting, Inventory, and CRM systems
    "If technology doesn't work for people, then it doesn't work."

  7. #7
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    112
    Yes 'Micron', that is exactly what I want to achieve in my Step 1. If the field has value greater than zero then I would like to see the field name in the results. We call these as mixtures( generic terms to represent multiple pathogens).
    Some times there might be one pathogen and in some cases there might be 5 or 6 pathogens. If all the targets are zero then the results will be Negative

    Once this is achieved I will try to implement the step 2 to decide if that is repeat or inconclusive.

    Yes, I agree with Boyd to redesign the table or have subset or transaction table to loop in the logic. In fact I am planning to have all the field names and permutation and combinations of all the results and link that to update the main table.
    But not sure how to proceed.

    thank you

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    Please show us:
    -your table design showing field data types
    -some sample records with values and intended outputs.

  9. #9
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    112
    Datatypes..
    Sampleid is short text(40)
    all_Ct fields number(Double)
    Results short text (100)

    File with samples records attached.
    This file has all the sample data plus last two columns - results is the expected values. Comments are the conditions to generate results.

    Thank you
    Attached Files Attached Files

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    I think Micron's comment about a routine to loop over the fields in each record is a starting point to investigate.
    Also, having some number or code to identify each record's result that you can compare against may be viable. It might be simpler than a series of Case conditions, or a reduction in the number of statements, but you know your environment and just extending what you have may be fine. If you have a never ending list of conditions, then that's a new ball game.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,856
    If all you had to worry about is the columns in the sheet, then I'd say you might not be able to do this more concisely/efficiently. If there are 30 or so fields and the code you show would get much longer, then there is a decent possibility. One thing I'd propose in that case is that one or more of the rules can be simplified. When looping over the fields you can simply add the values and if the result is greater than zero, it satisfies the first condition with a proviso. If not, it satisfies the second condition, with a proviso. The proviso is that RNP can never be something other than what you state in the sheet. If it can be, then I don't know what to suggest.

    I find the data a bit confusing. Sample ID's look like just that - until you get to the green part, then they look like something else - something that all of a sudden imposes another condition to be measured against the results.

    If your table really looks like that, then it is the primary reason for such difficulty. Designing relational database tables like spreadsheets always comes back to bit the designer and I think that is the case here. Seeing as how you are this far into it, I think your easiest option is to carry on. The best option is to fix the table design if that sheet is representative.

  12. #12
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    280
    Hi all!

    Maybe in Excel is easier to get the results using some additional columns and some simple spreadsheet formulas.
    Check the formulas in cells with gold color in the attachment.

    You don't have to save the results in database.
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,837
    Here is Access VBA to provide specified output for given sample data.
    Code:
    Function Result(strID As String) As String
    Dim rs As DAO.Recordset, x As Integer
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Samples WHERE SampleID = '" & strID & "'")
    Result = IIf(IsNumeric(strID), "Negative", "Not Valid")
    For x = 1 To rs.Fields.Count - 1
        Select Case strID
            Case "HS"
                If (rs(x) = 0 And rs(x).name <> "RNP_Ct") Or (rs(x) > 0 And rs(x).name = "RNP_Ct") Then
                    Result = "Valid"
                End If
            Case "PC"
                If rs(x) > 0 Then
                    Result = "Valid"
                End If
            Case "NTC"
                If rs(x) = 0 Then
                    Result = "Valid"
                End If
            Case Else
                If (Nz(rs(x), 0) > 0 And rs(x).name <> "RNP_Ct") Then
                    Result = Left(rs(x).name, InStr(rs(x).name, "_") - 1)
                End If
        End Select
    Next
    End Function
    

    Use function in query or textbox.
    SELECT Samples.*, Result([SampleID]) AS Result FROM Samples;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-26-2016, 09:15 AM
  2. Optimizing MS Access Query
    By mariost in forum Queries
    Replies: 3
    Last Post: 11-27-2015, 04:13 AM
  3. SQL code help (Case)
    By alka in forum Access
    Replies: 10
    Last Post: 11-26-2013, 06:15 PM
  4. Help optimizing a query
    By mkallover in forum Queries
    Replies: 0
    Last Post: 03-01-2012, 09:13 AM
  5. Help optimizing an ugly query
    By kman42 in forum Queries
    Replies: 2
    Last Post: 07-26-2011, 07:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums