CODEWITHSUNDEEP
sqlms-access
Hassaan AlAnsary
Hassaan AlAnsary

Reputation: 179

Can't Make Crosstab Query on a query containing SubQuery

I have query that contain subquery: to calculate the interval between departure and arrival time, from my table "Timetable"

this Query works very fine, but when trying to execute it from the Crosstab, It prompts me an error that it cannot find table "a" which is alias I used for "Timetable"

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"));

I think this Question is very similar, and the solution is that I split my query As @DHW said, but I couldn't do that.

and this is my try on splitting:

[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;

I think the problem is:
In The working query I could put SELECT TOP 1 but in the split try I dont know where to put it.

update Actually, right now i want to split it anyway, because when i am trying to build a report in top of it. It prompts me that Access cant do grouping on this field.

But anyway this my attempt 

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;

The resultsThe Design View

Upvotes: 0

Views: 611

Answers (2)

Hassaan AlAnsary
Hassaan AlAnsary

Reputation: 179

Thank you @Parfait and @June7, I am adding this answer so anyone in the future can benefit from this problem.

The Problem
I figured out the problem to be: The query is subtracting all the smaller departure dates for a specific Vessel
i.e. Vessel 1 Departed 6/1, 6/3, 6/6 and Arrived 6/2,6/2,6/8. so for the last day It was subtracting 6/8-6/6, 6/8-6/3, 6/8-6/1. of the course the only first one (the bold one)is the right one.

The Solution 

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);

The only change here is Min(DateDiff('h',[mymov],[MovementTime])) which only give the smallest subtraction value, which translates to The biggest Departure Date.

Upvotes: 0

Parfait
Parfait

Reputation: 107587

Consider a crosstab with a domain aggregate, DMin() to replace subquery:

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;

Upvotes: 1

Related Questions