SELECT CASEMASTER.LOCATION, CASEMASTER.CASENO, CLINICVISIT.TREATMENTDATE FROM CASEMASTER INNER JOIN CLINICVISIT ON CASEMASTER.CASENO = CLINICVISIT.CASENO WHERE CLINICVISIT.TREATMENTDATE = (SELECT Min(TREATMENTDATE) FROM CLINICVISIT As C2 WHERE C2.CASENO = CASEMASTER.CASENO) ORDER BY CASEMASTER.LOCATION, CASEMASTER.CASENO;
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of JULIANNE SCHALK Sent: Saturday, January 28, 2006 9:58 AM To: [email protected] Subject: RE: [ms_access] Displaying first record from a set of records in a report Thanks for offering your assistance. Below is a similiar situation as the one I originally described to you. I need the query to list or display the first - or least recent - Treatment date for each CaseNo. How do I do that? SELECT CASEMASTER.LOCATION, CASEMASTER.CASENO, CLINICVISIT.TREATMENTDATE FROM CASEMASTER INNER JOIN CLINICVISIT ON CASEMASTER.CASENO = CLINICVISIT.CASENO ORDER BY CASEMASTER.LOCATION, CASEMASTER.CASENO; John Viescas <[EMAIL PROTECTED]> wrote: Julianne- You can modify the Record Source of your report to fetch only the last or first date for each patient as needed. Please post the SQL from your current Record Source, and one of us should be able to show you how to limit the date rows. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Julianne Sent: Monday, January 16, 2006 2:41 PM To: [email protected] Subject: [ms_access] Displaying first record from a set of records in a report Happy New Year and HELP!!! I have a report based on a query that displays all of the visit dates of patients serviced by a clinic. In a report, I need Access to display the first - or least recent - visit per patient for each patient. E.g., I select ClinicA on a form. My report then displays Mr Jones, Ms. Smith and all of the dates that Mr. Jones has been to ClinicA and all of the dates Ms. Smith has been to ClinicA. I need my report (or query - which ever is easiest) to list only the very first visit date from a long list of visit dates PER PATIENT NAME. Do I use the DFirst function? If so, how? All of the help I have viewed so far re:DFirst have been vague, especially in describing the "domain." Thank you! Yahoo! Groups Links Yahoo! Groups Links [Non-text portions of this message have been removed] Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
