I'm trying to copy data from one field to another.

[sevenone]

Hi guys, I'm quite new to Access and am stuck trying to do the following.

I have an accounts package that uses Pervasive SQL server. I have linked the tables in it to Access and also linked a DBF table which was made in Lotus Approch, the DBF database works out my selling prices.

what I want to do is, take the selling prices from the DBF linked table and copy them to the inventory table of the accounts package. I have made the necessary relationships in Access but haven't a clue where to go from here.

 

[Golom]

Assuming that you have defined a field in the Inventory table called Price and that the Inventory and DBF tables have a common field called StockCode

UPDATE tblInventory I INNER JOIN Prices P
       ON I.StockCode = P.StockCode

SET    I.Price = P.Price

should do the trick.

 

[sevenone]

Thanks for that Golom, but where do I put this, I don't know anything about visual basic.

 

[jrbarnett]

What Golom has provided isn't Visual Basic code, it is SQL, which is a language used for manipulation of the database tables and the data in it.

To use it, go to Access and the queries tab, create a new query, then go to the View menu and choose SQL.
Copy the code from the above post, paste it into the window and click the red exclamation mark in the toolbar to run the code.

John

 

[sevenone]

OK, The table I want the field updated in is called MultistoreTrn, the DBF table I want to take the prices from is called STOCKITM but, it because the table is linked I can't see the calculated field I need i.e. RetailInc. So I have made a new query that works out the RetailInc price and have called this query "StockitmPrices".

I have created a relationship between the MultistoreTrn Table and the StockItmPrices query.

From what you suggested earlier, I have made a new query in SQL mode like this:

UPDATE MultistoreTrn I INNER JOIN STOCKITMPRICES P
ON I.ItemCode = P.INVENTORY_

SET I.SellInc01 = P.RetailInc;

But when I run the query, it comes up with a promt I.SellInc01 and upon clicking OK I get a warning saying "You won't be able to undo the changes this action query is about to make to the data in the linked table or tables.

Can you tell me if this is OK or have I mucked up?

 

[Golom]

If you are seeing a prompt for I.SellInc01 that means that SQL is unable to find a field named SellInc01 in table MultistoreTrn. You need to go back to the table and make sure that you have the correct field name.

The warning message is a standard warning that appears whenever you run an action query unless you have turned such messages off.

 

[sevenone]

Yes I did mess up, I misspelled SellInc01, it should be SellIncl01.

Thanks.