How to Design/Link Tables - Logic Braindead

[forrest33]

Ok, here it goes:

Got these tables:

CompanyTbl:
CompanyID (PK)
Name
Street
etc.
NAICS1(this is a standard industrial code, a 6 digit number telling about their product/service)
NAICS2
NAICS...6 (company can have 1-6 NAICS codes)

A layman has no clue what these NAICS code are, so each and one of them has a descriptive title, like NAICS 32330 = Canned Vegetables Manufacturing
For this puropse, I designed a second table:

ProductTbl
ProductTitle (PK)
NAICScode

Purpose:1. Be able to query by ProductTitle and get all companies with that profile.
2. Be able to retrieve Company ID with all its NAICS code/s and corresponding ProductTitle/s.
Now one company can have up to 6 NAICS codes and one NAICS code can appear under the descirption of multiple companies. So a many-to-many relationship. But how? A third table, probably....But how?

 

[Sergeant]

Perhaps an intermediate table where you can tie the companyID to as many Product Type codes as you wish:

tblCompProduct:
CompProductID (PK)
CompanyID
NAICScode

You can add an infinite number of records there that tie a company to varous product types.

 

[forrest33]

Thanks, Sergeant,

Do I leave the NAICS1, 2 fields etc in the CompanyTbl?
What is CompanyProductID exacly in the CompProductTbl?
Sorry for being a pain, but been stuck on this for a good few days...

 

[Sergeant]

Yeah, the idea is you'd NOT store the NAICS data in your Company table. You should make a copy of your db to try this...
CompanyProductID is just a primary key for the intermediate table. You don't really need it. In fact it might be best to leave out that column and instead make a composite primary key using the CompanyID and the NAICScode. (both fields together=the primary key...select one field, hold shift and select the other and then click the primary key icon in the toolbar)

 

[Sergeant]

BTW, keep your NAICS fields in the main table for now so that you can use them to update your intermediate table when you get it built.

 

[forrest33]

Sergeant,

Does not work.
Here's what I did:
CompanyTbl:
CompanyID (PK)this PK linked in One-to-Many with the CompanyID (CPK) in the intermediate table
Name
Street
etc.
NAICS1)
NAICS2
NAICS...6

intemediate table is
CompanyNAICStbl
CompanyID(CPK)
NAICScode(CPK) this linked in Many-to-One with the NAICS in the third table,
ProductNAICSTbl
Title
NAICS(PK)
This cannot work, the NAICS codes are spread in 6 columns, see CompanyTbl, as 1 Company can have up to 6 NAICS.
I am going in circles and feeling really dumb, it's like my little brain has reached its limits. Very frustrating.
Thanks for your help, Sergeant.

 

[Mile-O]

You need three tables as you have a many-to-many relationship.

This structure should simplify it:

tblCompanies
CompanyID (Autonumber, Primary Key)
CompanyName
...other company info

tblProducts
ProductID (Autonumber, Primary Key)
ProductName
..other product info

tblCompaniesToProducts
CompanyID (Number, Foreign Key)
ProductID (Number, Foreign Key)

These two fields both become the composite primary key for this third table.

 

[forrest33]

SJ,
The three tables part I got, what I cannot solve is how to link the up to 6 Products/NAICScodes for each company into the relationships.

Von mir, noch keine Taten!

 

[Mile-O]

SJ,
The three tables part I got, what I cannot solve is how to link the up to 6 Products/NAICScodes for each company into the relationships.

Put the three tables in the relationships window, link the two ProductID keys and then link the two CompanyID keys. Enforce referential integrity and set it to Cascade Delete.

On a form, you would need a form/subform combination to represent it.

If you only want six codes then that will be a custom restriction done with a bit of VBA by yourself. The tables only simulate the relationships; the restrictions should be made by the designer.

 

[forrest33]

Thanks, SJ,

That's what I did. For some reason though, the linked fields come up empty in all tables of the relationship. I must be doing something wrong, but cannot see what, this is pretty basic.

Noch keine Taten!

 

[forrest33]

This is it, where is the mistake?

CompanyTbl
CompanyID(PK) - one to many w. CompanyID in CompanyNAICStbl
CompName
Street
City
NAICS1
NAICS1
NAICS3
NAICS4
NAICS5
NAICS6

NAICSTbl
Title
NAICS(PK) - one to many w. NAICS in CompanyNAICStbl

CompanyNAICSTbl
CompanyID (CompositePK)
NAICS (CompositePK)
This table is not populated. The other two are.

 

[Sergeant]

The idea is to have 1 or more records in the linking table (tblCompanyNAICS) for each company, that links a company to one or more product categories in tblNAICS.

 

[forrest33]

This is what I have done, with some success only:
I created an Union query that normalizes that should normalize the data, taking the CompanyID field and assigning to it each NAICS code for a certain company
SELECT CompanyID, NAICS1 from 06CompanyDirectorywNaics where NAICS1<>
"
UNION
Select CompanyID, NAICS2 from 06CompanyDirectorywNaics where NAICS2<>
"
UNION
Select CompanyID, NAICS3 from 06CompanyDirectorywNaics where NAICS3<>
"
UNION
Select CompanyID, NAICS4 from 06CompanyDirectorywNaics where NAICS4<>
"

UNION Select CompanyID, NAICS5 from 06CompanyDirectorywNaics where NAICS5<>
"
UNION
Select CompanyID, NAICS6 from 06CompanyDirectorywNaics where NAICS6<>
";
This query has two columns, CompanyID and NAICS1 for some reason. It shouldn't be NAICS1.
It only works partially, it takes only the NAICS1, NAICS3 and NAICS5 for each company, cannot figure out why, but I'm thinking of renamig the NAICS fields.
This query (qryNormalized) is appended to the CompanyNAICS table, so I can delete the NAICS1, NAICS2, NAICS3 from the CompanyTbl.

INSERT INTO CompanyNAICS
SELECT qryNormalized.*
FROM qryNormalized;
This query does not run, it gives me an error message regarding the NAICS1 column in the qryNormalized.
I'm reading more SQL tutorials, maybe I'll get it right.