Comparing Two Tables

[Xavier]

Hello,

I was wondering if you guys would know the best way to pursue the
following problem. I have two tables, one with the unique items and
another with all the changes that occurred to the item. For example:

Table1
Model1 - Item1

Table2
Model1 - Item1 - RevA - Completion DateA
Model1 - Item1 - RevB - Completion DateB
Model1 - Item1 - RevC - Completion DateC

What I want to do is build a query that has Model#, Item# and the max
Completion Date. The problem is that in some cases, an item could have
a revision that is still open. In that case, I would want to see a
blank date next to the item#. In other words, show the latest date of
completion for the Item# or a null value if there is still a revision
that is incomplete. Does anyone have any recommendations as to how I
should pursue this problem? Thanks!

Xavier

 

[Guest]

Xavier:

Try this:

SELECT Model, Item,
MAX([Completion Date]) As [Latest Completion Date]
FROM Table2 AS T1
WHERE NOT EXISTS
(SELECT *
FROM Table2 AS T2
WHERE T2.Model = T1.Model
AND T2.Item = T1.Item
AND T2.[Completion Date] IS NULL)
GROUP BY Model, Item
UNION ALL
SELECT Model, Item, [Completion Date]
FROM Table2
WHERE [Completion Date] IS NULL;

The first part of the UNION ALL operation returns those model/item rows
where there is no NULL completion date (identified by the subquery) grouping
by Model/Item and returning the latest date. The second part of the
operation returns those roes where the completion date IS NULL. There is no
need to group this part of the UNION ALL operation as I assume there will no
more than one open revision per model/item. If there can be more than one
simply change the UNION ALL to a UNION operation as the latter suppresses
duplicates. A UNION operation is slower than a UNION ALL, however, so should
only be used when necessary.

Ken Sheridan
Stafford, England

 

[John Spencer]

One way should be

SELECT Model, Item, Rev, CompletionDate
FROM Table2
WHERE Nz(CompletionDate,#1/1/9999#) =
(SELECT Max(Nz(CompletionDate,#1/1/9999#))
FROM Table2 as Temp
WHERE Temp.Model = Table2.Model)

 

[Xavier]

Thanks guys, got both methods to work!

Xavier