Refine query to eliminate dups based on criteria

  • Thread starter Thread starter Todd
  • Start date Start date
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];
 
Dear Todd:

Here's your original query in "cleaned up" form (to my personal taste)
which I'm posting for my own future reference:

SELECT V.[Vessel Number], V.[Asset Number], S.[Status Date],
L.Latest, S.[BIN in Vessel], S.Status, F.FlagDescription,
IIf([S.Status] = "SIP" Or [S.Status] = "Sterile Storage",
P.Latest + 13) AS SIPExpireDate
FROM Vessels V
INNER JOIN [Latest Date Query] L
ON V.[Vessel Number] = L.[Vessel Number]
LEFT JOIN [Flags Active Query] F
ON L.[Vessel Number] = F.FlagVessel
LEFT JOIN [Latest SIP Query] P
ON V.[Vessel Number] = P.[Vessel Number]
INNER JOIN Status S
ON V.[Vessel Number] = S.[Vessel Number])
AND L.Latest = S.[Status Date]
WHERE S.Status Like "*" & [Enter Status or Hit Enter for All] & "*"
ORDER BY V.[Vessel Number];

First, I'd like to look at your IIf() statement. It is missing a
parameter. Why? When the test condition is false, what do you want
to see?

Also, you could change this to read:

IIf([S.Status] IN ("SIP", "Sterile Storage"),
P.Latest + 13, P.Latest) AS SIPExpireDate

Here, I've assumed you might want to have the Latest date value when
Status is not SIP or Sterile Storage.

Now, on to your main problem.

I take it you want only one row returned for each combination of
[Vessel Number] and [Status Date]. To do this you would then filter
out any additional rows taken from the Status table (or query),
leaving only the one in which statusID is the largest (maximum) value
out of all the status values for that date.

There are two approaches that occur to me. One is to put all the
logic for this into your query above. But that may be a bit more
complex to handle. I think it would be best to use an alternative
approach.

We can write a query on Status that returns the desired values but has
already removed all duplicate dates per your requirements.

SELECT [Vessel Number], Status, [Status Date], [BIN in Vessel]
FROM Status S
WHERE statusID = (SELECT MAX(statusID) FROM Status S1
WHERE S1.[Vessel Number] = S.[Vessel Number]
AND S1.[Status Date] = S.[Status Date])

First, test this to make sure it is giving only single rows per
Vessel/Date as you would want. Then, replace the Status table/query
you are now using in your query with the whatever you name the above
query.

That should fix your problem. For reference, if you should choose to
study the technique above, you might search for "correlated subquery"
as this is a common query technique. Using two instances of the same
table/query (Status) which are distinguishable by the aliasing used (S
and S1) it correlates them (the 2 part WHERE clause) on [Vessel
Number] and [Status Date] in order to find the single largest statusID
for each Vessel/Date. Then, using the value of the statusID returned
by the subquery, it filters the main query to show just that one row.
The fact that statusID is unique is an essential part of making this
work.

Please let me know if this worked for you, and if I can be of any
other assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

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];
 
Back
Top