Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20

    A brief report

    Hello!
    I'm trying to make a report from the BDPerfil table that gives me the percentage of the "Sensível" texts that appears in each of the antimicrobiotics fields without the repetition of the Microorganism name. Like this:
    MicroorganismoGram_positivo | AMICACINA | AZTREONAM (...)
    A | 60% | 100%
    B | 100% | 50%
    C | 70% | 70%
    I think that is a sum of the "Sensível" incidence in each antimicrobiotics field for each Microorganism registered divided by the sum of "Sensível" and "Resistente" multiplied by 100.
    I simply don't know how to do this, I've already tried doing queries but I didn't have the result that I want. I want a simplified report, not a repetition of the table.
    Well, I hope that you understood me, Thanks for any help.
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In my view your problem is related to your database structure and I have not clearly understood what you want to do.
    You have identified lookup values for most of your fields.
    There are additional tables (not included) in your application. For example:
    Code:
     SELECT [Microorganismos Negativo].[Mircroorganismo] FROM [Microorganismos Negativo];
    Perhaps a table of antibiotics is needed and an observation table ---this antibiotic for this surgery --observation Negative etc.

    I think your efforts to simplify things to the 1 table in your database is a major obstruction to queries/reports.

    Good luck.

  3. #3
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20
    Well. What I'm trying to do exactly is a profile of the bacteria isolated in the microbiologic exams here in our lab.
    I made a form to add the exams, its a form that have the fields of the BDPerfil Table, I have to choose the "Gram" of the bacteria and then fill the name of the bacteria using a list from a query from a table that has a general list of bacteria., then just fill the lists of the antibiotics with "Sensísel"; "Resistente" or "Não testado".
    My report has to show me the sensibility percentage, bacteria per bacteria individually, for each of the antibiotics.
    If I have to change the structure, will I have to type the results of the exams all over again? =/
    Attached Files Attached Files

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The data you have provided doesn't have much to work with. To understand what you want and using the data you have provided, what will your results look like?
    Staphylococcus aureus | 0% | 0%
    Staphylococcus epidermidis | 12.5% | 0%

  5. #5
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20
    The lines should be the fields that has the name of the antibiotics and the columns the incidende of sensibility of the microorganisms to that specific antibiotic.
    Thats the 2015 report, I made that in a table in Microsoft Word manually. %S = sum of "Sensível"/Sum of "Sensível" + "Resistente"; NT is "Não Testado" means not tested, not tried I guess....
    E. coli Klebsiella Pseudomonas Enterobacter
    %S %S %S %S
    AMICACINA 50% 60% 0% 100%
    AMPICILINA NT 0% NT 0%
    AZTREONAM 0% 60% 0% 100%
    CEFALEXINA NT 60% NT 0%
    CEFALOTINA NT 60% NT 0%
    CEFTAZIDIMA NT NT 0% NT
    CEFOTAXIMA NT 50% NT 100%
    CEFTRIAXONA NT 80% NT 100%
    CEFEPIME NT 50% NT 100%
    CIPROFLOXACIN 67% 67% 0% 100%
    IMIPENEM 100% 100% 0% 100%
    GENTAMICINA 67% 67% 0% 100%
    MEROPENEM NT 100% 0% 100%
    PIP/TAZO 0% 100% 0% NT
    SMX/TMP 100% 67% NT 100%

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your database structure does not support this, you will have to change it. The first column above are field names. Field names do not appear to the user, not ever. The user only sees values that are stored inside a field, not the field name itself. You will have to create a table that contains one row for each of those values, then another table which contains the PK of that table, date tested, test result (Sensitive, etc), and whatever else pertains to the test of that value.

    Then you can start to report on that data, but not until then. You can do it manually, of course, but then we can't help with that.

    (Orange, you arrived at this point a whole faster than I did!)

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    aytee111,

    It's rare that a 1 table database is the optimal structure for supporting a business or segment of a business.
    I really wasn't sure of the OP' s requirement.
    As I see things after a few rereads, it appears there are several microorganisms and a number of antibiotics.
    Also there are "environments"( in my view) associated with testing and measuring the effectiveness of antibiotics against/controlling
    certain microorganisms.

    It seems like a typical experiment. You have some sort of hypothesis and you set up some tests. You have some expected outcomes and then you have observed outcomes. In this set up you have a microorganism and you "test" the effectiveness of a specific antibiotics to "kill"/reduce the negative affects of that microorganism.

    Just seemed like there was too much info for 1 table. And, as per many of my posts, it's always better to get a clear description of what you're trying to do; bounce it off others to ensure it is complete and clear; get a good data model; test it with mock data to validate the model; then move to physical database development. Jumping into physical database (Access or other) without a plan and/or model can be a very long, difficult journey.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Well said, and well written. There should be a place for posts that everyone should read first!

  9. #9
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20
    Ok, guys, Thanks for the tips. As you could see I know nothing about designing a Data Base.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you give us a clear description of what you are trying to accomplish in plain, simple English? Avoid jargon and pseudo-vba.

  11. #11
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20
    My english is not good. Sorry, but I'll try.
    I work at an Infection Control Comission of a hospital and we need to prospect every bacteria that "grows" in our ICU/ITU.
    So, every patient that has an infection, the microbiology laboratory collects a sample of blood, or any material that is suspected of being infected. and then tests the material.
    If it is infected, grows in the blood, or liquor brain, or scar fluid etc, an amount of bacteria (Gram positive that can be Staphylococcus, or Gram negative that can be klebsiella, all those are grouped at the DB), and that amount has to be tested again, this time against the action of certains antibiotics (Amicacina, Vancomicina, Ampicilina, also grouped in DB, all that in portuguese). If the bacteria are not able to grow and reduce their count in the sample under the action of the antibiotic, we say that the bacteria is sensible against that specific antibiotic,but if theres still bacteria growing, the bacteria is resistant.
    and what I was trying to do is gather all those antibiotic tests/exams, and make a sensibility profile of our ICU. Just say how many bacteria that grows from each kind of material is sensible for each antibiotic.
    Again, sorry for the bad english, and thanks for the attention.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Se você acha que seu inglês não é suficiente para a comunicação, uma coisa que você pode fazer é usar o Google Translate.
    Digite sua descrição em seu idioma e peça ao tradutor do Google para mostrar a tradução em inglês. Pode não ser perfeito, mas certamente deve ser suficiente para o diálogo.

    I think your English is better than many posters.

  13. #13
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20
    Man, I almost thought that you were a brazilian too. lol.
    I'm already doing this manually. When we finish the 2016 anual report, I'll work in a better BD design,and with better forms for any users to feed the BD and retrieve good information for our day to day work.
    Thank you =]

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure what you expect of the proposed database.

    I'm looking at your data and I note

    Code:
    SELECT DISTINCT BDPerfil.MicroorganismoGram_negativo
    FROM BDPerfil
    WHERE (((BDPerfil.MicroorganismoGram_negativo) Is Not Null));
    You have 7 unique gram negative items

    MicroorganismoGram_negativo
    Acinetobacter baumannii
    Citrobacter koseri
    Enterobacter aerogenes
    Escherichia coli
    Klebsiella pneumoniae
    Proteus vulgaris
    Pseudomonas aeruginosa

    and 4 Gram Positive

    Code:
    SELECT DISTINCT BDPerfil.MicroorganismoGram_Positivo
    FROM BDPerfil
    WHERE (((BDPerfil.MicroorganismoGram_Positivo) Is Not Null));
    MicroorganismoGram_Positivo
    Enterococcus faecalis
    Staphylococcus aureus
    Staphylococcus epidermidis
    Staphylococcus lugdunensis


    and 7 unique sectors

    Code:
    SELECT DISTINCT BDPerfil.Setor
    FROM BDPerfil
    WHERE (((BDPerfil.Setor) Is Not Null));


    Setor
    Alto risco
    UA
    UB
    UB4
    UC
    UTI Materna
    UTINeo

    and 3 material types

    Code:
    SELECT distinct BDPerfil.Material
    FROM BDPerfil
    WHERE (((BDPerfil.Material) Is Not Null));


    Material
    Ferida operatória Cesárea
    Ferida Operatória HTA
    Sangue


    Getting a report in the format you've shown may be a challenge, but you could get info onto a form if there was a specific or other question.'
    It really depends on the requirements.

    I can see various subjects involved (separate tables) but would need more info to establish relationships.
    Attached Thumbnails Attached Thumbnails InfectionAntibioticTesting.jpg  

  15. #15
    amaro.neto is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    20
    Whoa! That seems very difficult to do. But you figure things out very quickly, I'm impressed.
    Well, you actually know the main informations that we need: the sample site; the inpatient unit (sector); Patient type (if he is an adult or a child); If the infection is from our hospital or comes from elsewhere (infection type: if it is hospital acquired or not); microorganism organized by its gram; and the tested antibiotics.
    And that information had to be shown like that table I've posted above. That one shows the Gram negative microorganisms that grew from scar fluids of patients from a specific inpatient unit and their respectives sensibility against each one of the antibiotics.
    When I tried to make that DB, I thought that a register should have those information as fields to be filled, and just it. Never thought about multiple tables converging into a unique data. Buts that's because I know nothing about DBs.
    Thanks for the help, man!

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

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