Grouping Table

[tmyers]

I may be approaching this wrong but I am trying to make a table to group a large list of manufacturers into categories. What would be the best structure for such a table?

Currently, I have a table (tblBusinessMix) that is an imported Excel file taken directly from our business's sales software. It list each manufacturers (mfr) code (generally just the companies name shortened, but not always) along with each customer who bought something from that mfr code. So customer x bought y amount from z manufacturer.

How can I go about grouping various mfr codes under "archetypes"? Like I want the mfr codes A, B, C to go under "Wire" then D, E, F go under "Conduit".

 

[plog]

How can I go about grouping various mfr codes under "archetypes"? Like I want the mfr codes A, B, C to go under "Wire" then D, E, F go under "Conduit".

This is one of those so simple, I must be missing something:

ArchType, MfrCode
A, Wire
B, Wire
C, Wire
D, Conduit
E, Conduit
F, Conduit

What am I missing?

 

[tmyers]

Ironically talking myself through it to write out my question sort of answered my own question.

 

[tmyers]

I am having a problem figuring out the relationship between the two tables. My grouping table (tblMfrGrouping) only consist of two fields, [GroupID] and [GroupName]. I am trying to make a 1 to many relationship to my table [tblBusinessMix] on the FK [GroupID], but am not succeeding.

 

[CJ_London]

without seeing your table structure and relationships and some example data and an explanation of what 'not succeeding' means, not sure we can add anything to what has already been said

 

[plog]

My grouping table (tblMfrGrouping) only consist of two fields, [GroupID] and [GroupName].

Sounds like you don't need a grouping table then. Just store the GroupName instead of the GroupID in that foreign table.

 

[Pat Hartman]

I am trying to make a 1 to many relationship to my table [tblBusinessMix] on the FK [GroupID], but am not succeeding.

If ArchType is in tblBusinessMix you would join to the look up table on that to pick up MfgCode