Inputting data from table into different table using 1 field

[stevey03]

Tried so many different ways to do this. I'm trying to get details from a table to show up in another table, dependant on a value that I have entered in a particular field. the database is like a basic shop pos system. The two tables contain near identical fields.

Stock_info(ID, name,buy price, sell price, profit per sale, stock value, stock limit)
Item_details(TRANSACTIONID, item id,item name, quantity, price)

The item name is the field in the Item_details table that I want to use to import the information. I want it to import the Item ID, and sell price into the item ID table as the item Id and the price.

Really stuck on how to do this. Anyone has any suggestions, could post a reply - would be appreciated.

Steve

 

[texasalynn]

do the two tables have a common field that you can link the two together? If so then you can create a query:

Update a table based on values in another table by using a query

1. Create an update query that contains the table you want to update and the table whose values you want to copy. For information about how to create an update query, click .


2. If the tables aren't already joined, join them on the fields that have related information. For information on creating joins, click .
For example, if you want to copy data from the ProductName field in the Products table to a field in another table, join the two tables on the primary key, which might be called ProductID.

3. In the Update To cell for the fields you want to update, type an expression with the following syntax:
[tablename].[fieldname]

where tablename and fieldname are the names of the table and field that contain the data you're copying. For example, if you are copying data from the ProductName field in the Products table, you would type [Products].[ProductName] in the Update To cell.

This is from the help menu on update_query

HTH
texasalynn

 

[stevey03]

Not quite what I was looking for but I'll try that. I was hoping that I could do it all within the form, so that you enter the name of the product, and the ID number, Price automatically appear in the form. I'll give the update query a go though.

Cheers Steve

 

[Norie]

Steve

Why not base your form on a query that joinsthe 2 tables rather than on a table?

By the way you really don't need to have data repeated in 2 tables.