SQL UPDATE statement question

[tricard@gmail.com]

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) tblPurchaseOrde r: holds POnumber (primary key), date, closed? etc.
2) tblPurchaseOrde rDetails: holds DetailID (primary key), POnumber from
tblPurchaseOrde r, PartNumber ordered, delivery date etc.
3) tblPurchaseOrde rReceipt: holds ReceiptID (prim. key), DetailID from
tblPurchaseOrde rDetails, quantity received, date received etc.

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

A temp table is created and holds all the records from
tblPurchaseOrde rDetails corresponding to the original
tblPurchaseOrde r.POnumber. The temp table also has a field named
OldDetailID which holds the DetailID from the original
tblPurchaseOrde rDetails 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
tblPurchaseOrde rDetails. The old records are not deleted or changed,
since it's correspnding POnumber has been made "not current".

Finally, any record in tblPurchaseOrde rReceipt 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
tblPurchaseOrde rReciept records with DetailID equalling the old
DetailIDs in tblPurchaseOrde rDetails to the new DetailID values in
tblPurchaseOrde rDetails.

Thanks in advance

Tim

[Bas Cost Budde]

Re: SQL UPDATE statement question

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.

tricard@gmail.c om schreef:

>
Finally, any record in tblPurchaseOrde rReceipt 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
tblPurchaseOrde rReciept records with DetailID equalling the old
DetailIDs in tblPurchaseOrde rDetails to the new DetailID values in
tblPurchaseOrde rDetails.
>
Thanks in advance
>
Tim
>

--
Bas Cost Budde
Holland

[tricard@gmail.com]

Re: SQL UPDATE statement question

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.
>
tricard@gmail.c om schreef:

Finally, any record in tblPurchaseOrde rReceipt 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
tblPurchaseOrde rReciept records with DetailID equalling the old
DetailIDs in tblPurchaseOrde rDetails to the new DetailID values in
tblPurchaseOrde rDetails.

Thanks in advance

Tim

>
--
Bas Cost Budde
Holland