Roy,

I've read Steve's comments and I must disagree with some of his points.

While I agree that you need a “a well designed set of normalized tables to
hold your data”. However, before you can design your tables, you have to
define all of your fields and relationships. If you have not defined all of
your outputs, you can not possibly know all of your inputs to the system!

This is why you always start with the outputs. If you have a report that
has a field that you never defined on the input side, then don’t have a “well
designed set of normalized tables”. Therefore, you must define all of your
fields and relationships BEFORE you design your tables.

Also, as I stated before define ALL of your goals, even if they seem far
fetched. I’ve had projects where the customer has asked for some pretty wild
stuff, but as the design unfolds they have turned out to be pretty easy to
accomplish. On the other had, I’ve had request that were very difficult to
accomplish and were not worth the development cost. However, if you don’t
put EVERYTHING down, you don’t know what you can and can not do.

I do agree that you need a list of Access Reserved Words to avoid. Here is
a link to MS’s Office 2007 Access Reserved words:

http://office.microsoft.com/en-us/ac...306431033.aspx

Steve mentions to avoiding using spaces or underlines in you table names.
However, he should have also told you not to use spaces or dashes in the data
field names.

As far as the primary key always being an auto number field, I think that is
personal choice. Where there is a “natural key” (such as part State
abbreviation) I use it. If there is not, I’ll use an auto number field for
the key. Also, if you create a meaning key like LED instead of 24 (auto key)
when you are testing your system it is easier to determine if data is in the
right place if you have meaning code names instead of numbers. But, like I
said I use both “natural” and auto numbers fields for my keys.

One other suggestion I have instead of having a whole bunch of lookup
description tables for:

Receptacle type (box, drawer, plastic tub, chest, etc.)
Room
Position
Genre Description

I like to have two tables. The First table, tblDescType, describes the
second table, tblDesc.

The first table is called tblDescType and has the following structu

Key: DescTypeId Text field and is code name for the Type of Description.
(ie. RC for receptacle type, R for Room, P for position)

Data: CodeDesc A text field which contains the description for
description type – Receptacle Type, Room
ShortDesc
CodePrompt – Text field which contains the text for the Desc
form’s Code Name label field.
DescPrompt - Text field which contains the text for the code’s
descriptions label prompt.
ShortDesc – Text field which contains the text for the code’s
short description label prompt.
Parm1Promp – Text field which contains the text for an additional
parameter prompt.
Parm2Promp – Text field which contains the text for an additional
parameter prompt.
Parm3Promp – Text field which contains the text for an additional
parameter prompt.
Parm4Promp – Text field which contains the text for an additional
parameter prompt.

I realized that I could normalize this table, but this was a quick and dirty
solution for having multiple description tables. At this time I have over 50
different types of descriptions and only two tables. It takes about 1 minute
to setup a new description type.

The second table is called tblDesc and contains the actually description
records.

Key: DescID This is a “manual” two part key. The first part is the
DescTypeId and the second part is the code for the description record. I
call it a manual two part key because the data entry form has to concatenate
the DescTypeId field with a dash and then with the Desc code. So they key
to a drawer might be R-D or R-C (chest). I create the manual key here once
and all of the other tables that reference this table store the entire DescId
(R-D).

Data DescTypeId – FK to the tblDescType table. Yes, this is duplicate
data, but I did not know a SQL way to break out the DescTypeId when I used
the field in queries. So I duplicated the data to make life simple.
DescCd – This is the second part of the key. It is here for the
same reason the first part is above.
Desc - The description text for the code.
ShortDesc – Short (15 chars or less) description for the code.
Parm1
Parm2
Parm3
Parm4 These fields contain whatever additional information you
want for each description. Yes, each fields having a different meaning is a
violation of database rules, however these fields are not “data” fields, they
are more parameter fields for you to use to allows certain transaction type
to be selected for a report, or to sort your drop down list of descriptions
in a certain sequence other than numerical or alphabetical.

I have one form that allows me to enter the parameters for a given
Description Type and another form that the record for a Description Type and
fills in the appropriate field labels for that Desc. Type and allows the user
to enter the code’s description record.

If you have any more questions, please post them.

Good luck

Dennis