I need help with a Query used in a Report

[DPROMICS]

The Situation:

I have an Access App running that works correctly (mostly). Data entry and display works as desired. A needed query for a report doesn't. That is the biggest headache that is currently holding up finishing this Freebee project I got myself in to.

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's query 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 prompt for an index number in to the patient demographic table. Then it quite nicely prints only the most recent (last) clinic visit data for the currently selected patient.

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

There are two tables.

A patient demographics data table: (Tbl_PT_Demographics)
which is the main table in the Access app.

A Coumadin Clinic Visit data table: (Tbl_Coumadin_Clinic).

In the Patient Demographic Data Table (Tbl_PT_Demographics) 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 (Tbl_Coumadin_Clinic).

In the Coumadin Clinic Visit Data table (Tbl_Coumadin_Clinic) 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 Data 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 labeled Patients. On this Tab one can select a patient from a list box that displays all patients in the Patient Demographic Data Table (Tbl_PT_Demographics).

There is another Tab on the main form that is labeled: 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 also 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 actual subform are the same.

On the Coumadin Clinic Tab of the main form there is a command button labeled: Print Current Clinic Visit. The command button's control is: Cmd_Print_Current_Coumadin_Clinic_Visit_Report.

When the Print Current Coumadin Clinic Visit Report button is clicked on, 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 most recent (read last) Coumadin Clinic visit data is printed. That is, the report contains each patient's most recent (last) Coumadin Clinic Visit data printed by patient name rather than only the currently selected patient's data.

Now:

The "basic" part of the query works as desired. I have set up the query so that it will prompt 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 prompts for a patient index number. No matter what number I enter, as long as it is a valid index number (i.e. that record exists in the table), the query displays only the most recent (last) 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_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 is where the correct syntax needs to be entered in place of the [Tbo_PT_DB_Number] in the query to accomplish what I need it to do.

I will greatly appreciate any help, coding/methods examples, suggestions, etc. Once, the correct syntax for this query is known, several other needed queries will fall in to place for other reports needed for this Access app.

Remember I'm considerably 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 specific, i.e. use actual coding example(s), pertinent to this app's tables and above query, etc. of what needs to replace the [Tbo_PT_DB_Number] entry that would greatly help me.

I really want to and need to get this Freebee project completed. The query’s syntax is basically the only thing holding that up.

Best Regards

 

[PHV]

And what about something like this ?
SELECT C.PT_DB_Number, D.PT_Name_Last, D.PT_Name_Prefix, D.PT_Name_First, Demographics.PT_Name_Middle, D.PT_Name_Suffix, C.Clinic_Visit_Date, C.Medication, C.Med_Dose_Wk1_Mon, C.Med_Dose_Wk1_Tue, C.Med_Dose_Wk2_Sun, C.Next_Lab_Date, C.Next_Visit_Med_Date
FROM Tbl_PT_Demographics D INNER JOIN Tbl_Coumadin_Clinic C ON D.PT_DB_Number = Clinic.PT_DB_Number
WHERE C.Clinic_Visit_Date = (SELECT Max(Clinic_Visit_Date) FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number = C.PT_DB_Number)
AND D.PT_DB_Number = Forms!Frm_ECMR_Main![name of PT_DB_Number control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

OK,

- - - AND D.PT_DB_Number = Forms!Frm_ECMR_Main![name of PT_DB_Number control] <---- I follow all of it until we get to the: [name of PT_DB_Number control].

A patient entry is selected from the Tbl_PT_Demographics on the main form (Frm_ECMR_Main). I would assume that the form name Frm_ECMR_Main is the control. The list box on the main form, from which a patient is selected, is unbounded. So I am lost on this item. Could you expand on it?

Best regards

 

[PHV]

AND D.PT_DB_Number = Forms!Frm_ECMR_Main![name of listbox]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

AND D.PT_DB_Number = Forms!Frm_ECMR_Main![name of listbox]

Dosen't work.

The List Box on the Frm_ECMR_Main is Unbounded.

Its Name in its properties box is: ListBox_Display_Patients

The query in the list box propereties is:

SELECT * FROM Tbl_PT_Demographics WHERE PT_Name_Last LIKE '**' ORDER BY [PT_Name_Last], [PT_Name_First];

Any other thoughts about how to make this query work?

 

[PHV]

Set the BoundColumn property of ListBox_Display_Patients to the column number of PT_DB_Number.
And then in the SQL code:
AND D.PT_DB_Number = [Forms]![Frm_ECMR_Main]![ListBox_Display_Patients]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

Well, nope . . . didn't work no matter what I tried.

OK, perhaps there is a better way to approach this. Perhaps the better approach would be to create a query that finds the most recent (actually their Last) clinic visit of all patients that have been seen in the clinic. This will find a list of patients and then use the last name, first name, and middle name/initial of the currently selected patient to select the patient one really wants.

One still has the problem of how to access the name of the patient from the form.

OK, the following query works correctly no matter what existing patient last name is placed in the quotes.

SELECT [Tbl_PT_Demographics].[PT_DB_Number], [Tbl_PT_Demographics].[PT_Name_Last], [Tbl_PT_Demographics].[PT_Name_First], [Tbl_Coumadin_Clinic].[PT_DB_Number], [Tbl_Coumadin_Clinic].[Coumadin_Clinic_Visit_Date], [Tbl_Coumadin_Clinic].[Coumadin_Lab_Name]

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.Coumadin_Clinic_Visit_Date)=(SELECT Max(Coumadin_Clinic_Visit_Date)

FROM Tbl_Coumadin_Clinic T WHERE T.PT_DB_Number = Tbl_Coumadin_Clinic.PT_DB_Number AND Tbl_PT_Demographics.PT_Name_Last = "AAATest-Anderson" )));

