>Works great thanks.
>
>I have one more chalange though.
>
>Each part number in table a has a current engineering drawing revision letter.
>
>Each inspection record in table b also has the engineering drawing level it was checked to. This revision level may have changed since it was inspected. I need a query that will show where table a revisions and table b revisions(greatest date of group) that no longer match.
select A.*, B.RevisionLevel, B.Date from
TableA A inner join
TableB B on A.PartNo = B.PartNo
inner join
(select PartNo, max(date) as MaxDate from TableB group by PartNo) X
on B.PartNo = X.PartNo and B.Date = X.MaxDate
where A.RevisionLevel <> B.RevisionLevel
If it's not broken, fix it until it is.
My Blog