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.
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.