A brief report

[orange]

There is a difference with the storage of data and the presentation/display of data.
Raw data is stored in tables. Manipulated, calculated, modified data (and raw data) can be displayed by means of queries, forms and or reports...

The structure of the database, tables and fields is all based on/derived from the business facts/rules. And these facts/rules are best gathered from a description of the business.
Can you give a 1-2 line description of the things involved.
For example:
-SampleSite
-Sector
-PatientType


-Material
-GramPosNeg
-Antibiotic
-any other relevant info/thing/subject you can think of

I'm sure there is another table that represents the actual "sample" (perhaps it is material??).

Also, can you provide a little more detail on the processes involved. I see these as a start

-collecting sample
-initial test
-sector assignment
-microorganism identification
-microorganism-antibiotic test
-sensibility assignment

Do you have multiple replicates when testing an antibiotic vs a microorganism? How relevant is that in your "business".

I can help you build a data model that we can test, but will need info from you.

Good luck.

[amaro.neto]

What I meant of samplesite was just if the sample was from the blood of the patient, or if it was from a scar tissue or a scar fluid, urine, feces, cerebralspinal fluid (brain liquor), so I think that material and samplesite could be the same thing.
The Sector is where the patient is in the hospital. If it is the ICU, or any of the infirmary, or the first-aid post.
The patient here are adult women and newborns (our hospital is a maternity hospital), so we have to differentiate their use of antibiotic, this is why we have the PacientType.
GramPosNeg - In infectology, treatment of bacteria infection diseases are mainly prescribed based on the type of the bacteria. If the Gram is positive, the doctor should prescribe a kind of antibiotic, if the gram is negative, theres another kind of antibiotic to prescribe. but, even in the same Gram group, theres bacteria that responds better against specific antibiotics than others. That why we should, in addition to identifying the gram, identify the name (kind) of bacteria.
Antibiotic - Its the medicine for infectious diseases, they kill bacteria, but not every bacteria. thats why theres a lot of antibiotics, as I said, theres a specific antibiotic for each of bacteria. There's bacteria that resists the action of certain antibiotic. And bacteria can, if it is subjected to antibiotics for a long time, get resistance. That's why we need to prospect, every year, the results of the microbiological tests, to see if the resistance of the bacteria is growing. The use of the antibiotic depends on the resistance or the sensibility (I dont know if I'm using the proper word to translate "sensibilidade" the antonym of resistance) of the bacteria, If we understand through the sensiblity profile that the sensibility is reducing/resistance growing, we have to change the treatment scheme. Because of 2 things: to not create superbacteria and to have a more effective treatment for infection diseases.
The collect of the sample depends of the material. you have to collect blood if the pacient is suspicious of blood related infection, or lung infection like pneumonia. if the patient is suspicious of havin urinary tract infection you collect urine. If he is suspicious of meningitis you collect cerebralspinal fluid (brain liquor). If the patient made a cirurgy(cesarean section for example), and the scar left by it is suspicious of being infected, you collect the fluid from the scar. The sample goes in a specific container proper to grow bacteria, but if the sample is clean, should not grow bacteria in it. (yes, this whole process has to be done by very specialized people)
Then the lab tests the sample in a machine that speeds up the process of multiplication of bacteria. If the patient is clear of infection, the sample will be clean of bacteria, but if the patient is infected, will grow something. If grows something, the machine will identify it (If gram is positive or negaive, the name of the bacteria), and tests it against the antibiotics. This test is not random, there are guidelines for that (clsi.org), each bacteria should be tested against a specific group of antibiotics that is determined by these guidelines. After the machine do its job, it will print a report of the sample. in the report is written the name of the patient, the sector where he is, the material of the sample, the date it was collected, and lists the antibiotic which have been tested and says, for each antibiotic, if it was sensible or resistant.
Then I pick that print and feed some excel sheets to compile all the information and, finally, make that table in a word document. The excel sheets are divided by patient type (if it is adult or newborn)and by the material and by the type of infection. Like that table I've posted that shows the sensibility percentage of the bacteria that grew in the samples of scar fluid from adult patients that had hospital acquired infection.
I think I've not forgot anything.

[orange]

Good stuff. I've been out for a few hours.
Here i s a draft model, we'll need to see what attributes(fields) are required. Those that can be looked up in another table etc.




Here is a list of the rules represented in the draft model
Re: MaterialAntibioticTesting01.erwin model

1 or more Materialsamples are taken from a Patient
Each Materialsample is from 1 SampleSite
Each Materialsample is from 1 Patient
Each Materialsample is from 1 Sector
Each Materialsample is identified by a unique MaterialID
Each Materialsample holds info about the CollectionDate, the SectorID, theSampleSiteID and a Patient code
The InitialGramTest of a Materialsample has an InitialTestDate and the resultant Gram testResult
Following the InitialGramTest the Materialsample may undergo 1 or more Antibiotic tests
Each Antibiotic test is identified by a unique AntiBioSampleID
Each Antibiotic test holds info about the AntiBioTestDate, a SensitivityResistanceResult and the InitialGramTest
Each Antibiotic is identified with a unique identifier and its name
A separate table identifies SensitivityResistance

These are preliminary and can be modified before any testing of this proposed data model.

[amaro.neto]

Hey, guys! I've been out for the carnival holidays and festivities.
I don't know if you are still interested in this thread, but anyways, the antibiotic sensibility test is all based on the CLSI guidelines. Actually, the correct terminology in english is antimicrobial susceptibility. I'm not a specialist in microbiology, sorry, but, as far as I know, first the tests identifies the Gram (For example, Gram positive), then Identifies the name of the microoganism (for example a gram positive bacteria like Staphylococcus aures), and then, based on the name, tests the antibiotics (for S. aureus, vancomycin has to be in the susceptibility test among others antibiotics according to the guidelines).
Thanks for your dedication. All this seems very interesting.

[amaro.neto]

Hey! I kind of forgot about this project in that day, because I couldn't study it and learn how to build it. But I got it back a month ago, and I've being studying a bit about relational databases, and I tried again.
And I made those tables relationships on access: . But I'm getting the error: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." when I try to add an register through "Paciente" table.
Any help would be very valuable. Thank you!