SQL UPDATE statement question

[tricard]

Good morning,

This is a tough one for me to explain, so if I make no sense, just let
me know :).

I have created a purchase order entry system where there are four
tables that hold all neccessary details of the parts ordered on the
purchase order:

1) tblPurchaseOrder: holds POnumber (primary key), date, closed? etc.
2) tblPurchaseOrderDetails: holds DetailID (primary key), POnumber from
tblPurchaseOrder, PartNumber ordered, delivery date etc.
3) tblPurchaseOrderReceipt: holds ReceiptID (prim. key), DetailID from
tblPurchaseOrderDetails, quantity received, date received etc.

I have created a way to revise a purchase order when neccessary. A new
record is created in tblPurchaseOrder, and the original record is
updated to not current through its field tblPurchaseOrder.fldCurrent.
At this point, the new PO holds no records in tblPurchaseOrderDetails.

A temp table is created and holds all the records from
tblPurchaseOrderDetails corresponding to the original
tblPurchaseOrder.POnumber. The temp table also has a field named
OldDetailID which holds the DetailID from the original
tblPurchaseOrderDetails records.

The user now modifies the temp table and adds or deletes records as
neccessary. Added records obviously have no value in OldDetailID.

Once the user has made all neccessary modifications, then the Update PO
button is clicked and the temp table data is appended to
tblPurchaseOrderDetails. The old records are not deleted or changed,
since it's correspnding POnumber has been made "not current".

Finally, any record in tblPurchaseOrderReceipt that had the original
DetailID must be updated to hold the new DetailID. This is where I am
stuck. Is it possible to create an UPDATE query that can change all
tblPurchaseOrderReciept records with DetailID equalling the old
DetailIDs in tblPurchaseOrderDetails to the new DetailID values in
tblPurchaseOrderDetails.

Thanks in advance

Tim

[Bas Cost Budde]

Rephrasing and condensing the original question to see if I understand
you correctly.

You have a table with IDs in use, and a (temporary) table with pairs of
IDs; you want all IDs in the first table that occur in the second table
to be 'translated', changed, updated.

I haven't been succesful in creating an Update query that does this; it
must have to do with including the right fields from the various primary
keys.

tr*****@gmail.com schreef:

>
Finally, any record in tblPurchaseOrderReceipt that had the original
DetailID must be updated to hold the new DetailID. This is where I am
stuck. Is it possible to create an UPDATE query that can change all
tblPurchaseOrderReciept records with DetailID equalling the old
DetailIDs in tblPurchaseOrderDetails to the new DetailID values in
tblPurchaseOrderDetails.

Thanks in advance

Tim

--
Bas Cost Budde
Holland

[tricard]

LOL. That is exactly what i want to do. I think I might have to use DAO
and recordsets to accomplish this (Which is not the end of the world,
but annoying since I think there should be some SQL way to do this).

Tim

Bas Cost Budde wrote:

Rephrasing and condensing the original question to see if I understand
you correctly.

You have a table with IDs in use, and a (temporary) table with pairs of
IDs; you want all IDs in the first table that occur in the second table
to be 'translated', changed, updated.

I haven't been succesful in creating an Update query that does this; it
must have to do with including the right fields from the various primary
keys.

tr*****@gmail.com schreef:

Finally, any record in tblPurchaseOrderReceipt that had the original
DetailID must be updated to hold the new DetailID. This is where I am
stuck. Is it possible to create an UPDATE query that can change all
tblPurchaseOrderReciept records with DetailID equalling the old
DetailIDs in tblPurchaseOrderDetails to the new DetailID values in
tblPurchaseOrderDetails.

Thanks in advance

Tim

--
Bas Cost Budde
Holland