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 headach 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 and 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 table (Tbl_PT_Demographics) 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 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.
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 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 current (most recent) 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 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 (i.e. that record exists in the table), the query displays only the current/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_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 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 specifc, 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 querie's syntax is basically the only thing holding that up.
Best Regards
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 and 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 table (Tbl_PT_Demographics) 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 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.
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 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 current (most recent) 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 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 (i.e. that record exists in the table), the query displays only the current/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_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 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 specifc, 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 querie's syntax is basically the only thing holding that up.
Best Regards