So what code replaces the quoted string?

One more piece of information. On the main form there is also another subform. The subform is called: Frm_ECMR_Main_Display_Selected_PT_Subform.

This form displays the name (Last, First, Middle)of the currently selected patient.

The Last name text box on this form is called: TBo_Name_Last.

The problem with setting the Bound column to the PT_DB_Number in the ListBox_Display_Patients query is that the above form (Frm_ECMR_Main_Display_Selected_PT_Subform)stops displaying the currently selected patient's name. But perhaps one could use the PT name displayed in its text boxes to select the currently selected patient in the query.

Best Regards.

 

[PHV]

Perhaps this ?
AND Tbl_PT_Demographics.PT_Name_Last = [Forms]![Frm_ECMR_Main].Form![Frm_ECMR_Main_Display_Selected_PT_Subform]![TBo_Name_Last]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

PHV (MIS)

I tried this:
AND Tbl_PT_Demographics.PT_Name_Last = [Forms]![Frm_ECMR_Main].Form![Frm_ECMR_Main_Display_Selected_PT_Subform]![TBo_Name_Last]

Whe running teh query if I enter a valid patient last name that's patient's last clinic visit is selected and displayed. However, When running the query from the Access App I get the following error message:

The specified field 'PT_DB_Number' could refer to more than one table listed in the FROM clause of your SQL statement.

Any suggestions? I appriciate the help and understand that kt is very difficult to figure out a problem via an on-line site. But thanks for your and all who have offered some guidance.

 

[PHV]

Another try:
SELECT C.PT_DB_Number, D.PT_Name_Last, D.PT_Name_Prefix, D.PT_Name_First, D.PT_Name_Middle, D.PT_Name_Suffix, C.Clinic_Visit_Date
, C.Medication, C.Med_Dose_Wk1_Mon, C.Med_Dose_Wk1_Tue, C.Med_Dose_Wk2_Sun, C.Next_Lab_Date, C.Next_Visit_Med_Date
FROM Tbl_PT_Demographics D INNER JOIN Tbl_Coumadin_Clinic C ON D.PT_DB_Number = C.PT_DB_Number
WHERE C.Clinic_Visit_Date = (SELECT Max(Clinic_Visit_Date) FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number = C.PT_DB_Number)
AND D.PT_Name_Last = [Forms]![Frm_ECMR_Main].Form![Frm_ECMR_Main_Display_Selected_PT_Subform]![TBo_Name_Last]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

Sorry got side tracked with another project. Just got back to this last night.

