Page 1 of 2 12 LastLast
Results 1 to 15 of 17

multiple criteria of creating a button and interpretation

  1. #1
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14

    multiple criteria of creating a button and interpretation

    I am working on the bacterial projects regarding collecting data to study the prevalence of antibiotic resistance.


    The step in susceptibility process, I have to select drugs in subform (stSusceptibility Subform) based on a pathogen (Result) in main form (stSusceptibility Details Form),
    Is it possible to create only one button for 8 criteria and automatically interpret to S,I or R in Interpret field when input data in Zone Diameter field based on reference range (S_ZD, I_ZD, R_ZD fields)
    For example
    if a pathogen in main form is Aeromonas caviae which is in Aeromonas group (GroupDrug field in stBacteriaGroups table) then select DrugID; AAZMR18, ACAZ10, ACIP10, ACRO10, ACTX10, ANAR18, ASXT10 and ATE10 in Antimicrobials table.
    if a pathogen is Campylobacter coli in Campylobacter coli group >> select CAZMCC13, CCDCC13, CECC13, CTECC13, CC13, CCIP13, CCN13, CNA13 >>Campylobacter will input data in MIC field and interpretation depends on S_MIC, I_MIC, R_MIC

    MIC and Zone Diameter are the value from performing susceptibility testing but different methods
    Campylobacter and Arcobacter >> MIC, other pathogens >> Zone Diameter


    Click image for larger version. 

Name:	GroupbyDrug.PNG 
Views:	37 
Size:	53.9 KB 
ID:	38178

    Click image for larger version. 

Name:	GroupbyPathogen.PNG 
Views:	38 
Size:	68.0 KB 
ID:	38179

    Click image for larger version. 

Name:	frmSusceptibility.PNG 
Views:	35 
Size:	41.7 KB 
ID:	38180

    Click image for larger version. 

Name:	frmSusceptibilityInterpretation.PNG 
Views:	34 
Size:	42.6 KB 
ID:	38181



    I have been trying to search for this for a couple days but I cannot find the answer.
    Thank you very much in advance.
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,690
    Ginger,

    I am assuming that these threads are related to this thread. Just trying to give readers relevant info to assist with any response.
    http://www.accessforums.net/showthread.php?t=76386
    http://www.accessforums.net/showthread.php?t=76512

    Good luck with your project.

    For anyone reviewing Microbiology susceptibility testing, I did find this link via Google that describes
    MIC minimum inhibitory concentration and
    how to interpret susceptibility S (sensitive), I (intermediate), or R (resistant).

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    621
    Hi, looking at the database I saw it wasn't really normalized. Example: tables having an autonumber but another text field as PK, fields with multiple data (example ColonyFromAgar), and other. It gives me the look and feel of working with Excel. I think normalizing the data would be the first step to find the solution.

  4. #4
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    Sorry for not explaining clearly, I didn't use autonumber as primary key because each of sample has its ID and colony from agar field is just describe that what agar plate the pathogens grow and each of specimen uses different agar media, for example, specimen is stool >> use Mac, HE, MSRV, mCCA, TCBS and Blood agar with a membrane filter.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    I don't understand you reason for not using an Autonumber for the PK field.


    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


    I don't like that a couple of tables have Lookup fields ( See The Evils of Lookup Fields in Tables )
    I also don't understand why you used a split form as the default view.

    I normalized your design a little. PK fields are Autonumber type and FK fields are Long Integer type.

    Click image for larger version. 

