Newbee Access DB structure help

  • Thread starter Thread starter Yoric
  • Start date Start date
Y

Yoric

Hi All,
I am doing a project to get started on Access. I am a tinkerer, and have a
lab and garage full of storage boxes, component chests, tackle boxes, plastic
tubs and such, on shelves, in cupboards and on the floor. Each receptacle, be
it a large plastic tub, or a single drawer of a 6x5 component chest has
components in it. There may be more than one, and more than one type of
component in each receptacle. Each receptacle has a location (room), position
(shelf, floor) and a unique id. A receptacle is always singular, but may be a
single carton, or a drawer within a multi-drawer chest. There may be several
chests, some with 5x6 drawers, some with 8x4 drawers etc.
Components each have attributes: Description, part #, genre, image,
datasheet, # on hand, etc. A single type of component may have 1, 2 or more
genres (OPTO, Discrete, Semiconductor). There may be the same type of
component in different receptacles (Part of the reason for this project is to
gather them and consolidate them!)
Thus far, I have created tables for ReceptacleType (18 possibilities),
Location(4 possible rooms), Component (20 fields incl location, receptacle
type, #on hand, but mainly just attributes etc). I also have a Receptacle
table which is the uniqueid for every single receptacle, its type, location,
and component list field.
I am struggling in several areas:
1) I am not sure how to build the relationships, such that some receptacles
have 'parents' or hierarchy and some do not. For example, a drawer within a
chest of 20, or a single large cardboard box.
2) How to construct forms that will change 'downstream' fields, dependent
upon a certain choice made in a combo box. Say if I select a chest drawer, a
field will open to describe which chest
3) How to set up forms for the most efficient data entry.

I envisage visiting each receptacle with my laptop, entering the list of
components in it (briefly), the location of the receptacle, physically
labelling the receptacle with the uniqueid sticker, then moving to the next.
Once that is done, I would go to each component, and flesh out the data for
the attributes as necessary. Later on, it would be great to report all the
'OPTO' components, so I could gather them all up and consolidate them into
grouped, or adjacent receptacles, updating the db as I did so.
An important attribute for each component would be a synonym field, so I
could query LED, led, LEDS, Light Emitting Diodes and find all of the
relevant records.

Frankly, any advice, especially on where to begin, would be most appreciated!

Thanks in anticipation,
Roy.
 
Roy,

Wow, you have some project ahead of you. Both in Access and in physical
inventory work. Access has a steep learning curve, but there are a lot of
people on this forum who are they to help you so don’t let that intimidate
you.

Questions: Where to begin?
Answer: I always like to begin at the end. That is, the first thing I
do is figure out the objective of the project which you have done pretty
well. I would recommend that you write them down. The main purpose for
putting them on paper is it makes you think about everything you want to do.
Realize that this might take a couple of days or weeks to create a complete
list.

Secondly, you should sketch out ALL of the reports and inquiry screens
(including the fields) you would like to have someday. You will use the
inquiry and reports fields as a check list to insure you have captured all of
the required fields. If you did not enter the field or you can not calculate
the field, you can not have it on a report.

Also, once you have all of you reports and inquiry screens, you can then
decide how to make them all work together. If you don’t know the final
output, it is very hard to design the front end.

Also, once you have all of you reports and inquiry screens laid out, you can
check to make sure that you have met all of your objectives. If the reports
do not meet the requirements, they you have some more work to do.

Question 1) I am not sure how to build the relationships, such that some
receptacles have 'parents' or hierarchy and some do not. For example, a
drawer within a chest of 20, or a single large cardboard box.

Answer: There are a couple of ways to do this. Here is one way.
1. Given a Receptacle table of:

Key: Receptacle No:
Data Description
Location
Position
Multiple Drawer (Y/N)

I would have a Receptacle Drawer table. This table would have a two part
key: Receptacle No/Name, and drawer number. By definition, all receptacles
have one “drawer†and that is itself. So there will always be a drawer no 1
for all receptacles. You could write you VBA code to automatically create
drawer number 1 for all receptacles that do not have multiple drawers.


