I need some help with making a report work correctly

[DPROMICS]

Situation: I have an Access App running that works
correctly (mostly).

In this app I have a report that
displays/prints the current/most recent clinic
visit data for the currently selected patient.
At least it is suppose to.

The report will print the data for every
patient's most recent clinic visit unless I
force it, by adding some "code" in to the query
to force it to prompt for an index number (to
prompt for a patient number (patient demographic
table index number).

There are two tables. A patient demographics
data table and a patient clinic visits data
table. The patient demographics table has a
one to many relationship with the patient
clinic visits data table. For each patient
there can be/are multiple clinic visits data
entries.

The query:

SELECT DISTINCTROW [Tbl_Coumadin_Clinic].[PT_DB_Number], [Tbl_PT_Demographics].[PT_Name_Last], [Tbl_PT_Demographics].[PT_Name_Prefix], [Tbl_PT_Demographics].[PT_Name_First], [Tbl_PT_Demographics].[PT_Name_Middle], [Tbl_PT_Demographics].[PT_Name_Suffix], [Tbl_Coumadin_Clinic].[Clinic_Visit_Date], [Tbl_Coumadin_Clinic].[Medication], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Mon], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Tue], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Wed], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Thu], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Fri], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Sat], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Sun], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Mon], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Tue], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Wed], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Thu], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Fri], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Sat], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Sun], [Tbl_Coumadin_Clinic].[Next_Lab_Date], [Tbl_Coumadin_Clinic].[Next_Visit_Med_Date]

FROM Tbl_PT_Demographics INNER JOIN Tbl_Coumadin_Clinic ON [Tbl_PT_Demographics].[PT_DB_Number]=[Tbl_Coumadin_Clinic].[PT_DB_Number]

WHERE (((Tbl_Coumadin_Clinic.Clinic_Visit_Date)=(SELECT Max(Clinic_Visit_Date)

FROM Tbl_Coumadin_Clinic T WHERE T.PT_DB_Number = Tbl_Coumadin_Clinic.PT_DB_Number) ) );

When the report is selected to be run there is a
currently selected patient. So the Index in to
the patient demographic data table is set to a
value. Is the problem I must somehow "pass"
this number to the query or at least somehow
make it available to it?

I will greatly appreciate any help. coding/methods examples, suggestions, etc.

 

[lupins46]

It sounds like you are are trying to run this query while viewing the table. Access has no mechanism for identifying the current record selected in a table datasheet and just processes all the records. So you cannot do what you are trying to do.

For this type of requirement, you must be viewing the data through a form and in your query should refer to the value of a control in a the form
( WHERE T.PT_DB_Number = forms!myformname!mycontrolname)

In more general terms, you should think of tables purely as where data is stored. Never view/add/delete/modify data through a table datasheet. Always use a form, as you can build in much more control over the integrity of the data.

 

[BoxHead]

DPROMICS,

There's an article that's referenced in this forum fairly often and it's worth reading for your situation.

http://r937.com/relational.html

It looks like you've set up separate columns for each coumadin dose over two weeks. I've known people to get the med for 6 months and that would make for a whole lot of columns. Beyond that, it would seem that in practice, a new record is required for each visit and 13 of the 14 columns in the table are left blank.

There are more dependable ways to capture and use this data to ensure its accuracy.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[DPROMICS]

lupins46

Actually, the command button to print this report is on a subform where the current clinic visit's data has been or will be entered.

There is another command button that also will cause this same report to be printed while on a form that displays all of the patient's clinic visits through a list box.

In both situations a patient has been selected from a list of patients. So the patient's index in to teh patient's data table is "known". Hence, this report should be possible to implement. I just haven't been able to figure out how to make it work correctly.

BoxHead

Actually, typically a patient is perscribed a coumadin or waraphin doseage that typically will repeat after two weeks... For example for the first week a patient is told to take a 1mg dose on Monday, Wednesday, and Friday. The other days of then week they will take a 2mg tablet. In this case the same doseage schedule repeats after the first week. The mg amount per day can very from day to day and may extend over a two week period. Even if the patient is only seen once a month or less than that. The goal is to have the patient's reach a certain level of blodd thiness. This measure of blood "thickness" or thinness" is called the INR.

