用于生成套用信函的 SQL select 语句

发布于 2025-01-16 01:50:48 字数 633 浏览 0 评论 0原文

我目前在 MS Access 中有一个数据库，我需要更新生成套用信函的方式。我们使用我 10 年前所做的查询来生成出勤证明信。

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;

结果是您输入的任何研讨会 ID 的一组字母。每个字母代表医生的个人注册。

现在的问题是我们有一个额外的表（许可证），我需要每个字母来显示医生可能拥有的 1 到 5 个许可证。许可证表中的字段为：license_ID、doctor_id、license_type、state 和 license_number。

我可以显示一个许可证，但要显示不同数量的许可证号和状态，每个许可证号和状态都与发给其被许可人的信件相匹配，这超出了我迄今为止所知道的范围。

任何意见都会受到赞赏。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乱了心跳 2025-01-23 01:50:48

您需要创建一个子报告，列出信件收件人的医生的所有许可证。首先，您需要一个将医生与其拥有的许可证（例如“doctor_licenses”）链接起来的表，其中包含字段 dr_id 和 license_id。将 doctor.dr_id 添加到上面的查询中，使其成为主报告记录源的一部分。然后创建第二个报告来显示每位医生的执照。第二个报告的记录源类似于将

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

第二个报告作为子报告添加到主报告中。通过将子报表的“链接主字段”和“链接子字段”属性均设置为 dr_id，将子报表链接到主报表。有关子报表以及如何创建和使用它们的更多信息，请参见此处：
https://support.microsoft.com/en-us/office/create-and-use-subreports-816f987a-4615-4058-8f20-ce817093bb33

编辑，因为我无法评论您的原文发布后，您仍然需要使用子报告来列出医生执照，因为医生和执照之间存在一对多关系（一名医生可以拥有一份或多份许可证）。如果您尝试将许可证列表添加为逗号分隔列表或类似于报告的内容，则必须使用更复杂的方法。请查看子报表解决方案，如果这不能满足您的需求，请更具体地说明您的要求。

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.

回复收藏 0 原文

~没有更多了~