Name:	Relationships1.png 
Views:	28 
Size:	99.4 KB 
ID:	38185
    Probably needs one more table (ColonyfromAgar) for the "CultureMedia". Your "CultureMedia" table violates 1NF.


    In your first post, you asked
    Quote Originally Posted by Ginger View Post
    Is it possible to create only one button for 8 criteria and automatically interpret to S,I or R in Interpret field when input data in Zone Diameter field based on reference range (S_ZD, I_ZD, R_ZD fields)
    That is a definite maybe! (If I understood what you wanted).
    It would probably need lots of code.
    Could you explain step by step your examples?
    I don't know what you mean by ">>".

    "if a pathogen is Campylobacter coli".... what should happen?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    I have already changed the primary keys as you suggested. there are 3 main tables, 1.tblCulture 2.tblIdentification 3.tblSusceptibility.

    The bacteria culture process, if there is growth of pathogenic bacteria on a agar plate, a staff will identify bacteria and fill the data in Pathogen field in frmCultureResultDetail (Identification tab) after that another staff will perform susceptibility testing which follows guidelines for interpretation and fill the data in Antimicrobial field in frmSusceptibilityDetail which in this process, the staff has to repeatedly add about 9 drugs and interpret the data according to S_ZoneDiameter, I_ZoneDiameter and R_ZoneDiameter reference fields by filling the data in ZoneDiameter field and interpret in Interpret field as S, I, R, WT, NWT. So I would like to create a button to add drugs following the criteria below and automatically interpret the data.

    The susceptibility testing of Campylobacter and Arcobacter is MIC data and will be interpreted as S or R.
    If the MIC is between S and R then will be interpreted as R

    Wild-type (WT) and non-wild-type (NWT) instead of susceptible and resistant, respectively

    Click image for larger version. 

Name:	Criteria.JPG 
Views:	23 
Size:	48.2 KB 
ID:	38200

    Click image for larger version. 

Name:	RelationshipEdit.PNG 
Views:	23 
Size:	55.9 KB 
ID:	38201

    Thank you so much for your help!
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    The tables and relationships look better.
    My naming convention is to add a suffix to the PK and FK fields to make it easier to know which is a PK field and which is a FK field. So I use suffixs "_PK" and "_FK". Some don't use the underscore to save typing. Others use prefixes of "pk" or "fk" for the fields. Either way, it really helps (IMHO).


    I had to do some research to find try and understand your jargon.......
    I found
    MIC = Minimum Inhibitory Concentration
    CFU = Colony-forming unit
    I knew about Petri dishes (https://en.wikipedia.org/wiki/Petri_dish)..... didn't know that they were sometimes stacked upside down and incubated...... interesting.

    I found an article from Feb 2001 that really helped: Microbiology Guide to Interpreting MIC (Minimum Inhibitory Concentration)
    by Linda Matros, DVM, DACVIM Terri Wheeler, DVM and the Microbiology Team IVS Sacramento


    There is an "Old Programmer's Rule" : If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.



    I kind of understood your explanation about what you wanted toe button to do....... but not really.

    So: (START)
    1) a staff will identify bacteria and fill the data in Pathogen field in frmCultureResultDetail (Identification tab) after that OK
    2) another staff will perform susceptibility testing which follows guidelines for interpretation and fill the data in Antimicrobial field in frmSusceptibilityDetail OK?

    which in this process,
    the staff has to repeatedly add about 9 drugs Are the drugs in the following table?
    Click image for larger version. 

Name:	Criteria.JPG 
Views:	23 
Size:	48.2 KB 
ID:	38203



    and interpret the data according to S_ZoneDiameter, I_ZoneDiameter and R_ZoneDiameter reference fields Where are the reference fields? (in a table? Which table?)


    by filling the data in ZoneDiameter field and interpret in Interpret field as S, I, R, WT, NWT. Are there reference fields? (in a table? Which table?)
    So I would like to create a button to add drugs following the criteria below and automatically interpret the data.



    Can you write down step by step the process starting from START (see above)?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    "the staff has to repeatedly add drugs" I meant that the staff has add drugs of each pathogen following the 8 criteria that I made a table showing you above over and over, for example, Campylobacter jejuni and Arcobacter butzleri grow on agar plate in the same specimen the the staff has to add drug the same pattern twice.

    I use qrySusceptibility (tblSusceptibility+tblAntimicrobial) in frmSusceptibilitySubform, when I fill the data in Antimicrobial field in frmSusceptibilityDetail, the references will pop up

    Click image for larger version. 