OK. Got it a little farther along. And I am learning . . . things.

Now, If I just run the query from teh query "screen" in Access, I am prompted for a Patient Number (an index into the Tbl_PT_Demographics table) which when entered returns the desired patient. Assuming that is a valid patient number. Great!

However, if I run the report in preview mode or by clicking on the report command button in the application I get the following error message:

"The specified field 'PT_DB_Number' could refer to more than one table listed in the FROM clause of your SQL statement."

There is a subform on the main form with a field called: [Tbo_PT_DB_Number] which does display the correct patient index when a patient is selected.

The problem remains how to pass to the query the currently selected patient's index in to the Tbl_PT_Demographics.

Any ideas?

The current query syntax:

SELECT C.PT_DB_Number, D.PT_DB_Number, D.PT_Name_Last, D.PT_Name_Prefix, D.PT_Name_First, D.PT_Name_Middle, D.PT_Name_Suffix, C.Coumadin_Clinic_Visit_Date, C.Coumadin_Medication, C.Coumadin_Med_Dose_Wk1_Mon, C.Coumadin_Med_Dose_Wk1_Tue, C.Coumadin_Med_Dose_Wk1_Wed, C.Coumadin_Med_Dose_Wk1_Thu, C.Coumadin_Med_Dose_Wk1_Fri, C.Coumadin_Med_Dose_Wk1_Sat, C.Coumadin_Med_Dose_Wk1_Sun, C.Coumadin_Med_Dose_Wk2_Mon, C.Coumadin_Med_Dose_Wk2_Tue, C.Coumadin_Med_Dose_Wk2_Wed, C.Coumadin_Med_Dose_Wk2_Thu, C.Coumadin_Med_Dose_Wk2_Fri, C.Coumadin_Med_Dose_Wk2_Sat, C.Coumadin_Med_Dose_Wk2_Sun, C.Coumadin_Clinic_Next_Lab_Date, C.Coumadin_Clinic_Next_Visit_Med_Date

FROM Tbl_PT_Demographics AS D INNER JOIN Tbl_Coumadin_Clinic AS C ON D.PT_DB_Number = C.PT_DB_Number

WHERE (((C.Coumadin_Clinic_Visit_Date)=(SELECT Max(Coumadin_Clinic_Visit_Date)

FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number = C.PT_DB_Number AND D.PT_DB_Number =

Forms![Frm_ECMR_Main]![Frm_ECMR_Main_Display_Selected_PT_Subform]![Tbo_PT_DB_Number] )));

Best Regards

 

[PHV]

This is not the syntax I suggested you in my post stamped 29 Nov 05 16:37

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

PHV

Oppsss . . . Your are correct.

Your suggestion on 29 Nov 05 was:

FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number=C.PT_DB_Number)

AND D.PT_Name_Last = [Forms]![Frm_ECMR_Main].Form!
------- -----

[Frm_ECMR_Main_Display_Selected_PT_Subform]![TBo_Name_Last]


But, I missed the [Froms]! and the .Forms! in my query's .syntax

FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number= C.PT_DB_Number

AND D.PT_DB_Number = Forms![Frm_ECMR_Main]!
^
[ ] .Form!

[Frm_ECMR_Main_Display_Selected_PT_Subform]![Tbo_PT_DB_Number]

I will try this later this morning and let you know how it worked.

Best Regards

 

[DPROMICS]

PHV

OK. I made the changes to the query's syntax. The last from/where clause now is:

FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number= C.PT_DB_Number

AND [D.PT_DB_Number] = Forms![Frm_ECMR_Main].Frm_ECMR_Main_Display_Selected_PT_Subform]![Tbo_PT_DB_Number]

I have created a textbox on the main form Frm_ECMR_Main that is bound to Tbl_PT_Demographics.PT_DB_Number. This text box does display the correct PT_DB_Number for any patient selected.

Now if I run the query from the form the query part of Access I am prompted for a value for TBO_PT_DB_Number. When I enter a valid index in to the Tbl_PT_Demographics that patient's data is found and displayed. However, if I click on the command button on the form Frm_ECMR_Main to run the report I still receive the error message:

