How do I limit data returned from a query to N items?

[DPROMICS]

How do I limit data returned from a query to only N items?

1) I have a report where I want to only display the most recent four patient clinic visits data.

2) I have a database that contains patient names in one table and their clinic visits data in another table. The
table relationship is a one to many.

3) The query below finds the patient currently selected in the Access app and then returns their past clinic visits starting with the most recent clinic visit.

I need to limit the number of clinic visits returned to only the last four, most recent, clinic visits.

OK how can I do that?

The 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, D.PT_DOB, D.PT_Sex, D.PT_Status, D.PT_Status_State, D.PT_Practice_Physician, D.PT_Home_Telephone_AC, D.PT_Home_Telephone_Number, D.PT_Emergency_Telephone_AC, D.PT_Emergency_Telephone_Number, D.PT_Ref_Physician, D.PT_Clinic_1, D.PT_Clinic_2, D.PT_Clinic_3, D.PT_Clinic_4, D.PT_Clinic_5, D.PT_Clinic_6, C.Coumadin_Lab_Name, C.Coumadin_Lab_Date, C.Coumadin_Lab_PT, C.Coumadin_Lab_INR, C.Coumadin_Diagnosis_1, C.Coumadin_Diagnosis_INR_Goal, C.Coumadin_Diagnosis_1, C.Coumadin_Diagnosis_2, C.Coumadin_Diagnosis_3, C.Coumadin_Clinic_Visit_Patient_Symptoms, C.Coumadin_Clinic_Visit_Notes, C.Coumadin_Clinic_Visit_Staff_Initials, 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 (((D.PT_DB_Number)=C.PT_DB_Number And (D.PT_DB_Number)=[Forms]![Frm_ECMR_Main]![TBo_PT_DB_Number]))
ORDER BY C.Coumadin_Lab_Date DESC;

Best Regards,

 

[lespaul]

SELECT TOP 4 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, D.PT_DOB, D.PT_Sex, D.PT_Status, D.PT_Status_State, D.PT_Practice_Physician, D.PT_Home_Telephone_AC, D.PT_Home_Telephone_Number, D.PT_Emergency_Telephone_AC, D.PT_Emergency_Telephone_Number, D.PT_Ref_Physician, D.PT_Clinic_1, D.PT_Clinic_2, D.PT_Clinic_3, D.PT_Clinic_4, D.PT_Clinic_5, D.PT_Clinic_6, C.Coumadin_Lab_Name, C.Coumadin_Lab_Date, C.Coumadin_Lab_PT, C.Coumadin_Lab_INR, C.Coumadin_Diagnosis_1, C.Coumadin_Diagnosis_INR_Goal, C.Coumadin_Diagnosis_1, C.Coumadin_Diagnosis_2, C.Coumadin_Diagnosis_3, C.Coumadin_Clinic_Visit_Patient_Symptoms, C.Coumadin_Clinic_Visit_Notes, C.Coumadin_Clinic_Visit_Staff_Initials, 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 (((D.PT_DB_Number)=C.PT_DB_Number And (D.PT_DB_Number)=[Forms]![Frm_ECMR_Main]![TBo_PT_DB_Number]))
ORDER BY C.Coumadin_Lab_Date DESC;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[DPROMICS]

Great! . . . it work quite nicely. I must have missed this while looking through some SQL syntax books. I really appreciate the help.

Best Regards,