Duplicating table entries

[Adzi]

I have a table named tblProduct,
I have a table names tblProductSpec,

tblProduct contains basic info, tblProductSpec contains infomation linked with outer tables. What I need to is have the product ID's duplicated in tblProductSpec. Is it possible to do this via relationships etc?

Any help greatly appreciated.

-Adzi

 

[KjWhal]

I have a table named tblProduct,
I have a table names tblProductSpec,

tblProduct contains basic info, tblProductSpec contains infomation linked with outer tables. What I need to is have the product ID's duplicated in tblProductSpec. Is it possible to do this via relationships etc?

Any help greatly appreciated.

-Adzi

I don't know what your table design looks like but, you can run a query to do this.

INSERT INTO tblProductSpec (ID)
SELECT ID FROM tblProduct

 

[jwhite]

Hi adzi,

It sounds possible that your database design is not "Normalized". Searching the web for "Microsoft Access Database Normalization" will get many good hits.

In tblProduct, you should have a Primary Key (AutoNumber) named something like [ProductID]. This is not the Product Number, but the ID used by Access to make the record unique for table relationships. In tblProductSpecs, similar design with a Primary Key [ProductSpecID], AND a Foreign Key [ProductID].

tblProduct
----------
ProductID PK AutoNumber
ProductNum Number (no duplicates)
.....

tblProductSpecs
----------------
ProductSpecID PK AutoNumber
ProductID Number

Now, using Access <Tools><Relationships> (in ToolBar), add these two tables. Click-on and drag ProductID from tblProducts to same field in tblProductSpecs. It will create a One-To-Many join from tblProducts to tblProductSpecs.

If you have pre-existing data in these tables, you may be told the relationship cannot be made. The culprit would be ProductID values in the first table do not exist in the second, and vice-versa.

Try it out, and report what your results are here. Also, please do list your fields in each table (in format like above) so we can better help you.

 

[KjWhal]

Hi adzi,

It sounds possible that your database design is not "Normalized". Searching the web for "Microsoft Access Database Normalization" will get many good hits.

In tblProduct, you should have a Primary Key (AutoNumber) named something like [ProductID]. This is not the Product Number, but the ID used by Access to make the record unique for table relationships. In tblProductSpecs, similar design with a Primary Key [ProductSpecID], AND a Foreign Key [ProductID].

tblProduct
----------
ProductID PK AutoNumber
ProductNum Number (no duplicates)
.....

tblProductSpecs
----------------
ProductSpecID PK AutoNumber
ProductID Number

Now, using Access <Tools><Relationships> (in ToolBar), add these two tables. Click-on and drag ProductID from tblProducts to same field in tblProductSpecs. It will create a One-To-Many join from tblProducts to tblProductSpecs.

If you have pre-existing data in these tables, you may be told the relationship cannot be made. The culprit would be ProductID values in the first table do not exist in the second, and vice-versa.

Try it out, and report what your results are here. Also, please do list your fields in each table (in format like above) so we can better help you.

He would only need to add a synthetic key to tblProductSpecs IF there is too be more than one Spec per product. Why did you not consider the possibility that this could be a 1 to 1 relationship?