"n" levels of Atrributes for Product Coding convention, so .....

[dekker]

"n" levels of Atrributes for Product Coding convention, so .....

I'm new to Access and not sure if Access is the tool, given the
relational nature. Anyway, here's what I need to do.

Classify our products, all 18,544 of them. They fall in 11 major
categories, up to 34 subcategories for each major category, each
subcategory can have up to 25 columns with up to 90+ fields per
column, and so forth, for 8 distinct fields to describe a single
product and derive an associated unique identifying character string
(part number).

The challenge is to use comboboxes to classify each item. New items
are added frequently, as well as some are dropped, resulting in about
500 new items per year.

The need arises from inconsistency in naming/description of each item.
Over time it gets ugly. No two people do it the same way.

Right now it looks like just over 150 tables with a minimum of 6
columns and up to 63 columns with a maximum of 90+ fields, except one
table which has 3 columns with 301 fields.

The column heading describes the applicable attributes, while table
name describles the hierarchial placement, hence cascading
synchronized comboboxes.

Each combobox will have 2 columns, the 1st column will contain a
character and the 2nd column will contain a word or phrase. I need to
somehow relate/identify these 2 columns to the selection made from the
preceeding combobox selection which may or may not be in the same
table. Hyperlinking is the general idea. The selected field sends me
to the the correct table and the appropriate columns.

The form(s) for assigning this product classification consist of:
------------------------------------------------------------------------------------------------

txtItemNo from tblItem - unique number generally referenced
txtItemDescription from tblItem - noun description not unique

The 2 generated text boxes will have the follwing information :
1.) txtbox_1 is a full description field with the concatenated
words/characters from at least 6 of the 8 fields using the 8
sequential comboboxes. 2nd column in combobox
2.) txtbox_2 is for 8 characters concatenated from all 8 fields using
the 8 sequential comboboxes. 1st column in combobox

-------------------------------------------------------------------------------------------------------------

While I'm asking for the moon, how do I get the column 1 values to go
to txtbox_1 and the column 2 values to go to txtbox_2

=[cboCategoryView] & ", " & [cboCategorySection] & ", " &
[cboSectionID] & ", " & [cboProduct] & ", " & [cboProductID] & " X " &
[cboProductAttribute]& ", " & [cboStatus] & ", " & [cboMarkup]
-------------------------------------------------------------------------------------------------------------
I'm sure I've left something out ..........
Like queries, lack of primary keys and so on.

Thanks
dekker

 

[Tom Ellison]

Dear Dekker:

The way I use it, Access should be a fine tool to use in your product.
But that means using the MSDE back end and an ADP front end. This
scales seamlessly to SQL Server for the production environment if the
volume or user load demands it.

However . . .

I'm not sure I understand all you have written. For example, you say:

each subcategory can have up to 25 columns with up to 90+ fields per
column

In the terminology to which I'm accustomed, a column and a field are
two words for the same thing. Also, 90+ fields would be a very large
number for any one table. Depending on your experience with database
design, this may be amenable to normalizing design principles, but
without some considerable details, that's just speculation at this
point.

For a project of considerable scope (and likely expense as well) a
thorough design review should be undertaken before any steps such as
choosing your final environment would be appropriate. If you are not
familiar with this process, I heartily suggest you get some help
before leaping into this, especially if the database is to produce
results of critical importance to some business.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

"n" levels of Atrributes for Product Coding convention, so .....

I'm new to Access and not sure if Access is the tool, given the
relational nature. Anyway, here's what I need to do.

Classify our products, all 18,544 of them. They fall in 11 major
categories, up to 34 subcategories for each major category, each
subcategory can have up to 25 columns with up to 90+ fields per
column, and so forth, for 8 distinct fields to describe a single
product and derive an associated unique identifying character string
(part number).

The challenge is to use comboboxes to classify each item. New items
are added frequently, as well as some are dropped, resulting in about
500 new items per year.

The need arises from inconsistency in naming/description of each item.
Over time it gets ugly. No two people do it the same way.

Right now it looks like just over 150 tables with a minimum of 6
columns and up to 63 columns with a maximum of 90+ fields, except one
table which has 3 columns with 301 fields.

The column heading describes the applicable attributes, while table
name describles the hierarchial placement, hence cascading
synchronized comboboxes.

Each combobox will have 2 columns, the 1st column will contain a
character and the 2nd column will contain a word or phrase. I need to
somehow relate/identify these 2 columns to the selection made from the
preceeding combobox selection which may or may not be in the same
table. Hyperlinking is the general idea. The selected field sends me
to the the correct table and the appropriate columns.

The form(s) for assigning this product classification consist of:
------------------------------------------------------------------------------------------------

txtItemNo from tblItem - unique number generally referenced
txtItemDescription from tblItem - noun description not unique

