基于给定日期范围的 MS Access 选择查询字段

Question

我接手了一家医疗诊所的Access 2003数据库管理。不幸的是，创建该数据库的人并不懂很多关于数据库的知识，将所有信息都放入了一个巨大的表中，即All Clinic Data表，其中包含以下字段（以及其他大约40个字段）：PatientID、名字、姓氏、预约日期、第一次预定的妇产科医生、计费项目#1、第二次预约日期、第二次预约原因、第二次预定的妇产科医生、计费项目#2。 我知道这不是最佳的数据安排方式，未来正在计划彻底改造数据库。但是，现在我需要创建仅包含特定日期范围内预约信息的报告。

这是我目前使用的SQL查询，用于填充我的报告，但如果一个患者的任何预约时间在指定的日期范围内，它就会捕获该患者的所有预约。

SELECT 
    Format([Input start date],"mm/dd/yy") & " through " & Format([Input end date],"mm/dd/yy") AS Expr1, 
    [All Clinic Data].[PatientID], [All Clinic Data].[Last Name], 
    [All Clinic Data].[First Name], [All Clinic Data].[Appt Date], 
    [All Clinic Data].[OB at Appt], [All Clinic Data].[Billing Item #1], 
    [All Clinic Data].[Second Appt Date], [All Clinic Data].[Reason for Second Appt], 
    [All Clinic Data].[OB at Second Appt], [All Clinic Data].[Billing Item #2] 
FROM [All Clinic Data]
WHERE (
        (([All Clinic Data].[Appt Date])>[Input start date] And 
        ([All Clinic Data].[Appt Date])<[Input end date])
    ) 
    OR 
    (
        (([All Clinic Data].[Second Appt Date])>[Input start date] And 
        ([All Clinic Data].[Second Appt Date])<[Input end date])
    ) 
ORDER BY [All Clinic Data].[Last Name];

注意：输入的起始日期和结束日期是在查询运行时输入的参数。

我尝试使用IIF来删除多余的数据，但我不知道如何构造语句以便仅显示给定日期范围内的预约日期和相关数据（OB和账单项目）。

示例：

1, Sally, Jones, 1/04/2010, Dr.A, 2/05/2011, Dr. B, Flu

2, Jennifer, Baker, 7/05/2010, Dr.X, 15/05/2011, Dr. B, Checkup

3, Joe, Smith, 20/06/2010, Dr.S, 

4, Tina, Turner, 17/05/2010, Dr.X, 15/06/2011, Dr. B, Checkup 

如果[输入的开始日期]为2010年5月1日，[输入的结束日期]为2010年5月31日，则我希望我的报告包含以下内容：

Sally, Jones, 

 1. 2/05/2011, Dr. B, Flu

Jennifer, Baker, 

 1. 7/05/2010, Dr.X  
 2. 15/05/2011, Dr.B, Checkup

Tina, Turner, 

 1. 17/05/2010, Dr.X

我希望这些信息足够了解，谢谢您的帮助。

更新1 以下是我尝试Mike下面建议的第一步。我将使用phn作为患者信息的标识符，稍后会获取该信息。但我遇到了编译错误，而我不够精通，不知道哪里出了问题。有什么想法吗？

SELECT 
    Format([Input start date],"mm/dd/yy") & " through " & Format([Input end     date],"mm/dd/yy") AS Expr1, 
    [All Clinic Data].PHN AS Phn, [All Clinic Data].[Appt Date] AS Date, 
    [All Clinic Data].[OB at Appt] AS OBName, 
    [All Clinic Data].[Billing Item #1] AS Billing 
FROM [All Clinic Data]

WHERE ([All Clinic Data].[Appt Date]>[Input start date] And [All Clinic Data].[Appt Date]<[Input end date])

UNION

SELECT 
    Format([Input start date],"mm/dd/yy") & " through " & Format([Input end date],"mm/dd/yy") AS Expr1, 
    [All Clinic Data].PHN AS Phn, [All Clinic Data].[Second Appt Date] AS Date, 
    [All Clinic Data].[OB at Second App] AS OBName, 
    [All Clinic Data].[Billing Item #2] AS Billing FROM [All Clinic Data]

WHERE ([All Clinic Data].[Second Appt Date]>[Input start date] And [All Clinic Data].[Second Appt Date]<[Input end date]);

- Crystal

---

您要求在MM/DD/YYYY和MM/DD/YYYY之间进行预约，但似乎数据存储为DD/MM/YYYY。您能确认一下吗？ - mikeY

是的，我的数据以 DD/MM/YYYY 的格式存储。在我的报告中，我将其格式化为 MM/DD/YY。在示例中，我手动输入了我的输入参数，据我所知，Access 会将其转换为 DD/MM/YYYY，以便进行比较。 - Crystal

我现在没有时间逐步走过代码并测试，但我认为这是我会考虑的方法：1.创建一个查询以选择您想要的每个人的第一个约会信息。2.创建一个查询以选择所有第二次约会信息。3.将它们合并起来并从中选择。需要解决分组问题。希望对你有帮助。 - mikeY

Answer 1

创建一个名为 PatientAppointment 的查询：

SELECT
   D.PatientID,
   D.[Appt Date] AS ApptDate,
   D.[OB at Appt] AS OBName
   D.[Billing Item #1] AS Billing 
UNION ALL SELECT
   D.PatientID,
   D.[Second Appt Date],
   D.[OB at Second Appt],
   D.[Billing Item #2];

请注意，此查询无法在设计器中进行编辑。您只能在 SQL 视图中使用它。然后，将此查询用作所有数据查询的源：

SELECT *
FROM
   PatientAppointment PA
WHERE
    PA.ApptDate >= [Input start date]
    AND PA.ApptDate < [Input end date];

我不知道性能会如何，但你可以尝试一下并让我们知道。基本上，将其视为您的约会表，并且甚至不要处理主表中的这些列。这是您缓慢开始更改应用程序的一种方式，因为最终您可以将其作为实际表格并迁移数据。

以下是可能更好，也可能更差的替代解决方案:

创建一个仅具有一列和一行的表。表或行的名称无关紧要，但数据类型必须为数字长整型。我将假设您将此表命名为Dual。然后执行同样的PatientAppointment查询：

SELECT
    D.PatientID,
    Iif(A.ApptNum = 1, D.[Appt Date], D.[Second Appt Date]) ApptDate,
    Iif(A.ApptNum = 1, D.[OB at Appt], D.[OB at Second Appt]) OBName,
    Iif(A.ApptNum = 1, D.[Billing Item #1], D.[Billing Item #2]) Billing
FROM
   [All Clinic Data] D
   INNER JOIN [
      SELECT 1 AS ApptNum FROM Dual
      UNION ALL SELECT 2 FROM Dual
   ]. A ON 1 = 1

如果您的数据库中打开了“SQL 92 Syntax”，请告诉我，我会在最终查询中切换到更好的语法。

您还可以像评论中建议的那样使用UNION ALL SELECT方法（我建议不要只使用UNION SELECT，因为这会让引擎做更多的工作来消除重复项）。我立即看不出您的查询有什么问题，请尝试将每个单独的SELECT语句分别执行，以查看是否有问题。

最后，如果您使用任何这些方法的性能太差，则我的Dual查询与您给定的查询的混合可能会奏效。按照您显示的方式进行查询，使用大型OR条件，但将两个约会拆分为上面的Dual查询中的单独行。您将需要添加其他条件来抑制不匹配正确日期范围的行（因为其他约会已经匹配）。

- ErikE