Receptacle Drawer table
Key: Receptacle No
Drawer No
Drawer Type (this is the key to the Drawer Type Desc Table)
Drawer size

The top half of the Receptacle data entry form would have the receptacle
information that is stored in the Receptacle table and the bottom half of the
form would be a sub-form (a form within a form) that is in continuous view
(display one line after the other – like excel). The sub-form would have all
of the drawers assigned to this receptacle.

If there is not enough room, you could put tabs on the form. The first tab
would have the receptacle information and the second tab would have the
drawer information.

In either case, you could make the sub-form or the second tab invisible if
the receptacle had no drawers. Yet another reason for the Multiple Drawer
question.

On the parts table, you would have both a receptacle number and a drawer
number fields. If the receptacle did not have multiple drawers, you could
have your code automatically set drawer number to 1 on the inventory form.



2) How to construct forms that will change 'downstream' fields, dependent
upon a certain choice made in a combo box. Say if I select a chest drawer, a
field will open to describe which chest

Yes you can do this. There are multiple ways to accomplish this. In the
combo box, you can return multiple fields from the query / sql statement.
You can then access those fields via the statement:
Assuming a SQL stmt like
Select InventoryTable PartNo, PartName, LocNo, RecNo, DrawerNo for the
combobox, your VBA code to access that addition data would be:

strPartName = me.cboPartNo.column(1)
strLocNo = me.cboPartNo.column(2)
strRecpName = me.cboPartNo.column(3)
strDrawerNo = me.cboPartNo.column(4)

You can then use that data to do whatever you wanted to do.



3) How to set up forms for the most efficient data entry.

There are a LOT of different opinions on this. The best is keep it simple,
don’t go crazy with different colors, adopt a standard and keep to that
standard (ie. The save button is always green, the Delete button is always
Red, etc.)

However, think about how you are going to be entering the data when you are
going from receptacle to receptacle. The main thing is enter the information
is a sequent that makes sense.

One thing, on you might wants to have a Part Genres table. That way you can
have as many genres for a given part as you want.

The table would look something like:

Part Genres table:
Key: Auto assigned number by access.
Data Part No (index - duplicates ok)
Genres code (the description to this code is in another table)


I have a question. You said the same part could be in different
receptacles. If that is the case, how will you handle that? Will you have
different part numbers for the same part? Or will you use the same part
number? If you use the same part number, then you will need a master part
tables that holds the description of the part and another table – Part Loc
table – that has the different locations of the part of the quantity on hand
in each location.

You might have to have a parts table that has a main part and two sub-forms.
The first sub-form would be the Parts Genres sub-form. The second sub-form
would be the Parts Location sub-form.

So much to think about! It’s late and I need some sleep.

Dennis
 
Roy,

Dennis has given you some bad advise! Start at the beginning and not at the
end. The first thing you need is a well designed set of normalized tables to
hold your data. To avoid building in headaches, find a reference to Access
Reserved Words and don't name any table or field with a reserved word. Part
two of avoiding building in headaches is to name all your tables starting
with Tbl and don't use any spaces or underlines in the names. Part three is
to use an autonumber field for all primary keys, end the primary key field
name with ID and make all foreign key names the same as its related primary
key. If you achieve this, coding will be simple and easy to read and
creating any forms and reports you want will be easy.

Basically your data consists of components, receptacles for components and
location of each receptacle. Spend time with a pencil and paper and work out
a preliminary design of your tables. Then post back what you have for a
critique and suggestions. The typical way to post tables is:
TblComponent
ComponentID
ComponentDesc
<other fields describing attributes of a component>


Steve
(e-mail address removed)
 
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/access/HA100306431033.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 structure:

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
 
Roy,

Once again Dennis has given you some very bad advise! It's your choice
whether you want to heed it.

Steve
 
Yoric,

Basically, you need an inventory management system. Unfortunately, I have
not seen a Data Model for one but there some other here that might help with
*how to store your data*...

http://www.databasedev.co.uk/data_models.html