Even though the table has fields for three weeks of a medication doseage schedule the Application is now only using two week doseage schedule.

I'm only the computer person. This is how the clinical staff - Docs, Nurse PRactitioners, etc. want the program to work based on their clinical practice and experience.

Best regards to both of you.

 

[lupins46]

then the syntax you want is:
WHERE T.PT_DB_Number = forms!mainformname!nameOfSubformcontrolOnmainform.FORM!controlnameOnSubform)

 

[DPROMICS]

lupins46

OK. I knew there had to be a method of doing this. But, it looks like this is beyond the several Access books I have and/or I missed an examplke of this in one of them.

Or may be I need to reread the sections in teh books on reprots again. But this time more thuroughly. :>).

I'll give it a try and post the results.

Best Regards and TNX ALL for help.

 

[DPROMICS]

lupins46

Still doesn't work. However, probably a syntax issue(?) in the query. More likely my failing than your suggestion.

1) The main form is called: Frm_ECMR_Main

2) There are tabs on the main form. The tab of interest
is: Tab4_Coumadin_Clinic

3) On teh above tab of the main form there is a command
button. The command button is called:
Cmd_Print_Coumadin_Doseage_Report

Based on the synbtax you suggested of:

FROM Tbl_Coumadin_Clinic T WHERE T.PT_DB_Number =
forms!nameOfSubformcontrolOnmainform.FORM!
controlnameOnSubform

I tried the following:

FROM Tbl_Coumadin_Clinic T WHERE T.PT_DB_Number =
FORMS!Frm_ECMR_Main!FORM!
Cmd_Print_Coumadin_Doseage_Report

OK. It didn't work. My gut feeling is that my syntax
is not correct. Given the form layout there obviously
isn't any subforms. So my interpretation of the syntx
isn't correct. Yes, I tried a few variations
eliminating reference to a subform. Nothing worked.

OK.

1) What is the correct syntax?

2) What books, web sites, etc. on Access, contain
examples of these types of queries?

Best Regards

 

[lupins46]

You need to know:

1. the name of the main form

2. the name of the subform CONTROL on the main form. This might be the same as the name of the subform, but it might not be. (It is important to note that when you use the toolbox wizard you do not add a (sub) form to a mainform directly, you add a subform control to the main form and then set the subform control source property to the name of the form you want the control to contain.)

3. the name of the control on the subform that you want to match to

The name of the subform itself is not required and the use of the tabbbed control makes no difference to anything.

So if the answers were:

1.Frm_ECMR_Main

2. child1

3. txtPT_DB_Number

you would have:

= forms!Frm_ECMR_Main!child1.Form!txtPT_DB_Number

 

[DPROMICS]

lupins46

I still do not follow your explanation nor can I get the part of the query you suggested to work.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
You suggested:

1. Frm_ECMR_Main - OK I follow this.

2. child1 - I don't understand what this is.

3. txtPT_DB_Number - what is the txt?

you would have:

= forms!Frm_ECMR_Main!child1.Form!txtPT_DB_Number
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Again here is the pertinent information:

1) The patient's demographic table is called:
Tbl_PT_Demographics.
The primary index in to that table is:
PT_DB_Number.

2) The patient's Coumadin Clinic Visits table is called:
Tbl_Coumadin_Clinic.
It has a field in it called: PT_DB_Number

3) There is a one to many relationship between the
Tbl_PT_Demographics and the Tbl_Coumadin_Clinic.
The one to many relationship is based on the
PT_DB_Number field in the patient's demographics
table and the Coumadin Clinic table.

3) The main form is: Frm_ECMR_Main

4) There is a command button on the main form.

5) The command button name is:
Cmd_Print_Coumadin_Doseage_Report

