Inputting data from table into different table using 1 field

stevey03

New Member
Joined
Nov 28, 2003
Messages
25
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

New Member
Joined
Nov 28, 2003
Messages
25
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,897
Messages
5,621,498
Members
415,845
Latest member
marej123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top