Update Query Question

[Guest]

Hello,
I am wanting to run an update query that will update specific records in a
table, and set a certain field to an already existing field, the first one in
a number sequence.
For example, I have table1 with fields Order_ID, Ship_Order, and Ship_Date.
For records that have the same Order_ID, they will also have a Ship_Order
that is sequential. I would like to update all Ship_Order fields to the
lowest Ship_Order where Order_ID is the same and Ship_Date is blank.
I know to use an update query, but the query set up has been giving me fits.
Any help would be appreciated, thanks!
-gary

 

[John Spencer]

Assumptions:
Ship_Order is a number field
Order_ID is a number field
Ship_Date is a DateTime field

UPDATE YourTable
SET Ship_Order = DMin("Ship_order","YourTable","Order_ID=" &
[YourTable].[Order_ID])
WHERE Ship_Date is Null

If you have a lot of records this could be slow. Also, TEST this on a copy
of your data OR backup your data first.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

awesome, thank you!
-gary

Assumptions:
Ship_Order is a number field
Order_ID is a number field
Ship_Date is a DateTime field

UPDATE YourTable
SET Ship_Order = DMin("Ship_order","YourTable","Order_ID=" &
[YourTable].[Order_ID])
WHERE Ship_Date is Null

If you have a lot of records this could be slow. Also, TEST this on a copy
of your data OR backup your data first.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,
I am wanting to run an update query that will update specific records in a
table, and set a certain field to an already existing field, the first one
in
a number sequence.
For example, I have table1 with fields Order_ID, Ship_Order, and
Ship_Date.
For records that have the same Order_ID, they will also have a Ship_Order
that is sequential. I would like to update all Ship_Order fields to the
lowest Ship_Order where Order_ID is the same and Ship_Date is blank.
I know to use an update query, but the query set up has been giving me
fits.
Any help would be appreciated, thanks!
-gary