Re: Concatenate and Null Values -- Features

Hi Hoopster,

And to throw another curve ball at you ... had to give you a chance to digest that last post... I talked about a table called Grommets – just so that you could visualize the item.

.... but, and here is the curve, Grommets are just one type of feature an item may have. If you do no more than keep track of measurements, you could use a generic ID field (I will just call it FtrID for lack of a better name -- I do like to keep IDs short) to describe it ...

*Features*
FtrID, autonumber
Feature, text

Then, you could have a table to cross-reference what (FeatrID) and how many (multiple records) particular features an item has.

*ItemFeatures*
ItFtrID, autonumber
ItemID, long integer (FK to Items table)
FtrID, long integer (FK to Features table)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



strive4peace wrote:
Hi Hoopster,

I must concur with Duane on this, "I wouldn't put too much effort into this without considering a complete re-design."

What you are facing now would be easy if your data were normalized ... and it wouldn't take that much to do it ... at least not compared to the extra time and problem you are creating for yourself by continuing with your current design.

Data should *flow*, easily ... simply ...

In order to do that, you must think more generically and less specifically.

You have repeating groups in your structure such as:

Header Stitch #1
Header Stitch #2
Header Stitch #3
Header Stitch #4
Header Stitch #5

Does every item have 5 Header Stitch values? Do some have none? Do other descriptors get filled out if Header Stitch is filled out? What is a Header Stitch? What does it describe?

... same questions for Fly Hem (which, I am presuming would only apply if there was a Low Fly or High Fly -- just guessing), Side Hem Stitch, Grommet (and I see you may have up to 10 of those ... what if you have 11? what if you have none?), Label Type (I imagine this would be especially important to be able to summarize on – hard to do if the data is scattered in up to 5 different fields), LabelPlace (here, you named well and did not use a space), Tail, Fold Width, Fold Length, ...

The point is, you have several places where you may have no data, or varying amounts of data. Whenever you have fields containing numbers in their name, you must ask yourself ... am I normalized?

The structure of your data is the foundation of all you will build in the future. You must make it strong.

Take a breath ... visualize ... think ... see the physical objects you need to describe.

Instead of designing according to what you want out ...think about what you have to put IN and structure it well.

Drawing an analogy to a building:

Data structure is the foundation.

Forms are walls.

Reports are paint.

Organize your data so the structures are strong. You want what you build on that foundation to be stable and flexible.

Structuring data is an iterative process. Set up tables, print relationship diagram, change tables, print relationship diagram, put data in, change tables, print relationship diagram, …

The best solution is simple... but it is the hardest to find.

It takes thought and foresight to design your structures well. And the simplest solution is rarely easy to see. Get the structure right before building anything else. If you have already built forms, queries, or other objects, fix the structure before building any more. Tear down and re-build whatever was not built on solid ground.

Just as you wouldn't build a house on sand without a foundation, your data structure is the foundation you will build on for Access.

It takes time to plan your database. Here are some ideas for you:

Think of the different "nouns" that you will track:

-Vendors
-Departments
-Items

... Items -- now that is a whole ball field in itself. Items can have so many different types of things ... you need to describe them better ... generalize a bit ... categorize -- and, most definitely, create related tables to hold repeating data.

-Grommets
(if you have different types of these, a cross-reference table between the Items table and the Grommets table would work, otherwise, if you do not care to describe the Grommet, you could simply have a table called ItemGrommets – or something like that – that contains each measurement. When you describe an item, you could enter information into a Grommets continuous subform ... if Grommets applied ... (this would use a main form/subform – ask if you want to know more)

-Etc...

For each "noun", you describe it with "adjectives", or fields. Each table should have a primary key. It is common to use the autonumber field and "ID", such as DeptID, ItemID, ItemGrommetID, etc.

By convention, when "ID" is used in a fieldname, it indicates that field was originally created by an autonumber and it is a LONG INTEGER data type.

Do not use "ID", alone, as a fieldname because it is ambiguous and not descriptive. Instead, qualify it with what type of ID it is ... NounID (where 'Noun' represents some physical entity that you can generally picture).

Don't repeat data structures

If you see that you have, for instance, address information in more than one table, create an Addresses table and use an autonumber AddrID to relate the data.

~~~

With all due respect, you have broken many rules of normalization – chances are, you just don't know any better – but, luckily, you have come to the right place for help.

Also, on naming -- you have not only used spaces in your fieldnames (that will bite you), you have also used special characters that have other purposes such as #, (, ) ... at least you haven't started any fieldname with a number.

... believe me, it is sooo much better to correct these things now than continue with a poor design.

Access is the hardest application in the Office suite for good reason, it is the most difficult to learn. That is because the most important thing to get right is the first step – the data structure. You can't really learn as you go unless you are willing to tear down and rebuild...



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Duane Hookom wrote:
Sorry, I had to go get a drink...
The last inspection application I wrote contained about 3-4 fields per table. Strings like Header Stitch, Grommet, Tail,... are all data values and shouldn't be used as field names. An inspection header record might describe who performed the inspection on which date and what item they inspected. Each attribute that is checked should create a record in a separate/related table that stores the attribute evaluated, the score, and possibly a comment.

You could possibly normalize this somewhat with a union query but to be honest, I wouldn't put too much effort into this without considering a complete re-design.

.