Copy Data from One Column into another of a different table

[shreyans.ghia]

Hello,

I have two tables pertaining to the same database.

Table [Purchase_Order_Details] amongst many fields have Columns - (Purchase Order #) which is the Primary Key and (Work Order #)

My second table [Order Details] also has a column (Work Order #) . Now I have added another column to this table i.e. (PurchaseOrderNo) - this column is blank as of now.

However I would like to run a query to fill the (PurchaseOrderNo) column depending upon the (Work Order #) which is present both the tables.

Any suggestions?

Thanks.

Shreyans.

 

[pr2-eugin]

So by your description, I find the relationship to be fine, why do you want to add this column into the child/many side of the relationship table?

 

[shreyans.ghia]

I would want to join the two tables .. that is why it is important that the values in the column (PurchaseOrderNo) in [Order Details] is filled up. For each (Purchase Order #) in the (Purchase_Order-Details) table, there would be some information which is present in the [Order Details] table. Hence can you suggest how do i do that?

 

[pr2-eugin]

Why could you not JOIN them based on the WorkOrder#, as they are present in both tables? Or am I missing something here? Can you show a screenshot of your relationships?

 

[shreyans.ghia]

Dear Paul,

The thing is I had to modify the entire structure of my database to incorporate certain changes. So now the way this is structured is 1 Work Order can have multiple Purchase Orders. Hence the column (Work Order #) is PK in the table [Projects Database] which is linked to [Purchase_Order_Details]. Now every Purchase Order has some more information which would go in to the [Order Details] Table. Finally there is a Query [Order Information] which pulls data from the table [Order Details]. Hence I need the PurchaseOrderNo Column in [Order Details] table to be filled in.

Thanks for your efforts.

 

[shreyans.ghia]

Oh sorry , the image didnt come... I'll try again.

 

[pr2-eugin]

If you need to post a picture or database before 10 posts...

 

[shreyans.ghia]

I have no idea why it still doesnt work... i am really sorry , I am new to this ..although I did zip it before pasting the link.

 

[pr2-eugin]

Shreya, look at the steps to attach file..

PS: I would advice you to remove email to avoid spamming..

 

[shreyans.ghia]

Thanks a lot for your detailed explanation. This should work now.

 

[pr2-eugin]

Try this Query, air coded... not tested..

UPDATE [Order Details] SET [Order Details].PurchaseOrderNo = [Purchase_Order_Details].[Purchase Order #] WHERE [Order Details].[Work Order #]=[Purchase_Order_Details].[Work Order #];

 

[shreyans.ghia]

Actually I tried this earlier as well, it doesn't work. It gives an error "Enter Parameter Value" for "Purchase_Order_Details.Purchase Order #"

 

[pr2-eugin]

Did you try the one I have given? With the square brackets?

 

[shreyans.ghia]

Yes I did try the one given by you as well. Cant figure out whats wrong with that.

 

[shreyans.ghia]

Well.. I have found an alternate solution to my problem. Although I wasn't able to run the update query, what I have done is Joined my 2 tables i.e. [Order Details] and [Purchase Order Details] using LEFT JOIN and then executed a "Make Table" query. This way I was able to get what I wanted, although I wanted to learn about doing the same via Update Query.

Anyway - thank you very much for all your efforts.

Shreyans.

 

[pr2-eugin]

Well glad you have it sorted.. Well since you have not included the two tables, it would prompt for the Enter Parameter Value.. So you should be JOINING the two tables, but when you JOIN, sometimes the Query cannot be updatable.. So I think that is where your problem is.. Sorry was not of great help there..