SQL select statement for generating form letters-CodePudding

I currently have a database in MS Access that I need to update the way we generate form letters. We use a query I made 10 years ago to generate certificate of attendance letters.

SELECT doctors.address, seminar_title, seminar_date, first_name, last_name
FROM doctors, seminars, registrations
WHERE seminars.seminar_id=[Seminar] AND registrations.seminar_id=seminars.seminar_id AND 
      registrations.dr_id=doctors.dr_id
ORDER BY doctors.last_name;

The result is a set of letters for whatever seminar ID you enter. Each letter representing an individual registration by a doctor.

The issue now is that we have an additional table (Licenses), and I need each letter to display 1 to 5 licenses that a doctor might have. The fields in the Licenses table are: license_ID, doctor_id, license_type, state, and license_number.

I can display one license, but getting a varying amount of license numbers and states to display, each matched on a letter to its licensee, has been beyond what I know how to do so far.

Any input is appreciated.

CodePudding user response：

You'll want to create a subreport listing all of the licenses for the doctor the letter is addressed to. First you will need a table linking doctors to the licenses they have (e.g. "doctor_licenses"), with the fields dr_id and license_id. Add doctors.dr_id to your query above so it is part of the recordsource for your main report. Then create a second report to display the licenses for each doctor. The recordsource for your second report will be something like

SELECT dr_id,
license_type
FROM doctor_licenses
INNER JOIN licenses
ON  doctor_licenses.license_id = licenses.license_id

Add the second report to your main report as a subreport. Link the subreport to your the main report by setting its "Link Master Fields" and "Link Child Fields" properties both to dr_id. More info on subreports and how to create and use them here: https://support.microsoft.com/en-us/office/create-and-use-subreports-816f987a-4615-4058-8f20-ce817093bb33

EDIT because I can't comment on your original post, you'll still need to use a subreport to list doctor licenses as there is a one-to-many relationship between doctor and licenses (one doctor can have one or more licenses). If you're trying to add the list of licenses as a comma delimited list or something similar to your report you'll have to utilize a more complicated approach. Please look at the subreport solution and if that doesn't do what you need please be more specific about your requirements.