Name:	Breakpoint.jpg 
Views:	24 
Size:	123.8 KB 
ID:	38204

    Click image for larger version. 

Name:	Breakpoint 2.jpg 
Views:	21 
Size:	100.8 KB 
ID:	38205

    Click image for larger version. 

Name:	Breakpoint 3.jpg 
Views:	21 
Size:	176.6 KB 
ID:	38206

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    OK, I added 2 tables, 2 queries and 2 forms for the criteria image.
    I changed a few field names and changed the name of the sub form container in the form "frmSusceptibilityDetail".

    Open the form "frmSusceptibilityDetail" and go to the last record #7. (I added record movement buttons.)
    You should see an Identification number of 73.
    Click on the "Add Drugs" button. Ta-Da!


    I asked for step by step instructions on how to get the ZoneDiameter, MIC and Interpret values, but............

    If it was my dB, I would hide the first 3 columns in the sub form "stSusceptibilitySubform". I would also remove the Lookup fields in the tables.
    There are other things I wold change/fix.....


    Is this anything close to what you are wanting?
    Attached Files Attached Files
    Last edited by ssanfu; 04-25-2019 at 07:55 PM. Reason: Added code to dB
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    This is exactly what I want!!! I really appreciate your help in resolving the problem.

    Zone Diameter is zone of inhibition by Kirby-Bauer (disk diffusion) method. The disk diffusion method is performed using 1.Mueller-Hinton Agar or Mueller Hinton Agar supplemented with 5%
    based on guidelines and growth of bacteria (fastidious bacteria do not survive well) 2.antibiotic disks such as ciprofloxacin (CIP), ceftriazone (CRO).
    The first step, pick 4-5 isolate single bacterial colonies (see picture below), the second step, adjust the 0.5/1 (based on guidelines) McFarland turbidity of bacterial suspensions
    by suspending colonies in 1st step in sterile saline/brain heart infusion (based on guideline) tube. the third step, spread the suspension in 2nd step onto the surface of the agar
    and then put paper disks of antibiotics onto the surface of the agar. the final step, incubate (1624 h at 35 C based on guidelines) after incubation, measure from one edge of
    the clear circular zones to the other edge of growth inhibition around each of the antibiotic discs. This is zone diameter.
    https://en.wikipedia.org/wiki/Disk_d...ganism_bks.png

    Determination of MIC by broth microdilution, E-test etc. depending on laboratories, the most common use Etest method which likes disk diffusion method but Etest is a quantitative technique.
    The MIC value is read from the scale, in link below MIC=0.094.
    https://en.wikipedia.org/wiki/Etest#...test_Ngono.jpg

    Click image for larger version. 

Name:	Shigella sonnei (MAC).jpg 
Views:	20 
Size:	112.6 KB 
ID:	38218

    Click image for larger version. 

