T
Todd
I have a query that returns the latest status information
for my equipment and which works perfectly except for one
thing. If there are duplicate status dates, it returns
both dates.
What I'd like to do is: if for a particular vessel there
are two status entries on the same date, I'd like to look
at the statusID (which is in the status table) and return
only the larger statusID.
How do I do this?
Here is my current SQL:
SELECT Vessels.[Vessel Number], Vessels.[Asset Number],
Status.[Status Date], [Latest Date Query].Latest, Status.
[BIN in Vessel], Status.Status, [Flags Active
Query].FlagDescription, IIf([Status.Status]="SIP" Or
[Status.Status]="Sterile Storage",[Latest SIP Query.Latest]
+13) AS SIPExpireDate
FROM (((Vessels INNER JOIN [Latest Date Query] ON Vessels.
[Vessel Number] = [Latest Date Query].[Vessel Number])
LEFT JOIN [Flags Active Query] ON [Latest Date Query].
[Vessel Number] = [Flags Active Query].FlagVessel)
LEFT JOIN [Latest SIP Query] ON Vessels.[Vessel Number] =
[Latest SIP Query].[Vessel Number]) INNER JOIN Status ON
(Vessels.[Vessel Number] = Status.[Vessel Number]) AND
([Latest Date Query].Latest = Status.[Status Date])
WHERE (((Status.Status) Like "*" & [Enter Status or Hit
Enter for All] & "*"))
ORDER BY Vessels.[Vessel Number];
for my equipment and which works perfectly except for one
thing. If there are duplicate status dates, it returns
both dates.
What I'd like to do is: if for a particular vessel there
are two status entries on the same date, I'd like to look
at the statusID (which is in the status table) and return
only the larger statusID.
How do I do this?
Here is my current SQL:
SELECT Vessels.[Vessel Number], Vessels.[Asset Number],
Status.[Status Date], [Latest Date Query].Latest, Status.
[BIN in Vessel], Status.Status, [Flags Active
Query].FlagDescription, IIf([Status.Status]="SIP" Or
[Status.Status]="Sterile Storage",[Latest SIP Query.Latest]
+13) AS SIPExpireDate
FROM (((Vessels INNER JOIN [Latest Date Query] ON Vessels.
[Vessel Number] = [Latest Date Query].[Vessel Number])
LEFT JOIN [Flags Active Query] ON [Latest Date Query].
[Vessel Number] = [Flags Active Query].FlagVessel)
LEFT JOIN [Latest SIP Query] ON Vessels.[Vessel Number] =
[Latest SIP Query].[Vessel Number]) INNER JOIN Status ON
(Vessels.[Vessel Number] = Status.[Vessel Number]) AND
([Latest Date Query].Latest = Status.[Status Date])
WHERE (((Status.Status) Like "*" & [Enter Status or Hit
Enter for All] & "*"))
ORDER BY Vessels.[Vessel Number];