Updating a field based on another table

[jmagestro]

What I am trying to do is easily done in microsoft Access using an update query. I want to update a field in one table/dbf with the same field in another/joined table/dbf.

Example. I have a table (Table A) with PartNumber, Desc, Manufacturer, Vendor, Cost, RetailPrice, Deptarment, and UPC_Code - PartNumber being the primary key/index.

I then get a list from 'Manufacturer A' of products whose cost are changing. So Table B contains PartNumber and Cost. I want to join the tables on PartNumber and replace Cost from Table A with Cost from Table B.

I have been looking through forums and other resources for the last several days and have yet to find anything. Perhaps I am just not using the correct terminology. I use both FoxPro 2.6 and Access regularly. We have an old Unix system on which runs FoxPro 2.6 for Unix. So we have on our other computers FoxPro 2.6 for Windows, Visual FoxPro, and Microsoft Access.

Any help or thoughts anyone might have would be appreciated.

 

[TamarGranor]

In VFP 9, you can use SQL UPDATE to what you want quite easily. In earlier versions of FoxPro/VFP, it's a little trickier to do with UPDATE.

In any version, you can open the tables, set a relation between them and use the REPLACE command to do this (off the top of my head):

USE TableA IN 0
USE TableB ORDER PartNumber IN 0

SELECT TableA
SET RELATION TO PartNumber INTO TableB

REPLACE Cost WITH TableB.Cost FOR NOT EOF("TableB")

Tamar

 

[JRB-Bldr]

One addition to Tamar's response...

In order to use the Order portion of the following code
USE TableB ORDER PartNumber IN 0
TableB would have needed to have an Index built whose Expression was PartNumber

* Somewhere before the above code is called, you would have needed to do the following:
USE TableB IN 0 EXCLUSIVE
INDEX ON PartNumber TAG PartNumber
USE

Good Luck,
JRB-Bldr

 

[jmagestro]

Awesome. Worked perfectly. You 2 are my heroes of the week.

 

[JRB-Bldr]

Mike - you are correct. I left out a line of code.

USE TableB IN 0 EXCLUSIVE
[B]SELECT TableB[/B]
INDEX ON PartNumber TAG PartNumber
USE

JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com