Name:	Shigella sonnei (SS).jpg 
Views:	19 
Size:	113.7 KB 
ID:	38219

    Another thing, can you please tell me just concepts of automatic interpretation?

    I cannot thank you enough for helping me to complete the access database.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    I understood about a 10th of what you said. It's been many, many years since I have been a Biology lab. But it is interesting.

    Quote Originally Posted by Ginger View Post
    Another thing, can you please tell me just concepts of automatic interpretation?
    I would.... if I knew what you are asking. (automatic interpretation????)
    Are you asking how I was able to add the several drugs that you wanted?


    Again, happy to help. It was a fun project to try and understand what you were trying to do and write the code.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    I'm so sorry for not explaining clearly what I want.
    I think it's similar to this link school grade calculation but my data have multiple criteria. For example, susceptible ciprofloxacin breakpoints for Salmonella and Enterobacteriaceae are >=31 mm and >=21, respectively. Moreover, MIC breakpoints of Campylobacter is not clear. So if MIC value is between S and R, it will be interpreted as R.

    I appreciate your kindness.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    Ummm, I've read the previous post (about 35 times) and it is not making a lot of sense.... yet.


    Referring to the form "frmSusceptibilityDetail" in the dB "Bacteria Mod1_ss.accdb":

    Q1) does the order of the fields "S_ZoneDiameter", "I_ZoneDiameter" and "R_ZoneDiameter" on the form make a difference?
    Q2) do the fields "S_ZoneDiameter", "I_ZoneDiameter", "R_ZoneDiameter", "S_MIC", "I_MIC" and "R_MIC really need to be visible?
    Q3) on the same form, the staff enters only enter: the drug, the "ZoneDiameter" and/or the "MIC" values?
    Q4) the "ZoneDiameter" value references "S_ZoneDiameter", "I_ZoneDiameter" and "R_ZoneDiameter" to get the value for Interpret field?
    Q5) the "MIC" values references "S_MIC", "I_MIC" and "R_MIC" to get the value for Interpret field?


    Example 1:
    For the record where Culture ID = 27 and Identification ID = 67 (record 1), the first drug is "Azithromycin" and The MIC entered = 20.
    The staff would look at "R_ZoneDiameter" and determine that 20 is greater than (GT) the value (13)
    The staff would then look at "I_ZoneDiameter" and determine that 20 is outside the "I_ZoneDiameter" value of 14-16.
    The staff would then look at "S_ZoneDiameter" and determine that 20 is GT the value 17.
    Q6) Therefore the staff would enter "S" in the "Interpret" field?


    Example 2:
    For the record where Culture ID = 27 and Identification ID = 68 (record 2), the first drug is "Ampicillin" and The ZoneDiameter entered = 0.7.
    The staff would look at "S_MIC" and determine that 0.7 is greater than (GT) the value ( 0.5).
    The staff would then look at "R_MIC" and determine that 0.7 is less than (LT) the "R_MIC" value of >=1.
    Q7) Therefore the staff would enter "R" in the "Interpret" field?


    Example 3:
    For the record where Culture ID = 27 and Identification ID = 69 (record 2), it looks like the "Interpret" field values are in the wrong field??

    But this record brings up the question about WT & NWT.
    The susceptibility testing of Campylobacter and Arcobacter is MIC data and will be interpreted as S or R.
    If the MIC is between S and R, then will be interpreted as R.

    Wild-type (WT) and non-wild-type (NWT) instead of susceptible and resistant, respectively
    Don't understand this!
    Q8) If there is WT/NWT in the S, I, R ZoneDiameters fields, use WT/NWT instead of S/R??
    Last edited by ssanfu; 04-26-2019 at 05:48 PM. Reason: added question.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    14
    Q1) does the order of the fields "S_ZoneDiameter", "I_ZoneDiameter" and "R_ZoneDiameter" on the form make a difference?
    Yes, besides avoiding data entry mistakes as CultureID=27 and IdentificationID=69, it's easy to change breakpoints to all data to refer the same guideline.
    If the data was interpreted by different breakpoints, it will be calculated incorrectly.

    Q2) do the fields "S_ZoneDiameter", "I_ZoneDiameter", "R_ZoneDiameter", "S_MIC", "I_MIC" and "R_MIC really need to be visible?
    Yes, if guidelines are revised and it impacts to the data, for example, antibiotic breakpoints are changed, the table/form has to
    add a new drug pattern/create the new button. So, the fields "S_ZoneDiameter", "I_ZoneDiameter", "R_ZoneDiameter", "S_MIC", "I_MIC", "R_MIC"
    and "Reference" (guideline's name, edition/year of publication) have to be visible to know that which guidelines are being used.

    Q3) on the same form, the staff enters only enter: the drug, the "ZoneDiameter" and/or the "MIC" values?
    Yes, I want it to auto-calculate in interpret field whenever new Zone Diameter or new MIC values filled in.

    Q4) the "ZoneDiameter" value references "S_ZoneDiameter", "I_ZoneDiameter" and "R_ZoneDiameter" to get the value for Interpret field?
    Q5) the "MIC" values references "S_MIC", "I_MIC" and "R_MIC" to get the value for Interpret field?


    Example 1:
    For the record where Culture ID = 27 and Identification ID = 67 (record 1), the first drug is "Azithromycin" and The MIC entered = 20.
    The staff would look at "R_ZoneDiameter" and determine that 20 is greater than (GT) the value (13)
    The staff would then look at "I_ZoneDiameter" and determine that 20 is outside the "I_ZoneDiameter" value of 14-16.
    The staff would then look at "S_ZoneDiameter" and determine that 20 is GT the value 17.
    Q6) Therefore the staff would enter "S" in the "Interpret" field?

    Example 2:
    For the record where Culture ID = 27 and Identification ID = 68 (record 2), the first drug is "Ampicillin" and The ZoneDiameter entered = 0.7.
    The staff would look at "S_MIC" and determine that 0.7 is greater than (GT) the value ( 0.5).
    The staff would then look at "R_MIC" and determine that 0.7 is less than (LT) the "R_MIC" value of >=1.
    Q7) Therefore the staff would enter "R" in the "Interpret" field?
    Yes to Q4, Q5, Q6 and Q7


    Don't understand this!
    Q8) If there is WT/NWT in the S, I, R ZoneDiameters fields, use WT/NWT instead of S/R??
    Because of no a susceptibility breakpoint for some bacteria, Epidemiological Cutoff Values (ECVs) are determined instead as wild-type (WT) and non-wild type (NWT)

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    I think I got it!
    Well, definitely maybe...

    I had to make some changes to a couple of your table structures.

    1) In table "tblSusceptibility":
    - I changed the field "MIC" from TEXT type to Double.

    2) In table "tblAntimicrobial":
    - You are storing 2 items of data (actually 3) in 1 field. Therefore I added 2 fields : "S_ZD_WT" (TEXT) and "R_ZD_WT" (TEXT). These two fields holds values for the "Wild" - WT and NWT.

    For the S_ZoneDiameter and R_ZoneDiameter fields: I would have 3 fields: 1 to hold the "<=" and ">=" , 1 field to hold the value 17 and/or 0.25 and the 1 field to hold the WT values.
    It is easier to combine the fields than it is to split them.

    3) So then I had to modify the query "qrySusceptibility".
    4) An then I had to rebuild the form "frmSusceptibilitySubform" because it is in Datasheet view. (I never use Dataview - I like to have control of the form, so I use Continuous Form View and design my own "datasheet".)

    5) Also in the form "frmSusceptibilitySubform", I hid the first 3 fields/controls because they are PK/FK fields/controls and shouldn't be displayed.
    Then I set the Locked property to YES and the Enabled property to NO for all controls except 4 controls: "AntimicrobialID_FK", "ZoneDiameter", "MIC" and "Interpret".
    Those 4 fields (that are bound to the controls) are in table "tblSusceptibility" and the only fields you should be modifying in form "frmSusceptibilitySubform".



    I used the after update events of the text boxes for the ZoneDiameter and MIC controls.
    If you delete the value in the ZoneDiameter and MIC controls, the value in the Interpret control will be deleted.
    If you enter a value in MIC and you should have entered it into MIC, you will get an error message. This happens because there will not be values in the S_MIC and R_MIC fields.
    Same goes for entering a value in ZoneDiameter when you should have entered it in the MIC column.

    I did over an hour of testing, but you need to a lot of testing to ensure you are getting the proper results.
    Attached Files Attached Files
    Last edited by ssanfu; 04-27-2019 at 06:13 PM. Reason: forgot to Add dB
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help creating a Report from multiple criteria
    By sethcaudill_03@hotmail.co in forum Reports
    Replies: 3
    Last Post: 08-08-2018, 06:45 AM
  2. Replies: 3
    Last Post: 08-05-2015, 11:06 AM
  3. Replies: 3
    Last Post: 06-26-2014, 02:02 PM
  4. Replies: 2
    Last Post: 05-09-2011, 05:45 PM
  5. Search Button with Multiple Criteria
    By injanib in forum Forms
    Replies: 2
    Last Post: 01-12-2011, 01:21 AM

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