Forum Discussion
Binary master and Date child fields,
I have a database for animals in a zoo. Animal ID information (and lifelong data which won't change) is in an ID table. (Tbl_PC_Animal_ID) During its life each animal may have one or more health iss...
If I read this correctly, I think you have a design problem. "I am avoiding having a record for every animal for every drug for every day,"
That cripples the concept of a "database" by deliberately omitting critical information. Did any given animal actually receive the medication or medications that animal should get on any given day? You have no way to verify that and that means NONE of the data is reliable. The way you seem to be doing it doesn't actually save any significant amount of data entry either, does it? You have to check off the days on which "some animals" were given "some medications" by "some employee", so there's still significant data entry.
I believe the problem with the query could be resolved by some appropriate modifications to the SQL, but in the absence of reliable data, it hardly seems worthwhile.
You need a properly designed table in which your users record the date, the ID of the animal, the ID of the employee who administered the medication, the ID of each medication that animal received, and the dosage that animal received of each medication. You can manage an interface to minimize the effort required to do so, but without it, your data is virtually useless because it assumes "if something was scheduled to happen, someone must have done it, right?"
- I've been advised I was unnecessarily harsh in criticizing the approach here. Reviewing my comments confirms that. I apologize.
The advice, however, regarding the appropriate approach to table design still seems valid to me.
A table of "Medications Administered" is advisable.
It should have fields for the AnimalID, MedicationID, AdministeringEmployeeID and the AdministeredDate. The dosage may or may not be required. If the dosage for a given medication is constant, and if that dosage is identified in the Medications table, it might not be necessary to repeat it in the MedicationsAdministered table.
However, if the dosage varies by animal (two animals of different species require two different dosages of the same medication), then it must be recorded with the other information in the record.
Again, I apologize and hope that doesn't impact you ability to resolve the problem.- Thank you for replying, many of your criticisms are valid and others are because I didn't explain things fully in an effort to keep my post short.
The table of medications (TblRx) has the following fields. ;
Rx Key(Home key) Issue Key (which links to a list of health problems which in turn links back to the animals ID) Start date, Drug, Route, Frequency, Finish date, Comment, Follow up action, follow up date, follow up completed.
Each record in TlbRx is essentially a prescription, or recommended course of one drug supplied by me as veterinarian. This way has served me well for years but I saw the verification of delivery as a separate function that did require daily records, and would need to be entered by staff.
At my previous position the delivery of medications was recorded by veterinary nurses on paper, (you sometimes have to let people do things their way).
My current zoo is smaller so I was going to include the recording of the drug administration in the database, and yes, initials of the administering staff member, or at least the staff member doing data entry would be a good idea. I will include that in the verification table which will now have the following fields; CheckOffRxKey, (Home key) RxKey, (to link to Rx table) RxDate, RxCheckOff, Initials.
I think I can resolve this if I can get the typed-in-date/Qry expression to be data type date instead of a binary. Alternatively there may be a better approach.
Thanks again for your reply.- You wrote earlier:
> That date is held in expression1as a binary. I can't make this other than a binary. (or if I can please let me know how)
This is *very* strange. Can you post expression1?
In general the best course of action is starting with a correct database design, then build expressions and more complexity on top of that, not compromising db design for an unruly expression. I think together we can slay this one.
Another thought about speeding up data entry: would it help to have barcodes for animals, employees, and medications? Then entering a record is mostly zapping some barcodes.