The 2 generated text boxes will have the follwing information :
1.) txtbox_1 is a full description field with the concatenated
words/characters from at least 6 of the 8 fields using the 8
sequential comboboxes. 2nd column in combobox
2.) txtbox_2 is for 8 characters concatenated from all 8 fields using
the 8 sequential comboboxes. 1st column in combobox

-------------------------------------------------------------------------------------------------------------

While I'm asking for the moon, how do I get the column 1 values to go
to txtbox_1 and the column 2 values to go to txtbox_2

=[cboCategoryView] & ", " & [cboCategorySection] & ", " &
[cboSectionID] & ", " & [cboProduct] & ", " & [cboProductID] & " X " &
[cboProductAttribute]& ", " & [cboStatus] & ", " & [cboMarkup]
-------------------------------------------------------------------------------------------------------------
I'm sure I've left something out ..........
Like queries, lack of primary keys and so on.

Thanks
dekker

 

[dekker]

Hi Tom

Thanks for the input. I know that using fields and columns that isn't
correct, so perhaps this may clarify my usage. Column
Headers/FieldNames define the values that are placed in a particular
row , some Excel terminology is more useful here, R2C7 has a
crtiria/value of 2-1/2" in a particular table, but all of the values
in that column are (as defined by it's fieldname/column header)
applicable to that column only .
This mainly is because the information was originally created in
Excel.

Basically I'm trying to establish a naming convention.

Does that clarify it any ?

dekker

Dear Dekker:

The way I use it, Access should be a fine tool to use in your product.
But that means using the MSDE back end and an ADP front end. This
scales seamlessly to SQL Server for the production environment if the
volume or user load demands it.

However . . .

I'm not sure I understand all you have written. For example, you say:

each subcategory can have up to 25 columns with up to 90+ fields per
column

In the terminology to which I'm accustomed, a column and a field are
two words for the same thing. Also, 90+ fields would be a very large
number for any one table. Depending on your experience with database
design, this may be amenable to normalizing design principles, but
without some considerable details, that's just speculation at this
point.

For a project of considerable scope (and likely expense as well) a
thorough design review should be undertaken before any steps such as
choosing your final environment would be appropriate. If you are not
familiar with this process, I heartily suggest you get some help
before leaping into this, especially if the database is to produce
results of critical importance to some business.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

"n" levels of Atrributes for Product Coding convention, so .....

I'm new to Access and not sure if Access is the tool, given the
relational nature. Anyway, here's what I need to do.

Classify our products, all 18,544 of them. They fall in 11 major
categories, up to 34 subcategories for each major category, each
subcategory can have up to 25 columns with up to 90+ fields per
column, and so forth, for 8 distinct fields to describe a single
product and derive an associated unique identifying character string
(part number).

The challenge is to use comboboxes to classify each item. New items
are added frequently, as well as some are dropped, resulting in about
500 new items per year.

The need arises from inconsistency in naming/description of each item.
Over time it gets ugly. No two people do it the same way.

Right now it looks like just over 150 tables with a minimum of 6
columns and up to 63 columns with a maximum of 90+ fields, except one
table which has 3 columns with 301 fields.

The column heading describes the applicable attributes, while table
name describles the hierarchial placement, hence cascading
synchronized comboboxes.

Each combobox will have 2 columns, the 1st column will contain a
character and the 2nd column will contain a word or phrase. I need to
somehow relate/identify these 2 columns to the selection made from the
preceeding combobox selection which may or may not be in the same
table. Hyperlinking is the general idea. The selected field sends me
to the the correct table and the appropriate columns.

The form(s) for assigning this product classification consist of:
------------------------------------------------------------------------------------------------

txtItemNo from tblItem - unique number generally referenced
txtItemDescription from tblItem - noun description not unique

The 2 generated text boxes will have the follwing information :
1.) txtbox_1 is a full description field with the concatenated
words/characters from at least 6 of the 8 fields using the 8
sequential comboboxes. 2nd column in combobox
2.) txtbox_2 is for 8 characters concatenated from all 8 fields using
the 8 sequential comboboxes. 1st column in combobox

-------------------------------------------------------------------------------------------------------------

While I'm asking for the moon, how do I get the column 1 values to go
to txtbox_1 and the column 2 values to go to txtbox_2

=[cboCategoryView] & ", " & [cboCategorySection] & ", " &
[cboSectionID] & ", " & [cboProduct] & ", " & [cboProductID] & " X " &
[cboProductAttribute]& ", " & [cboStatus] & ", " & [cboMarkup]
-------------------------------------------------------------------------------------------------------------
I'm sure I've left something out ..........
Like queries, lack of primary keys and so on.

Thanks
dekker

 

[Michael R Mattys]

"n" levels of Atrributes for Product Coding convention, so .....

I'm new to Access and not sure if Access is the tool, given the
relational nature. Anyway, here's what I need to do.

