Results 1 to 2 of 2
  1. #1
    Ginger is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    15

    Show Data Based on Criteria

    I need to show the data on the MIC and Interpret fields on the report by creating 3 columns, depending on Specimen Type field.
    If the Organism field is negative, the MIC and Interpret fields are blank.
    Is it possible?

    Thank you in advance for any help you can provide!

    Report
    Sample ID GC12
    Sputum Urine Wound
    Antimicrobials MIC Interpret MIC Interpret MIC Interpret
    Azithromycin 0.047 S 0.094 S
    Cefixime <=0.016 S <=0.016 S
    Ceftriaxone 0.25 S 0.25 S
    Ciprofloxacin 0.5 I 1 R
    Gentamicin 2 S >=32 R
    Penicillin 8 R 8 R
    Tetracycline 6 R 12 R

    Form


    Sample ID Specimen Type Organism Antimicrobial MIC Interpret
    GC12 Sputum Positive Azithromycin 0.047 S
    GC12 Sputum Positive Cefixime <=0.016 S
    GC12 Sputum Positive Ceftriaxone 0.25 S
    GC12 Sputum Positive Ciprofloxacin 0.5 I
    GC12 Sputum Positive Gentamicin 2 S
    GC12 Sputum Positive Penicillin 8 R
    GC12 Sputum Positive Tetracycline 6 R
    GC12 Urine Positive Azithromycin 0.094 S
    GC12 Urine Positive Cefixime <=0.016 S
    GC12 Urine Positive Ceftriaxone 0.25 S
    GC12 Urine Positive Ciprofloxacin 1 R
    GC12 Urine Positive Gentamicin >=32 R
    GC12 Urine Positive Penicillin 8 R
    GC12 Urine Positive Tetracycline 12 R
    GC12 Wound Negative

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review this tutorial on CROSSTAB query http://allenbrowne.com/ser-67.html

    An alternative is to emulate CROSSTAB with IIf() expressions and GROUP BY. Following shows calculation for Sputum. Replicate for the other two types.

    SELECT SampleID, Antimicrobial, Max(IIf([SpecimenType]='Sputum',[MIC],Null)) AS SputumMic, Max(IIf([SpecimenType]='Sputum',[Interpret],Null)) AS SputumInt
    FROM Data
    GROUP BY SampleID, Antimicrobial
    HAVING NOT Antimicrobial IS NULL;
    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: 2
    Last Post: 11-23-2015, 06:25 PM
  2. Show/Hide Fields based on criteria.
    By jtm013 in forum Queries
    Replies: 6
    Last Post: 08-14-2014, 08:05 AM
  3. Replies: 5
    Last Post: 05-17-2013, 10:45 AM
  4. show / hide columns based on criteria?
    By stevepcne in forum Access
    Replies: 1
    Last Post: 11-18-2011, 02:49 PM
  5. Replies: 1
    Last Post: 06-12-2011, 07:08 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
  •  
Other Forums: Microsoft Office Forums