bfuchs
asked on
Need to rename a field and make query updatable.
Hi Experts,
Wondering if I have the following as my SQL
See attached.
Also looking for ideas on how to make this query updatable (on the Patients_Weekly_Medication s table only).
Untitled.png
Wondering if I have the following as my SQL
SELECT Patients_Medications.Medication AS HHAMed, Patients_Weekly_Medications.*
FROM Patients_Medications LEFT JOIN Patients_Weekly_Medications ON (Patients_Medications.PatientID = Patients_Weekly_Medications.PatientID) AND (Patients_Medications.Medication = Patients_Weekly_Medications.Medication);
Why when switching to datasheet view is the first column named "Medication" as opposed to HHAMed?See attached.
Also looking for ideas on how to make this query updatable (on the Patients_Weekly_Medication
Untitled.png
Have you checked the captions on both the query and the underlying tables
If you're joining Patients_Medications.Medic ation to Patients_Weekly_Medication s.Medicati on, then I don't see the need for the Join. The values in both of those fields will be the same - and it's the ONLY field from Patients_Medications you've included in the output - so just do away with the table you don't need to update.
ASKER
Hi Experts,
@John,
The in the table table the caption is "Medication", but that should not affect query, and what is the alternative?
@Scott,
What I'm trying to accomplish here is the following
Patients_Medications table stores info from patients medications in general, meaning what are they supposed to take in daily/weekly bases.
Then I have Patients_Weekly_Medication s which are storing what the patient actually took each week/day.
Now I want to create a form to enter data for Patients_Weekly_Medication s table.
And I need that every time the form opens it should have a pre-filled list of all medications belonging to that patient, and users will just click on each field yes or no next to the medication. (see attached).
What I'm trying to accomplish with this join is to auto fill the Patients_Medications.Patie ntID and Patients_Medications.Medic ation into Patients_Weekly_Medication s.PatientI D and Patients_Weekly_Medication s.Medicati on (done already similar things with previous versions of Access).
Let me know if this is clear.
Thanks,
Ben
Untitled.png
@John,
The in the table table the caption is "Medication", but that should not affect query, and what is the alternative?
@Scott,
What I'm trying to accomplish here is the following
Patients_Medications table stores info from patients medications in general, meaning what are they supposed to take in daily/weekly bases.
Then I have Patients_Weekly_Medication
Now I want to create a form to enter data for Patients_Weekly_Medication
And I need that every time the form opens it should have a pre-filled list of all medications belonging to that patient, and users will just click on each field yes or no next to the medication. (see attached).
What I'm trying to accomplish with this join is to auto fill the Patients_Medications.Patie
Let me know if this is clear.
Thanks,
Ben
Untitled.png
You're not going to be able to update that if the records don't exist in the Weekly table. You'd have to prefill that Weekly table with all the patient's medications, and give the user a Yes/No field to check them off.
Your Left Join would show ALL the medications for that Patient (I assume), but would only show values in those records in the weekly table where a record exists for the Patient + Medication. If that record doesn't exist, no records exist, and you wouldn't be able to update anything. You'd have to add a NEW record.
I would think a better interface would be a Master-Child setup where the Patient is the Master, and the Child is all the Patients meds. Your subform could have a combo that would indicate if they've taken those meds that week.
Your Left Join would show ALL the medications for that Patient (I assume), but would only show values in those records in the weekly table where a record exists for the Patient + Medication. If that record doesn't exist, no records exist, and you wouldn't be able to update anything. You'd have to add a NEW record.
I would think a better interface would be a Master-Child setup where the Patient is the Master, and the Child is all the Patients meds. Your subform could have a combo that would indicate if they've taken those meds that week.
And finally,
What is the purpose of collecting this data? If you are actually trying to track what was taken when, your table will not do that. It is not normalized and only allows for one dose per day. Many medications are taken multiple times per day and sometimes with different doses. A better, normalized design would store one dose per row. You would end up with a lot of rows but they would have fewer columns each and will be much easier to manipulate to get whatever stats you need.
What is the purpose of collecting this data? If you are actually trying to track what was taken when, your table will not do that. It is not normalized and only allows for one dose per day. Many medications are taken multiple times per day and sometimes with different doses. A better, normalized design would store one dose per row. You would end up with a lot of rows but they would have fewer columns each and will be much easier to manipulate to get whatever stats you need.
ASKER
@Scott,
@Pat,
I know its not being in normalized state, and indeed I started designing that way (see 3rd attachment), however user requested that entire weeks medication should be view-able/editable at once...
Thanks,
Ben
Untitled.png
Untitled2.png
Untitled1.png
You'd have to prefill that Weekly table with all the patient's medications, and give the user a Yes/No field to check them off.The problem with this is, that every time i will open the screen, all the medication records will have to be added/removed, very unpractical...
You're not going to be able to update that if the records don't exist in the Weekly table.Actually if i change the structure/relationships a little I can get that working, meaning if I have the weekly medications table store the unique ID of the Patients medications table then it works as described above, I can have all medications (for this patient) listed on the left, and next to it on the right all the fields of the weekly medications and will auto fill in the relationship field (which is Patients_Medications_ID) see attached.
I would think a better interface would be...For the interface I'm planning to use a calendar of each patients schedule, and user clicks on a date/patient and will open form with all medications of that patient and next to it all weekly medications check boxes as described above.
@Pat,
I know its not being in normalized state, and indeed I started designing that way (see 3rd attachment), however user requested that entire weeks medication should be view-able/editable at once...
Thanks,
Ben
Untitled.png
Untitled2.png
Untitled1.png
should be view-able/editable at once...
That doesn't seem be mean that you cannot normalize. Please make sure (for your own sanity) that the user understands the constraint he is making and that this design will not track by dose. There is absolutely NO point of keeping a single time field per day when multiple doses are possible. I would go so far as to make a document for the user and his manager to sign indicating that they know what they want and they understand that the schema will not actually satisfy any real world requirement.To populate a new "day" which should be the main form with the dose in the subform, you would copy the current schedule from the client record and append it for entry for the new day. Requery the subform and all the medications show up as a list This happens once and only once and that is in the AfterInsert event of the main form record. It never happens at other times.
ASKER
There is absolutely NO point of keeping a single time field per day when multiple doses are possible.No, I am planning to create as many fields as necessary per day, all under one week date (lets say Sun of each week).
See attached what the form will look like.
I understand this will make reports more challenging when it comes to figure out how many Med's are missing etc...however its doable, while making a form (where data is being added) should look like they're expecting, entire week in one screen is not, or at least I don't know how to do that.
Thanks,
Ben
Untitled.png
ASKER
Hi Experts,
So far I have programmed the form as follows, added the following to the weekly med form.
and this is what i use for opening that screen as criteria from patients schedule screen.
The data entry looks to be working fine, but the filter above does not work properly, meaning if a patient has 24 meds and I select 4 as taken this week, next time I open from schedule on this week I get to see 24 as expected, however if opening from a different week I only get to see 20 meds.
Wondering what am I missing?
Thanks,
Ben
So far I have programmed the form as follows, added the following to the weekly med form.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.PatientID = Me.PM_PatientID
Me.Medication = Me.PM_Medication
Me.Day = GetWeekStartDate([Forms]![FormSched].[VisitDate])
End Sub
and this is what i use for opening that screen as criteria from patients schedule screen.
stLinkCriteria = "[PM_PatientID] = " & Me.PatientID & " and ([PatientID]=" & Me.PatientID & " or PatientID is null) and ([Day] = #" & GetWeekStartDate(Me.VisitDate) & "# or [Day] is null)"
The data entry looks to be working fine, but the filter above does not work properly, meaning if a patient has 24 meds and I select 4 as taken this week, next time I open from schedule on this week I get to see 24 as expected, however if opening from a different week I only get to see 20 meds.
Wondering what am I missing?
Thanks,
Ben
ASKER
Or perhaps the forms record source needs a change...?
Thanks,
Ben
SELECT Patients_Medications.Medication AS PM_Medication, Patients_Medications.PatientID AS PM_PatientID, Patients_Weekly_Medications.*
FROM Patients_Medications LEFT JOIN Patients_Weekly_Medications ON Patients_Medications.Patients_Medications_ID = Patients_Weekly_Medications.Patients_Medications_ID;
Thanks,
Ben
No, I am planning to create as many fields as necessary per day, all under one week date (lets say Sun of each week).
See attached what the form will look like.
I would not do this. Normalizing the data is the correct solution. Anything else is just going to cause problems and extra work.I posted a sample of a bound denormalized form where you can use properly normalized tables but create a spreadsheet like form for the user to update. It might give you some idea of how to satisfy the user but still not create a nightmare for yourself.
Bound-Denormalized-Forms.pdf
BoundDenormalizedForm.accdb
ASKER
@Pat,
Waiting for user to return, will have a meeting and discuss all options, will let you know.
Thanks,
Ben
Waiting for user to return, will have a meeting and discuss all options, will let you know.
Thanks,
Ben
ASKER
Hi Experts,
Can someone explain the following mystery
I Had the query posted above being updatable (and still have it in a copy) and after adding/changing some fields to the Patients_Weekly_Medication s table it simply became none updatable.
Tried removing some fields, compacting/repair, copying the SQL from the working to the none working db... nothing helps!!!
Thanks,
Ben
Can someone explain the following mystery
I Had the query posted above being updatable (and still have it in a copy) and after adding/changing some fields to the Patients_Weekly_Medication
SELECT Patients_Medications.Medication AS PM_Medication, Patients_Medications.PatientID AS PM_PatientID, Patients_Weekly_Medications_Qry.*
FROM Patients_Medications LEFT JOIN Patients_Weekly_Medications_Qry ON Patients_Medications.Patients_Medications_ID = Patients_Weekly_Medications_Qry.Patients_Medications_ID;
Tried removing some fields, compacting/repair, copying the SQL from the working to the none working db... nothing helps!!!
Thanks,
Ben
Look at the data types of the fields in the table, especially the primary keys. If any are BigInt, that could be the problem. There are other issues with "newer" data types. Access added a setting in the back stage (I'm not exactly sure where it is) that makes the database recognize BigInt. HOWEVER, this actually changes the database format so that versions of Access prior to the advent of this setting (I think around 2 years ago), will not be able to read the database so everyone has to be pretty current with their versions.
This thread, like many you create, has several deviant thread paths which makes it difficult for us to help you and which will make it impossible for you to assign points fairly.
This thread, like many you create, has several deviant thread paths which makes it difficult for us to help you and which will make it impossible for you to assign points fairly.
ASKER
Look at the data types of the fields in the table, especially the primary keys. If any are BigInt,Datatypes of PK are Autonumber, and no Bigint used here, as mentioned I only renamed some fields, changed some datatypes from yes/no to short text and added new fields, can you see any of these causing this behavior?
This thread, like many you create, has several deviant thread paths which makes it difficult for us to help you and which will make it impossible for you to assign points fairly.Can you please clarify what are you referring to...
Just a recap... the 2 questions of this thread were..,
1- how to change the caption of that column which I didnt got an satisfied answer (and its not relevant anymore therefore I'm not focusing on that).
2- How to make this query updatable, and this is what I want to focus now.
You bough up the data modeling topic, which is very important to me here in this app, and I'm planning to open a new thread for discussing that, as after our meeting today, things only got more complicated, since users are looking for 4 records of each medication per patients, per day...
Thanks,
Ben
ASKER
Attaching a pic from the working and the none working queries.
Perhaps you can find the reason.
Thanks,
Ben
Untitled1.png
Untitled.png
Perhaps you can find the reason.
Thanks,
Ben
Untitled1.png
Untitled.png
Sorry Ben, There are some rabbit holes I will not descend into. This design violates accepted design practices and I am not going to help you implement it. Use at your own risk. This problem should be solved by a creative interface rather than by turning a table into a spreadsheet.
PS - I couldn't determine the problem from these pictures regardless. I would need to see the actual table definitions plus the queries that the pictured queries reference.
PS - I couldn't determine the problem from these pictures regardless. I would need to see the actual table definitions plus the queries that the pictured queries reference.
ASKER
Hi Pat,
Question- If I upload my app (In another thread as mentioned), would you be able to help me accomplish it in a normalized version?
Note- currently all this is working without having to create any records by code, user clicks on a button and this form pops up with only selected patients meds.
Thanks,
Ben
Untitled.png
This problem should be solved by a creative interface rather than by turning a table into a spreadsheet.See attached what the current requirement is for data entry...
Question- If I upload my app (In another thread as mentioned), would you be able to help me accomplish it in a normalized version?
Note- currently all this is working without having to create any records by code, user clicks on a button and this form pops up with only selected patients meds.
Thanks,
Ben
Untitled.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all participants!!