linking question

[rainbows]

i am confused as to which is correct because i have been told many different things

please see below

you can see i have linked stocklist MATERIALID to Product detail Material some tell me this is correct some tell me it should be

Material to material which one should it be and why and why not the other

thanks steve

 

[MajP]

Probably Neither. Product Detail likely should not have Material (assume that is a descriptive Name) but a key which I guess is MaterialID.
However, even that looks wrong. I would think StockList has a StocklistID and there is another tblMaterial with information only about the material.
You may need to describe in words what these tables contain and other tables.

 

[arnelgp]

first.. Re-start your pc..

 

[rainbows]

stocklist table

there are 96 products and each product has a Bill of materials attached to it . i select the materail from the stocklist then that populates the stock number , product ID and i enter the qty required for that product

 

[MajP]

You have two different fields with different meaning in two tables with the same name, and that is why you are confused

Material in the stock list table is a Descriptive string field. I assume a name

Material in the Product detail table is numeric field and has different information thanMaterial in the Stock list table. I assume that is the foreign key and should like to MaterialID. That is confusing to anyone looking at this and just a bad design. I would call it MaterialID_FK
And you always link PK to FK

 

[MajP]

To avoid these questions and issues I do personally do the following others may disagree.

1. Never name the primary key the same in different tables. Some people just call it ID in every table. Instead
AuthorID, MaterialID, BookID, CarID .... StockListID
2. The Foreign key (linking field) gets FK added to it. Often people resuse the name and the linking field has the same name as the Primary key.
MaterialID in StockList table and MaterialID in Product Detail
I would do
MaterialID in StockList table and MaterialID_FK in Product Detail

Then when I see in code or a query "MaterialID", I now which table it comes from.
However I also have my PK names match my table name
So I would actually have
StockListID and StockListID_FK

So I can see those field names and code or a query and know a lot. What table they came from, what is the PK, FK and how they should join.

 

[rainbows]

am i not correct in making the material a number field in product details so it can link to the materialid in the stocklist ? then use the combo box to give the materail description

 

[MajP]

Yes, but please rename that field if that is the plan.
In the combobox you select the MaterialID, and Material but hide the first column
column widths: 0;2
and bind the combo to column 1

This also assume that Material is a unique field.

 

[Pat Hartman]

Unless you are working with bad data that you are trying to clean, the linking fields are ALWAYS the PK of one table to the FK in the other table. That means that if the PK is an autonumber, which it usually is, the FK is always a long integer. They will ALWAYS match in data type. What the combo shows is normally a text value rather than the numeric FK value. The combo will be bound to the field named MaterialID. That means that MateralD is the value that will be saved to the table. The RowSource in the combo, is what provides the MaterialName which is text and that is usually what you want to pick from and see. So, in your case.

1. ALL tables must have a a primary key, usually an autonumber. Otherwise, you will run into joins that are not updateable.
2. Each PK should have, for your own sanity and the sanity of others who try to help you, a unique name. Convention, as MajP says, is to include the name of the table so for tblMaterial, the PK would be named MaterialID. Then in the tblProductDetail, the PK would be ProductDetailID and the FK which points to a material would be MaterialID or MaterialID_FK. If you call the table tblStockList, then you might consider using StockID as the name rather than material. It just causes confusion when you use different names all over the place. Consistency is your friend and programming will be ever so much easier if you do not constantly have to rack your brain for the names of objects.
3. Once you have defined all the PK's Then go to the Relationship window and define the relationships by drawing the line between the PK of one table to the FK in a different table and enforce RI. This will help to ensure that "child" data such as tblProductDetails is properly related to tblMaterial. Also, you might want to consider naming that table tblMaterialDetails. Again, use your creativity for building a nicely working interface rather than seeing how many names you can use for the same type of object.
4. Do NOT use embedded spaces or special characters (&,%, $, etc) in ANY object names. You have no idea the weirdness this causes in code plus the extra typing because you have to encase all your names in square brackets [] when they do not conform to VBA standards. VBA CANNOT use non-conforming object names so it "helps" you by renaming them

 

[rainbows]

thank you for your help and great advise
steve