Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

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
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Have you checked the captions on both the query and the underlying tables
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.
Avatar of bfuchs

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_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
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.
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.
Avatar of bfuchs

ASKER

@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
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.
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

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
Avatar of bfuchs

ASKER

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
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
Avatar of bfuchs

ASKER

@Pat,
Waiting for user to return, will have a meeting and discuss all options, will let you know.
Thanks,
Ben
Avatar of bfuchs

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_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
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.
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

Attaching a pic from the working and the none working queries.
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.
Avatar of bfuchs

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thanks to all participants!!