Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    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
    16,716
    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
    143
    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
    16,716
    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
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    702
    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?

  7. #7
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    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
    16,716
    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
    143
    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
    16,716
    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
    12,737
    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 Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    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
    52,816
    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.

  14. #14
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Yes Micron its little complicated. Sampelid with text values are called controls that is used to help identify the plate pass/fail based on valid o Invalid.

    Thank you Accesstos. This works only for the excel spread sheet. I also need to save the results into the Access table. But this works for the time being.

    Thank you June. I tested the code with couple of files and it worked perfectly as desired. I am planning to add 2 new fields to Samples table (date_tested as date field and TesTNo a small text field( 15)),.How can I eliminate them from the function?

    Once again thank you so much for all your time and hard work to resolve this.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Instead of * wildcard for fields can explicitely list fields to retrieve in SQL statement.

    Or have an If Then conditional that checks for those field names.

    The code I built relies on field order in table same as shown in your sample file. It expects first field to be SampleID and since first field has index 0, the loop code starts with index 1 and cycles for the number of fields counted. If the number of fields retrieved is pre-determined, the cycle can be hard-coded, such as: For x = 1 To 30. If the unwanted fields are beyond field index 30 they will not be included.

    Pick your preference.
    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.

Page 1 of 2 12 LastLast
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
  •  
Other Forums: Microsoft Office Forums