Need to rename a field and make query updatable.

Hi Experts,

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);

Open in new window

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_Medications table only).
Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Have you checked the captions on both the query and the underlying tables
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're joining Patients_Medications.Medication to Patients_Weekly_Medications.Medication, 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.
bfuchsAuthor Commented:
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_Medications which are storing what the patient actually took each week/day.

Now I want to create a form to enter data for Patients_Weekly_Medications 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.PatientID and Patients_Medications.Medication into Patients_Weekly_Medications.PatientID and Patients_Weekly_Medications.Medication (done already similar things with previous versions of Access).

Let me know if this is clear.

Thanks,
Ben
Untitled.png
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
PatHartmanCommented:
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.
bfuchsAuthor Commented:
@Scott,

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
PatHartmanCommented:
should be view-able/editable at once...

Open in new window

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.
bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
Hi Experts,

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

Open in new window


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

Open in new window


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
bfuchsAuthor Commented:
Or perhaps the forms record source needs a change...?
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;

Open in new window


Thanks,
Ben
PatHartmanCommented:
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.

Open in new window

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
bfuchsAuthor Commented:
@Pat,
Waiting for user to return, will have a meeting and discuss all options, will let you know.
Thanks,
Ben
bfuchsAuthor Commented:
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_Medications table it simply became none updatable.

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;

Open in new window


Tried removing some fields, compacting/repair, copying the SQL from the working to the none working db... nothing helps!!!

Thanks,
Ben
PatHartmanCommented:
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.
bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
Attaching a pic from the working and the none working queries.
Perhaps you can find the reason.

Thanks,
Ben
Untitled1.png
Untitled.png
PatHartmanCommented:
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.
bfuchsAuthor Commented:
Hi Pat,

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
PatHartmanCommented:
You really don't need me to do this for you.  You just need some ideas on how to display the data.  Since this is for data entry, you don't have to support more than one day at a time.  Make a form that shows one day for the patient.  Use a subform that lists Drug and as many times as are needed.  I agree that most drugs won't be taken more than four times per day so it seems "reasonable" to allow four doses on one line.  However, what, exactly would you need to do should you find one drug that needs to be taken every two hours during awake time?  See if you can talk to a pharmacist to get a sense of what he sees for odd instructions and multiple times per day.  Keep in mind that any time you fix a specific occurrence count, you are opening yourself up to future problems.  What exactly are you going to do should this situation arise?  Maybe two subforms would make sense.  The left one would list all the drugs and when you click on a specific drug, the right form shows the times and doses.  This is probably what I would go with.

Any other requirement is for reporting and reporting can be done using crosstabs to make things look like spreadsheets.

Here's a picture of one form I used in an insurance application.  Endorsements are listed on the left.  Upper right shows details of specific endorsementThreeSubforms.JPG.  Lower right shows data required to fill in the endorsement form.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks to all participants!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.