One thing to be careful of using Reserved Words for any field or table
names, to help with that here's a list of all those words...

http://allenbrowne.com/AppIssueBadWord.html

You didn't mention which version Access but Access 2007 has an inventory
template you could start with and modify to suit your needs.

Now, as to where to start... you didn't mention how much experience you have
with Access, so I am going to suggest the beginning...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi All,
I am doing a project to get started on Access. I am a tinkerer, and have a
lab and garage full of storage boxes, component chests, tackle boxes,
plastic
tubs and such, on shelves, in cupboards and on the floor. Each receptacle,
be
it a large plastic tub, or a single drawer of a 6x5 component chest has
components in it. There may be more than one, and more than one type of
component in each receptacle. Each receptacle has a location (room),
position
(shelf, floor) and a unique id. A receptacle is always singular, but may be
a
single carton, or a drawer within a multi-drawer chest. There may be several
chests, some with 5x6 drawers, some with 8x4 drawers etc.
Components each have attributes: Description, part #, genre, image,
datasheet, # on hand, etc. A single type of component may have 1, 2 or more
genres (OPTO, Discrete, Semiconductor). There may be the same type of
component in different receptacles (Part of the reason for this project is
to
gather them and consolidate them!)
Thus far, I have created tables for ReceptacleType (18 possibilities),
Location(4 possible rooms), Component (20 fields incl location, receptacle
type, #on hand, but mainly just attributes etc). I also have a Receptacle
table which is the uniqueid for every single receptacle, its type, location,
and component list field.
I am struggling in several areas:
1) I am not sure how to build the relationships, such that some receptacles
have 'parents' or hierarchy and some do not. For example, a drawer within a
chest of 20, or a single large cardboard box.
2) How to construct forms that will change 'downstream' fields, dependent
upon a certain choice made in a combo box. Say if I select a chest drawer, a
field will open to describe which chest
3) How to set up forms for the most efficient data entry.

I envisage visiting each receptacle with my laptop, entering the list of
components in it (briefly), the location of the receptacle, physically
labelling the receptacle with the uniqueid sticker, then moving to the next.
Once that is done, I would go to each component, and flesh out the data for
the attributes as necessary. Later on, it would be great to report all the
'OPTO' components, so I could gather them all up and consolidate them into
grouped, or adjacent receptacles, updating the db as I did so.
An important attribute for each component would be a synonym field, so I
could query LED, led, LEDS, Light Emitting Diodes and find all of the
relevant records.

Frankly, any advice, especially on where to begin, would be most
appreciated!

Thanks in anticipation,
Roy.
 
Thanks to all for such prompt and detailed advice. A lot of it is a little
over my head at this stage, but I will plow (plough) through it all and see
what I come up with.
I don't want to come between Steve and Dennis, and am certainly not
authorized to comment, however I always try to start a journey with the end
in mind....it is the only way I know that I ever get there.
Sage advice however, on labeling techniques, so thanks to both. I will
continue to define what I want out of the system, then post more details of
the tables as I develop them.

And I will visit those tutorials!
Thanks again to all,
Roy.
 
Roy,

Thanks for making my point for me. You can not start a journey unless you
have an end in mind - "it is the only way I know that I ever get there".
Enough said.

Gina has some good advice for you. You have a LOT to learn and she provided
some good links.

Don't worry about a lot of this stuff being over your head. Just keep the
advice in the back of your mind as you plow through your project . As you
get to the point where you have specific questions on how to do some, post it
on this forum and people will be there to help.


Good luck on your task. You have a lot to do.

Dennis
 
Roy,

Once again Dennis has given you some very bad advise! It's your choice
whether you want to heed it.

Steve
Click to expand...

Given your track record, I know who I'd rather put my faith in.
 
Yoric

Some folks are willing to share their ideas and perspectives. Others here
want to tell you where to go and what to do.

Choose the approach that works best with you...

By the way, if you don't know where you are going, it will be tough to, as
one responder suggested, create your "well designed set of normalized
tables"...

I also tend to start out trying to figure out what I will need at the end to
help me plan what I need to include.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top