The issue would seem to center around Prescribe and it's relationships--the very part that you don't explain in your first post.
What is the purpose of this table? (I'd think that each record would represent a drug or therapy that can be selected as a prescription for a patient, but then the link to Record makes no sense.)
Why does it link to Patient_Prescribe by Date_Modified?
Since any number of records in Patient_Prescribe can have the same date and the same is true for Prescribe, I don't see how this can be of use to you.
Each Patient may have several Prescriptions. The Prescription may change after 1 or more days, hence knowing the modification date is important. However, the Record of drug given is daily. So you might have for a single Patient, 2 Prescriptions: a) lasting 1 day (ie. 1 prescription, 1 record), b) lasting 3 days (ie. 1 prescription, 3 records).
I had assumed that if you choose a patient while on Record, you would need the Prescription dates for that patient to be pulled up in a field beside it. And to obtain this contextual value list I'd need another table occurance where the Prescription dates would be listed for the Patient chosen. However, I admit, I'm not quite how to use relationships !
Knowing the modification date IS important, but it makes no sense to use like you have as a match field in a relationship. It will match to many records for other patients that happen to have the same date.
What does one record in Prescribe represent?
1 record in Prescription is the drug/dose for 1 patient on that day. As mentioned the Prescription may be used for 1 or more days and for each day the actual delivery of drug and dose will be recorded (in Record; I'm probably better using the term 'Input' so as not to confuse with the generic term 'record').
Sorry to keep asking questions like this but by answering them, I get sufficient info to explain what you need to change to get this to work.
If one record in prescription is the drug/dow for 1 patient "on that day", what purpose is served by the Patient_Prescribe table?
And with your relationships as they are currently set up, there is no link from Prescribe to a specfiic record in Patients.
It sounds lik you should have:
Patients::PatientID = Prescribe::PatientID
Prescribe::PrescribeID = Records::RecordID
Patient_Prescribe is labeled and set up in relationships as though it is the join table in a many to many relationship between Prescribe and Patients, but if one record in Prescribe is one dose of one prescription for one patient on a specific date, then you do not have a many to many relationship.
I think you DO have the need for a Many to Many relationship, but not for the tables as you have currently set them up. If you had a table that served as a master list of drugs--one record for each drug that might be prescribed, then you would have use for a join table in a many to many relationship between Patient and such a master list table.
I've put back the relationship diagram to that you suggest. How would I use this to get value lists for popups: Patient (selection) and Prescription (selection based on what Patient is chosen). I'd planned to put these on 'Record' because input is a long list needing portrait layout so a portal wouldn't easily fit if I instead inserted one on a Patient tab to draw data from Prescription and Record. However, if there had been space, would the latter have been better?
Your comment on the end: Are you suggesting instead having: Patient < Join table > Drug master list?
I take it there would still be Patient < Prescription < Record (Input) relationships.
You may not actually need any pop up for selecting a patient for a given record. In fact, if there is any pop up, it would be to select a record from Prescribe, which in turn selects the patient via the relationship between prescribe and patient. You have a number of options.
You can place a portal to Prescribe on your patients layout. A second portal, linked via scripts and an additional relationship can allow you to create linked records in the Records table directly in the portal.
As to my last comment, I can't tell you what you need as I am not the one using the database. IF you need a master list of all medications that could be prescribed with data about each such medication recorded. Such a list could be linked to your patient with a join table as you would have many patients receiving the same medication and a given patient might receive multiple medications--which produces the "many to many" relationship that requires such a "Join" table. If you find that you need that, your current Prescribe table could easily be set up as such a join table by linking it to the master list table.
Re. portals, I can see how they could deal with my need, but can they be set up with fields as rows, records as columns. This would fit the screen I'm planning for but I've never seen a portal like this.
Not with a single portal.
You can put in a row of one row portals and resize the portal row to be several fields tall so that you can arrange your fields from one record in a column. The first portal would specify "initial row 1". The portal next to it: "initial row 2" and so forth. This can create a few design issues for your layout that require some scripting to handle.