One to many

[richardplr]

Hi,

I have a inventory check system and I need to create a one to many.

Explanation: I have created a form and I made the product to contain many field such as Month, Data & Stock. So when I search for one product, say example Product Part No: 00403915, so all inventory check record on this part number will list down. The Problem is, I need to set the product part number as index key.

So, please help and see whether anyone can solve this.

Thank you

 

[richardplr]

Hi,

I have a inventory check system and I need to create a one to many.

Explanation: I have created a form and I made the product to contain many field such as Month, Data & Stock. So when I search for one product, say example Product Part No: 00403915, so all inventory check record on this part number will list down. The Problem is, I need to set the product part number as index key.

So, please help and see whether anyone can solve this.

Thank you

Someone please help.

 

[KenHigg]

The reason you can't place multiple records in the 'CheckByDate' table with the same product number is because you have the productcode set up as the primary key when it should be a foreign key.


kh

 

[richardplr]

The reason you can't place multiple records in the 'CheckByDate' table with the same product number is because you have the productcode set up as the primary key when it should be a foreign key.


kh

hI

What is a foreign key. Did I do wrongly, please correct me.

Hope to hear from you

 

[KenHigg]

Do a search in access help on foreign. This will explain it better than I can...

kh

 

[richardplr]

Hello,

Can someone help me. I don't understand what the function of foreign key

Tks

 

[boblarson]

A primary key is something that identifies a unique record. A record is the data in a column or more, also known as a row. For most occasions there should be something unique about every row. This is accomplished by designating a primary key. For example, I could have a products table which has the product number as the primary key for that table. As long as no other single item can have the same product number, you can use that as a primary key for that table.

Primary keys can also be "composite keys." The rule of thumb on using composites (designating more than one column as the primary key) is to use ONLY the columns that make a record unique.

Foreign keys are basically primary keys from another table. For example, If I have a suppliers table, I could have a unique identifier for each supplier (in this case I'll just say it's an autonumber). In the products table, for each product, the supplier "autonumber" would be in the record for each individual product to identify which supplier supplied the product. This number, while a primary key in the suppliers table, is now a "foreign key" in the products table. It is what ties the products table to the suppliers table.

I hope that helps.

 

[richardplr]

A primary key is something that identifies a unique record. A record is the data in a column or more, also known as a row. For most occasions there should be something unique about every row. This is accomplished by designating a primary key. For example, I could have a products table which has the product number as the primary key for that table. As long as no other single item can have the same product number, you can use that as a primary key for that table.

Primary keys can also be "composite keys." The rule of thumb on using composites (designating more than one column as the primary key) is to use ONLY the columns that make a record unique.

Foreign keys are basically primary keys from another table. For example, If I have a suppliers table, I could have a unique identifier for each supplier (in this case I'll just say it's an autonumber). In the products table, for each product, the supplier "autonumber" would be in the record for each individual product to identify which supplier supplied the product. This number, while a primary key in the suppliers table, is now a "foreign key" in the products table. It is what ties the products table to the suppliers table.

I hope that helps.

Sorry, I am too blurr.

Can you show me the example. I have a table name "inventory check by date" how to set foreign key for this table and where to click for foreign key.

May be I am stupid, I cannot understand at all. I know what is Primary key because it has a key icon but where to look for foreign key.

Hope to hear from you.

 

[KenHigg]

Richard - Sorry that I seemed to confuse you even more. FKs (foreign keys), are really not that difficult. In the attached .gif, The deptID in tblEmployees would be considered a fk. There really is nothing in access you have to set, like you would set a pk (primary key). You just have to make sure the data types are compatible.

kh