Classify our products, all 18,544 of them. They fall in 11 major
categories, up to 34 subcategories for each major category, each
subcategory can have up to 25 columns with up to 90+ fields per
column, and so forth, for 8 distinct fields to describe a single
product and derive an associated unique identifying character string
(part number).

The challenge is to use comboboxes to classify each item. New items
are added frequently, as well as some are dropped, resulting in about
500 new items per year.

The need arises from inconsistency in naming/description of each item.
Over time it gets ugly. No two people do it the same way.

Right now it looks like just over 150 tables with a minimum of 6
columns and up to 63 columns with a maximum of 90+ fields, except one
table which has 3 columns with 301 fields.

The column heading describes the applicable attributes, while table
name describles the hierarchial placement, hence cascading
synchronized comboboxes.

Each combobox will have 2 columns, the 1st column will contain a
character and the 2nd column will contain a word or phrase. I need to
somehow relate/identify these 2 columns to the selection made from the
preceeding combobox selection which may or may not be in the same
table. Hyperlinking is the general idea. The selected field sends me
to the the correct table and the appropriate columns.

The form(s) for assigning this product classification consist of:
-------------------------------------------------------------------------- ----------------------

txtItemNo from tblItem - unique number generally referenced
txtItemDescription from tblItem - noun description not unique

The 2 generated text boxes will have the follwing information :
1.) txtbox_1 is a full description field with the concatenated
words/characters from at least 6 of the 8 fields using the 8
sequential comboboxes. 2nd column in combobox
2.) txtbox_2 is for 8 characters concatenated from all 8 fields using
the 8 sequential comboboxes. 1st column in combobox

-------------------------------------------------------------------------- -----------------------------------

While I'm asking for the moon, how do I get the column 1 values to go
to txtbox_1 and the column 2 values to go to txtbox_2

=[cboCategoryView] & ", " & [cboCategorySection] & ", " &
[cboSectionID] & ", " & [cboProduct] & ", " & [cboProductID] & " X " &
[cboProductAttribute]& ", " & [cboStatus] & ", " & [cboMarkup]
-------------------------------------------------------------------------- -----------------------------------
I'm sure I've left something out ..........
Like queries, lack of primary keys and so on.

Thanks
dekker

Dekker,

From what you describe, you have a bunch of
Excel tables that you now want to put into an
Access database.

To start off, you need the following:

tblMajorProductCategories -
'To hold 11 major categories
MajCatID - AutoNumber, Primary Key
MajCatName - Text (A major Product name)
AttribID - Number (Foreign Key *) LookUp:CBO

tblMinorProductCategories -
'To hold 34 minor categories
MinCatID - AutoNumber, Primary Key
MajCatID - Number (Foreign Key *)
MinCatName - Text (A minor Product name)
AttribID - Number (Foreign Key *) LookUp:CBO

* The foreign key would identify the minor product
as belonging to the major product category when
you try to filter in a query or combobox.

tblProductAttributes
'Holds all Product attributes **
AttribID - AutoNumber, Primary Key
AttribName - Text

** You may need to split this table up into several tables
that match the column headers. For example, your products
may come in a variety of colors so you'd need tblColors.

All of this allows you to add or delete products.
I have left out details, but it will start you on the tables.

It will also enable the cascading combos, though you
must still go further by writing code for the AfterUpdate
event of each combobox. These combos, will need to be
placed side-by-side on a continuous MinorProductCategory
form that is a subform of the MajorProductCategory form.

When you're done, you'll be able to filter by Major Product,
then Minor Product, then Attribute.

 

[Tom Ellison]

Dear Dekker:

In a way, this does clarify your situation considerably.

A database solution requires data be put into tables in a normalized
fashion. Normalization is a set of rules around which the operations
a database are designed to work. If your data is not normalized when
the tables are designed, then it will typically be far more difficult
than necessary to draw upon that information when the time comes.
That is to say that you will seem to progress rapidly and easily
through about the first half of a project, then you will run into
critical problems that become increasingly difficult to solve.

In simple terms, the database solution, properly done, does not
usually look a lot like a spreadsheet solution. It requires skills
not typically used in spreadsheet applications to do this properly.

When I read your initial post, I suspected you might be thinking
"spreadsheet," and it looks like you have confirmed this. Given the
complexity of the application you propose to create, the level of
later difficulties you will likely face may be quite extreme.

You should either learn database design, possibly at a level
equivalent to 6-10 university credit hours (2 semesters hard study at
a night course) or you should get some help with your project from
someone who is experienced with this.

It is not at all uncommon to have questions in this newsgroup from
those who have gotten far into a project, but have not normalized the
data. The amount of wasted effort in this is appalling. It is better
to learn the craft without making mistakes that are lessons easily
learned without doing so from expensive errors.

This is not to say that studying the spreadsheets you apparently
already have would not be useful. But mimicking them in a database is
likely to be very expensive in the long run.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts