automatically update fields in a table using values within the same table

[Jimmy]

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

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Untested query:

UPDATE [Original Table] As A LEFT JOIN [Original Table] As B
ON A.[Vehicle #] = B.[Vehicle #] AND A.[ShipDate] = B.[ShipDate]
SET B.Date_Received = A.Date_Received ,
B.Date_Ordered = A.Date_Ordered
WHERE A.Date_Received IS NOT NULL
AND A.Date_Ordered IS NOT NULL

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUhvyYechKq OuFEgEQK/8gCg2qoWRj3ZWYQ hNMcwpdt8aS7oZM sAoKg4
ACtzwAVZxOhF6GQ o69TtM/U9
=awQD
-----END PGP SIGNATURE-----
Jimmy wrote:

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.