6) There is also the "same" command button, on a subform
that is on the main form. The subform is called:
Frm_Coumadin_Clinic_Current_Visit_Subform. The command
button "calls" that same VB code that invokes the report.

OK. Given the above what should the query code look like?

- When invoked from the command button on the main form?

- When invoked by the command button on the subform?

Best Regards

 

[lupins46]

The only bit of your post I have addressed is the 'Where 'clause in your query.
This is what i said in my last post:

'You need to know:

1. the name of the main form

2. the name of the subform CONTROL on the main form. This might be the same as the name of the subform, but it might not be. (It is important to note that when you use the toolbox wizard you do not add a (sub) form to a mainform directly, you add a subform control to the main form and then set the subform control source property to the name of the form you want the control to contain.)

3. the name of the control on the subform that you want to match to

The name of the subform itself is not required and the use of the tabbbed control makes no difference to anything.'

You have given the the name of the main form(Frm_ECMR_Main), but i do not know what the answers are to 2 and 3 so in my reply I made up some names to illustrate the syntax.
You must use your own names for these two items.

I have shown earlier the general form for the syntax:
WHERE T.PT_DB_Number = forms!mainformname!nameOfSubformcontrolOnmainform.FORM!controlnameOnSubform

 

[DPROMICS]

lupins46

You have provided some clarification for me. I'll work on this.

This project has been interesting but has become far too time consuming. Its a freebee effort on my part. I will see NO money out of this. Nor did I start this project with that in mind. I started it as an effort to help get my wife out of the practice's office earlier than 7:00 to 8:00 PM. She gets to work at 7:00 AM! She is a Nurse Practitioner in a busy small group cardiology practice. Yes, the practice really needs to invest in a real EMR - Electronic Medical Record system. But for many reasons, including the very high cost, they can't do that for the near future. So I am trying to help as best I can with my programming efforts. The program is being used in the practice. Its not finished but far enough along to be of some help to the dcotors, nurse practitioners, nurses, MAs, etc.

So, that said. I appreiciate all help from everybody. And their patience with my sometimes slow to grasp mind as well.

Best Regards

 

[DPROMICS]

OK, lets try this again.

I know, I know, there may be many that think I am dense, dumb, stupid, or whatever . . . and may be they are correct. But, I really need to get this issue resolved and working correctly in my Access app so that I can finish this freebee project and move on to something else that I CAN bill for.

The Situation: I have an Access App running that works correctly (mostly). Data entry and display works as desired. A needed query doesn't. That is the biggest headach that is currently holding up finishing this project.

The Acess app:

In this app I have a report that displays/prints the current/most recent (read last) clinic visit data for the currently selected patient. At least it is suppose to.

The report will print the data for every patient's most recent clinic visit unless I force it, by adding some "code" in to the query in the last WHERE statement at(TBo_PT_DB_Number) of the query, to force it to prompt for an index number in to the patient demographic table.

The way the program, data tables, etc. are set up:

There are two tables. A patient demographics data table (Tbl_PT_Demographics) and a Coumadin Clinic Visit data
table (Tbl_Coumadin_Clinic). In the Patient Demographic Data Table the index in to that table is called: PT_DB_Number. The patient demographics table has a one to many relationship with the Coumadin Clinic Visit data table. In the Coumadin Clinic Visit table the index in to that table is called: Coumadin_Clinic_Visit_Index. There is also an entry in that table called: PT_DB_Number. The one to many relationship between these two tables is set by [Tbl_PT_Demographics].[PT_DB_Number] to [Tbl_Coumadin_Clinic].[PT_DB_Number]. For each patient in the patient demographic data table there can be many Coumadin Clinic Visit records for that patient in the Coumadin Clinic Visit data table.

There is a main form (Frm_ECMR_Main) which has Tabs on it. The first Tab is labled Patients. On this Tab one can select a patient from a list box that displays all patients in the database. There is another Tab on the main form that is labled Coumadin Clinic. On this tab there is a list box that displays the currently selected patient's past Coumadin Clinic visits. On this tab of the main form there is a subform where the patient's current Coumadin Clinic visit data is entered. The subform is called: SubFrm_Coumadin_Clinic_Current_Visit. The subform box (control) name on the main form and the name of the subform are the same.

