Simple Linking Tables Problem

[kailoont02]

It's quite embarassing but I have no idea how to do it.

I have two tables, one with a list of products with info and one with a list of suppliers with info.

Is it possible to link the two tables so that every time I look at a product record, I am able to get the supplier information for that record?

The problem is that the tables have to be separate and I have to link them.

Is this possible??

 

[glalsop]

If it doesn't already exist, you have to add a SupplierID field to the Product table, then link on this field.

-Gary

 

[kailoont02]

yup thats what I did, however all I got in the supplier table were some "+" beside the records and when I click them I get the products provided by that supplier..

unfortunately thats not my aim..

thanks alot for the tip though...

is there any other way to link the tables?

 

[glalsop]

If you can see all of the products offered by the supplier then the tables are linked properly... So you can see the supplierID on the products table but you want to see the supplierName instead? If this is the case, then open the products table in design view and click on the SupplierID field. Then at the bottom, click on the Lockup tab. In the Row Source type property, select Table/Query. Now click in the Row Source field and click the ellipses next to it. This bring up the Query design grid. Add the Supplier table to the grid and add the SupplierID and SupplierName fields. Right-click the query properties and select Unique Records -> Yes. Close the query design grid and choose yes when prompted about using that query as the source.

Hope that helps,

-Gary

 

[kailoont02]

thanks for the help and your time, and I understand what you are trying to say Gary, but I can't seem to find the exact items you are pointing out, I use Acess 2000 does that make any difference?
Even if it does is it possible to explain it again?

thanks

 

[kailoont02]

and I need to see the Supplier name FOR that product record, not a list of suppliers

 

[glalsop]

Access 2000 doesn't make a difference, that's what I'm using, too. Just to make sure I understand clearly - are you trying to see the Supplier name on the Product table (as opposed to the SupplierID), or do you just need it to show up on a form instead of the SupplierID field for each product?

-Gary

 

[kailoont02]

yes i need to see the supplier name for each product in the product table, using the supplier ID.

since one supplier can provide more than one product, I need to link the tables to do this, instead of typing the supplier name as an extra field in the product table.

 

[glalsop]

Hi there -

This is pretty easy to do. You need to create a lookup field for the Product table. Open the Product table in design view. Then at the top click Insert -> Lookup Field, and let the Wizard do the work for you.

Here are step-by-step instructions from MSDN:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304463&Product=acc2000

Make sure you de-select Hide Key Column when the Wizard asks you how you want to size the columns or you may receive an error.

Good luck.


-Gary