无法对包含SubQuery的查询进行交叉表查询

Question

我有一个包含子查询的查询:从我的表“时间表”中计算出出发时间和到达时间之间的间隔。

这个查询工作非常好，但是当试图从交叉表执行它时，它会提示我一个错误，它找不到表"a“，这是我用于”时间表“的别名。

SELECT a.VesselID, a.MovementID, a.MovementTime, (SELECT TOP 1 
 Timetable.MovementTime
 FROM Timetable
 WHERE (((Timetable.MovementID)="Arrival") AND 
 ((Timetable.VesselID)=a.VesselID]) AND ((Timetable.MovementTime)>a.
 [MovementTime]))
 ORDER BY Timetable.MovementTime) AS Arrival1,
DateDiff('h',[a].[MovementTime],[Arrival1]) AS [Interval]
FROM Timetable AS a INNER JOIN Timetable ON a.ID = Timetable.ID
WHERE (((a.MovementID)="Departure"));

我认为这个问题非常类似，解决方案是像@DHW所说的那样拆分我的查询，但是我做不到。

这是我分裂的尝试：

[Departure_Query]
    SELECT Timetable.VesselID, Timetable.MovementTime AS mymov, 
    Timetable.MovementID
    FROM Timetable
    WHERE (((Timetable.MovementID)="Departure"));

[Main]
    SELECT Timetable.MovementTime, Timetable.MovementID, Timetable.VesselID, Departure_Query.mymov, DateDiff('h',[mymov],[MovementTime]) AS [Interval]
    FROM Timetable INNER JOIN Departure_Query ON Timetable.VesselID = Departure_Query.VesselID
    WHERE (((Timetable.MovementTime)>[Departure_Query].[mymov]) AND ((Timetable.MovementID)="Arrival") AND ((Timetable.VesselID)=[Departure_Query].[VesselID]))
    ORDER BY Timetable.MovementTime;

我认为问题是：

在工作查询中，我可以放入SELECT TOP 1，但在拆分尝试中，我不知道将它放在哪里。

实际上，更新了，现在我想要拆分它，因为当我试图在它上面构建一个报告时。它提示我，Access不能对此字段进行分组。

但不管怎样，这是我的尝试

TRANSFORM DateDiff('h',[a].[MovementTime],[Arrival1]) AS [Interval]
SELECT a.MovementTime
FROM Timetable AS a INNER JOIN Timetable ON a.ID = Timetable.ID
WHERE (((a.MovementID)="Departure"))
GROUP BY a.MovementID, a.MovementTime, (SELECT TOP 1 Timetable.MovementTime
FROM Timetable
WHERE (((Timetable.MovementID)="Arrival") AND ((Timetable.VesselID)=a.[VesselID]) AND ((Timetable.MovementTime)>a.[MovementTime]))
ORDER BY Timetable.MovementTime)
PIVOT a.VesselID;

结果设计观

Answer 1

考虑使用域聚合的交叉表DMin()来替换子查询：

TRANSFORM DateDiff('h', main.[MovementTime], main.[Arrival1]) AS [Interval]
SELECT main.MovementID, main.MovementTime
FROM
   (SELECT t.VesselID, t.MovementID, t.MovementTime, 
           DMin("MovementTime", "Timetable", "MovementID = 'Arrival' 
                 AND VesselID = " & t.VesselID & " 
                 AND MovementTime > #" & t.MovementTime & "#") As Arrival1
    FROM Timetable AS t
    WHERE (((t.MovementID) = 'Departure'))
   ) As 
GROUP BY main.MovementID, main.MovementTime
PIVOT main.VesselID;

Answer 2

谢谢你@Parfait和@June7，我加了这个答案，这样将来的任何人都能从这个问题中受益。

问题

我想出了一个问题:查询是减去特定船只的所有较小的起飞日期。

即1号船离开6/1、6/3、6/6，到达6/2、6/2、6/8、6/8-6/6、6/8-6/3、6/8-6/1航向，第一艘(粗体)是正确的。

解决方案

SELECT Min(Timetable.MovementTime) AS MinOfMovementTime, Departure_Query.mymov AS DeptDate, Min(DateDiff('h',[mymov],[MovementTime])) AS WorkingH, Timetable.MovementID, Timetable.VesselID
FROM Timetable LEFT JOIN Departure_Query ON Timetable.VesselID = Departure_Query.VesselID
WHERE (((Timetable.MovementID)="Arrival") AND ((Timetable.VesselID)=[Departure_Query].[VesselID]) AND ((Timetable.MovementTime)>[mymov]))
GROUP BY Departure_Query.mymov, Timetable.MovementID, Timetable.VesselID
ORDER BY Min(Timetable.MovementTime);

这里唯一的变化是Min(DateDiff('h',[mymov],[MovementTime]))，它只给出最小的减法值，这就是最大的离开日期。