On the Coumadin Clinic Tab of the main form there is a command button labled: Print Current Clinic Visit. The command button's control is: Cmd_Print_Current_Coumadin_Clinic_Visit_Report. On the SubFrm_Coumadin_Clinic_Current_Visit sub form there is also a command button that is labled: Print Current Clinic Visit. It calls the same command (Cmd_Print_Current_Coumadin_Clinic_Visit_Report) code as the command button on the main form.

When the Print Current Coumadin Clinic Visit report is selected to be run there is a currently selected patient. So the index in to the patient demographic data table is set to a value. However, when I run the report all of the patients current (most recent) Coumadin Clinic visit data are printed. That is, the report contains each patient's most recent (last) Coumadin Clinic Visit data printed by patient name.

So the problem is I must somehow get "the index number" of the "currently selected patient" to the query or at least somehow make it available to it.

So far I haven't been able to figure out how to do that. Even with all of the suggestions others have posted to this thread.

Now:

The "basic" part of the query works as desired. I have set up the query so that it will promt for a patient index number ([TBo_PT_DB_Number] entry in the query) in to the patient demographic data. When the query is run it promts for a patient index number. No matter what number I enter, as long as it is a valid index number, the query displays only the last (current/most recent) Coumadin Clinic Visit data for that patient. Hence, I am confident that the basic part of the query works correctly. The query is called: Qry_Coumadin_Clinic_Daily_Doseage_Reminder_Report

The query used for the report is currently set up as:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT DISTINCTROW [Tbl_Coumadin_Clinic].[PT_DB_Number], [Tbl_PT_Demographics].[PT_Name_Last], [Tbl_PT_Demographics].[PT_Name_Prefix], [Tbl_PT_Demographics].[PT_Name_First], [Tbl_PT_Demographics].[PT_Name_Middle], [Tbl_PT_Demographics].[PT_Name_Suffix], [Tbl_Coumadin_Clinic].[Clinic_Visit_Date], [Tbl_Coumadin_Clinic].[Medication], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Mon], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Tue], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Wed], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Thu], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Fri], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Sat], [Tbl_Coumadin_Clinic].[Med_Dose_Wk1_Sun], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Mon], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Tue], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Wed], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Thu], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Fri], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Sat], [Tbl_Coumadin_Clinic].[Med_Dose_Wk2_Sun], [Tbl_Coumadin_Clinic].[Next_Lab_Date], [Tbl_Coumadin_Clinic].[Next_Visit_Med_Date]

FROM Tbl_PT_Demographics INNER JOIN Tbl_Coumadin_Clinic ON [Tbl_PT_Demographics].[PT_DB_Number]=[Tbl_Coumadin_Clinic].[PT_DB_Number]

WHERE (((Tbl_Coumadin_Clinic.Clinic_Visit_Date)=(SELECT Max(Clinic_Visit_Date)

FROM Tbl_Coumadin_Clinic T WHERE T.PT_DB_Number = Tbl_Coumadin_Clinic.PT_DB_Number)) AND (([TBo_PT_DB_Number])=[Tbl_PT_Demographics].[PT_DB_Number]));
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

OK, what is the correct syntax that is needed to replace the [Tbo_PT_DB_Number] entry in the last FROM - WHERE clause of the query based on how the query is currently set up. It seems to me that, that is where NEW (i.e. the correct) code needs to be entered in place of the [Tbo_PT_DB_Number] in the query to accomplish what I need to.

I will greatly appreciate any help, coding/methods examples, suggestions, etc. Remember I'm over 30 so I may be dumb, dense, stupid, or whatever. At least that is the way I sometimes feel these days. So, if you could be as sspecifc, i.e. use actual coding example of what needs to replace the [Tbo_PT_DB_Number] that will greatly help me.

Best Regards