I have a table that is shown below in ORIGINAL TABLE. It is common for
the VEHICLE # field to have repeating vehicles (ID 3,4,5 & 8,9).
Usually the Vehicle will have SHIPDATE, DATE_RECEIVED, and
DATE_ORDERED for the first entry, after that is could be null (ID 4 &
5). What I would like to do is for a query or VB code to look into the
ORIGINAL TABLE, find the duplicating VEHICLES. Where ever
DATE_RECEIVED or DATE_ORDERED is empty, automatically update it using
DATE_RECEIVED and DATE_ORDERED from the previous entry of the same
vehicle( but SHIPDATE MUST BE THE SAME!!).
ORIGINAL TABLE
ID VEHICLE # SHIPDATE DATE_RECEIVED DATE_ORDERED
1 BF16 5/1/2004 4/30/2004 4/28/2004
2 BF22 5/4/2004 5/1/2004 4/31/2004
3 GT15 5/15/2004 5/10/2004 5/9/2004
4 GT15 5/15/2004
5 GT15 5/15/2004
6 RV66 5/19/2004 5/18/2004 5/17/2004
7 RV68 5/19/2004 5/18/2004 5/17/2004
8 JM40 5/20/2004 5/18/2004 5/16/2004
9 JM40 5/22/2004
10 BX12 5/29/2004 5/25/2004 5/20/2004
RESULTING TABLE
ID VEHICLE # SHIPDATE DATE_RECEIVED DATE_ORDERED
1 BF16 5/1/2004 4/30/2004 4/28/2004
2 BF22 5/4/2004 5/1/2004 4/31/2004
3 GT15 5/15/2004 5/10/2004 5/9/2004
4 GT15 5/15/2004 5/10/2004 5/9/2004
5 GT15 5/15/2004 5/10/2004 5/9/2004
6 RV66 5/19/2004 5/18/2004 5/17/2004
7 RV68 5/19/2004 5/18/2004 5/17/2004
8 JM40 5/20/2004 5/18/2004 5/16/2004
9 JM40 5/22/2004
10 BX12 5/29/2004 5/25/2004 5/20/2004
Here, DATE_RECEIVED & DATE_ORDERED in rows ID 4,5 have been
automatically updated using values from row ID 3. Row ID 9 did not get
updated with using ID 8 because they have different SHIPDATE.
Any help is greatly appreciated. I cannot find a way to solve this at
all.
JIm