Binary master and Date child fields,

[Vere_Nicolson]

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 issues which are recorded in another table (Tbl_Issues) with a  1 to many relationship to the 1st table on ID-Key.

In response to each health issue the animal may receive one or more drugs over varying periods

Drug treatments are recorded in third level table (TblRx) linked by the Issue ID key from Issues table.

Treatment records have a start and end date which may be days or months apart. I am avoiding having a record for every animal for every drug for every day, but I do want a way for staff to acknowledge, and check if an animal has been treated on each particular day, (in a separate very simple table called TblCheckOffCompletedMeds, with just 4 fields, home key, RxKey, Date and completed yes/no)

I have a query (Below) which has animal ID, drug name , start date, end date etc to draw animal ID and drug treatment data together, having [Date to check off] in the criteria for this Query makes Access prompt to type in a date. 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)

SELECT Tbl_PC_Animal_ID.Species, Tbl_PC_Animal_ID.[House name], Tbl_PC_Animal_ID.[Microchip~tag~physicalID], Tbl_Rx.Drug, Tbl_Rx.Frequency, Tbl_Rx.Rx_Key, [Date to check off] AS Expr1
FROM (Tbl_PC_Animal_ID LEFT JOIN TblVetIssues ON Tbl_PC_Animal_ID.ID_Key = TblVetIssues.Animal_Key) LEFT JOIN Tbl_Rx ON TblVetIssues.Issue_Key = Tbl_Rx.Issue_Key
WHERE (((Tbl_Rx.[Finish date])>=[Date to check off]) AND ((Tbl_Rx.[Start date])<=[Date to check off])) OR (((Tbl_Rx.[Finish date]) Is Null) AND ((Tbl_Rx.[Start date])<=[Date to check off]));

This query populates a form which shows only animals on treatment on the date entered as [Date to check off]. No data is entered by the master form but it allows inputter to see which animal drug combination is to be checked off in the subform.

The subform is enters records to  TblCheckOffCompletedMeds.

My problem is that I can’t get linked master/child fields to work due to one being a date field and one being a binary field.

This leads to records from other days showing up in the subform. 

Can anyone help with this or suggest the much simpler way that I have probably overlooked.

Cheers

Vere Nicolson

[#2]

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?"

[#3]

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.

[#4]

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.

[#5]

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.