"The specified field 'PT_DB_Number' could refer to more than one table listed in the FROM clause of your SQL statement."

I assume that it is complaining about this part of the clause: FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number .
------------
OK. The query works as desired when it is run in the query part of Access. But not from the form. I tried adding a letter to the PT_DB_Number, i.e D.PT_DB_Number, etc. But still get the same error message.

The concept is simple . . . the devil is in the darn details (i.e. correct query syntax)! Arggggggg!

Any suggestions?

Something I thought about doing is to create a non-indexed table that cantains only a copy of the PT_DB_Number index into the Tbl_PT_Demographics and then use that entry to compare to the "C.PT_DB_Number" in the query. When the form Frm_ECMR_Main initially loads or a patient is selected the seletced patient's index in to the Tbl_PT_Demographics will be copied to the Tbl_Selected_PT.Currently_Selected_PT_DB_Number entry of the table. Sort of a VBA equivalent to me.record . . . , etc.

Best Regards,

 

[DPROMICS]

PHV & All,

YEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ! ! !

Here is the solution to this particular problem:

First off there were several aspects that had to be in place before the report/query would work correctly.

1) Once the query was working correctly, i.e. when run
from the query section of Access, would prompt for a
PT_DB_Number and then display the selected patient
the query was working correctly.

Here is the final form of this query:

SELECT
D.PT_DB_Number, C.PT_DB_Number, D.PT_DB_Number, D.PT_Name_Last, D.PT_Name_Prefix, D.PT_Name_First, D.PT_Name_Middle, D.PT_Name_Suffix, C.Coumadin_Clinic_Visit_Date, C.Coumadin_Medication, C.Coumadin_Med_Dose_Wk1_Mon, C.Coumadin_Med_Dose_Wk1_Tue, C.Coumadin_Med_Dose_Wk1_Wed, C.Coumadin_Med_Dose_Wk1_Thu, C.Coumadin_Med_Dose_Wk1_Fri, C.Coumadin_Med_Dose_Wk1_Sat, C.Coumadin_Med_Dose_Wk1_Sun, C.Coumadin_Med_Dose_Wk2_Mon, C.Coumadin_Med_Dose_Wk2_Tue, C.Coumadin_Med_Dose_Wk2_Wed, C.Coumadin_Med_Dose_Wk2_Thu, C.Coumadin_Med_Dose_Wk2_Fri, C.Coumadin_Med_Dose_Wk2_Sat, C.Coumadin_Med_Dose_Wk2_Sun, C.Coumadin_Clinic_Next_Lab_Date, C.Coumadin_Clinic_Next_Visit_Med_Date

FROM Tbl_PT_Demographics AS D INNER JOIN Tbl_Coumadin_Clinic AS C ON D.PT_DB_Number=C.PT_DB_Number

WHERE (((C.Coumadin_Clinic_Visit_Date) = (SELECT Max(Coumadin_Clinic_Visit_Date)

FROM Tbl_Coumadin_Clinic WHERE PT_DB_Number = C.PT_DB_Number) AND (D.PT_DB_Number = [Forms]![Frm_ECMR_Main]![TBo_PT_DB_Number]) ));

Note: That when the app was open and any one patient was selected, if the query was run from the query part of Access it would always display the queried data fror the currently selected patient. This is probably a method to verify that this type of query is working and gave me a hint of where the problem was.

2) But when the report, that used this query, was run
the query claimed that it couldn't resolve the
PT_DB_Number (i.e. the error message: "The specified
field 'PT_DB_Number' could refer to more than one
table listed in the FROM clause of your SQL
statement.").

3) OK. I tried recreating the report with the report
wizard. Guess what? The report now worked correctly.
appairently the "trick" is to create the query and
verify it works correctly and then create the report.

Additionaly, for this situation having the query reference the currently selected patient from a TBo_PT_DB_Number text box field on the main form, Frm_ECMR_Main, was part of the solution.

I thought I would send along the solution to this problem so that others having the same problem or a similiar problem could find a fix for their situation.

Perhaps the moderater of this group might want to add it to their list of common problem fixes. I would be willing to spend some time to write it up so that it is more clear and understandable to other.

Best Regards,