Brain Dead in Designing Tables - This Is Tough 2

[forrest33]

I have a big list of Companies, CompDirectory. Each of them has at least one NAICS code (six digit industry code)but no more than 6.

CompID (primary key)
CompName
Street
City etc.
NAICS1
Naics2
Naics3
Naics4
Naics5
Naics6

I've got a second table, ProductCategory, where each NAICS code has its corresponding description/title:

ProductId (primary Key)
ProductTitle (eg: Battery Mfg)
NAICS (eg: 332620)

I need to be able to query by ProductTitle and view each company that makes that product.
One company can have up to 6 Naics and one Naics can have multiple companies, hence, it's a many to many, but cannot figure out how to design the damn tables and keep thinking I need a third table.
Maybe you guys can help, I am stuck on it for a week now and the thick fog has not lifted yet...

 

[forrest33]

Nope, got sintax errors on qryNormalized. Does not work.

What if I kept the working qryNormalized and ran a Make-Table query for my CompanyNAICS table? I could then delete the original CompanyNAICS table and the NAICS1, 2 etc. from the CompanyTbl, for nomalization. Lemme know what you think. And thanks for hanging on.

 

[lespaul]

so you mean you are trying to insert these records into your ORIGINAL Company Table?

I'm really confused.

You need three tables (I would rename all your old tables _OLD) and start from scratch:

tblCompany
CompanyID (PK)
CompanyAddress
CompanyCity
(any other information regarding the COMPANY - phone number, contact name, fax number - DO NOT ADD ANYTHING ABOUT THE NAICS)

tblNAICS
NAICS (PK)
ProductDescription

tblCompanyNAICS
CompanyID
NAICS

Are these the three tables that you have?

Now to get the information from your _OLD tables to the NEW EMPTY tables you can run some INSERT queries.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[forrest33]

Those are my three tables, the tblCompanyNAICS is based on the qryNormalized that you helped me with. I ran the qryNormalized and made a table out of it, tblCompanyNAICS, populated and everything. Then, I deleted the tblCompany fields for NAICS1, 2 etc, like you suggested, for data normalization. Everything went smoothly, but (#$#@@$%^$%$%$*%&#$#!!!!) I cannot get a One-to-Many relationship between tblCompanyNAICS and tblNAICS, I can only link them without Referential Integrity. This is what I'm working on right now. Thanks again for staying with me.

 

[pbrodsky]

To get the relational integrity you just need to make sure that the keys are indexed. Specifying the primary keys in each table should be good enough.

 

[forrest33]

Thought so too, but it ain't enough. All 3 tables have indexed PK, yet the relationship btw. 2 of them (see above)is not working

 

[pbrodsky]

What do you have as the primary key for tblCompanyNAICS?

 

[PHV]

tblCompanyNAICS[tt]
CompanyID indexed with duplicates (FK referencing tblCompany)
NAICS indexed with duplicates (FK referencing tblNAICS)[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[forrest33]

Yep,PH, that's exactly what I have. Cannot Enforce Referential Integrity btw CompanyNAICS and ProductNAICS, hence no One-to-Many Relationship, can only have a simple link btw the two.

 

[forrest33]

For CompanyNAICS - composite PK NAICS and CompanyID, both indexed and duplicates OK.

 

[PHV]

For the one-to-many relationships, the many side is tblCompanyNAICS.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[forrest33]

PHV,
Yes, that's what I was trying to do, without success, since I am getting the following error message:

Data in the table"CompanyNAICS" violates referential integrity rules.
For example, there may be records relating to an employer in the related table but no record for the employee in the primary table.
Edit the data so that records in the primary table exist for all related records.
If you want to create the relationship without following the rules of referential integrity, clear the Enforce Referential Integrity check box.

For the design of the relationship, please see my previous posts.

 

[lespaul]

So you need to check the data.


SELECT * FROM CompanyNAICS WHERE CompanyID NOT IN (SELECT CompanyID FROM Company)

SELECT * FROM CompanyNAICS WHERE NAICS NOT IN (SELECT NAICS FROM NAICS)

Do these queries return any results?

leslie

 

[forrest33]

Did the check. No return. All fields are populated.

 

[forrest33]

The data in the ProductNAICS table is flawed. Hence the lack of referential integrity. The person entering the information is checking it right now and she found two typos already.
I'll keep you guys posted.
Thank you for your patience.