Hi,
Have a query like
-----------
SELECT *
FROM myTable
WHERE NOT ShippedToDate Is Null
-----------
call it q1 It just removes the field with a null in ShippedToDate.
Then, the following query should do
---------------------
SELECT a.ShipID,
LAST(a.SKU),
LAST(a.Date),
LAST(b.ShippedToDate)
FROM ( myTable As a LEFT JOIN q1 As b
ON a.SKU=b.SKU and a.Date >= b.Date)
LEFT JOIN q1 As c
ON a.SKU=c.SKU and a.Date >= c.Date
GROUP BY a.ShipID, b.Date
HAVING b.Date=MAX(c.Date)
--------------------
where b and c are the same sets, initially, the set of records with the same
SKU than the one they have to match, in set a, but with a date before, or
equal, to the one of set a. Sure, sets b and c contains only records with
not-null values for ShippedToDate. Set b is then restricted further by
keeping only the record that match its maximum date. We technically need a
HAVING clause to involve the MAX(c.date), and b.Date, used in the
comparison, also need to be in the GROUP list.
Since we group by a.ShipID ( a primary key), and restricted set b to just
one record, through the GROUP BY, the LAST aggregate can be replaced by
MIN, MAX, or FIRST. That assumes you have just one Date for a given SKU, or,
if you prefer, that the couple (SKU, Date) is a potential primary key.
Hoping it may help,
Vanderghast, Access MVP
