Microsoft Access Spare Parts Template

Document Sample
Microsoft Access Spare Parts Template Powered By Docstoc
					     [Your company name] presents:


                     ®
    Microsoft Office
        ®
  Access 2007 Training

Build a database I: Design tables
  for a new Access database
Course contents
• Overview: Tables, the backbone

• Lesson: Start with a plan




 The lesson includes a list of suggested tasks and a set of
 test questions.


                    Build a database I: Design tables
                      for a new Access database
Overview: Tables, the backbone
        Say you’ve been using Microsoft Office
        Excel® to manage your company’s
        assets. It used to work well, but now
        your spreadsheet is large and
        unmanageable.

        Access can make that information
        easier to manage. But where do you
        start? By planning tables, the
        backbone of any database.

        This course provides a comfortable
        entry into the design process and into
        Access 2007.
                Build a database I: Design tables
                  for a new Access database
Course goals
• Plan the table structure of a new database.

• Plan the fields — the individual columns in each table.

• Plan the primary key fields that enable the relationships
  among your tables.




                     Build a database I: Design tables
                       for a new Access database
   Lesson

Start with a plan
Start with a plan
                                                               Before you start
                                                               planning, remember a
                                                               key fact:

                                                               Databases organize
                                                               your information into
                                                               separate tables, and
                                                               each table contains
                                                               unique data.

The database then uses relationships to join the data
in the tables in a meaningful way.

That set of tables and relationships is called a
relational structure, which the picture shows.




                           Build a database I: Design tables
                             for a new Access database
Decide on a purpose
                                                               The first step in
                                                               planning a new
                                                               database is to write
                                                               down its purpose. In
                                                               this case, you need to
                                                               enter and manage
                                                               your company’s asset
                                                               data.


But don’t stop there. Ask yourself who will use the
database and how they’ll use it.

For example, the technician who fixes a defective
machine has different information needs than the
accountant who tracks costs. Make sure your purpose
statement addresses those different needs and uses.

                           Build a database I: Design tables
                             for a new Access database
List the data you want to store
                                                               A good database
                                                               design helps ensure
                                                               your data is complete,
                                                               and most importantly,
                                                               that it’s accurate.




To reach those goals, start by listing the data you want
to capture. You can start with your existing data — in this
case, your spreadsheet.

Or, if you use paper ledgers or forms, gather examples
of those. And don’t hesitate to ask your coworkers what
they need.

                           Build a database I: Design tables
                             for a new Access database
List the data you want to store
                                                                Another way to
                                                                identify the information
                                                                you need to store is to
                                                                create a flowchart of
                                                                the tasks associated
                                                                with your data.




And while you’re at it, think about the reports or mailings
you want to produce from the database.

Looking at the data you need to enter and consume can
help you decide which data to store.




                            Build a database I: Design tables
                              for a new Access database
Group your data by subject
                                                               As you list the data
                                                               you want to capture,
                                                               you’ll see it naturally
                                                               falls into one or more
                                                               subject matter
                                                               categories or groups.




For example, your information may group itself like this:

• Asset data, such as models, purchase dates, and
  costs.
• Support data — who you call for help or spare parts.
  This category will probably include company names,
  addresses, phone numbers, and contact names.

                           Build a database I: Design tables
                             for a new Access database
Group your data by subject
                                                               As you list the data
                                                               you want to capture,
                                                               you’ll see it naturally
                                                               falls into one or more
                                                               subject matter
                                                               categories or groups.




For example, your information may group itself like this:

• Supplier data, which looks a lot like support data
  because it includes companies and contact names.

Grouping is important because each category can correspond
to a table, such as Assets, Support, Suppliers.

                           Build a database I: Design tables
                             for a new Access database
Group your data by subject
                                                             Your groups may not
                                                             result in a complete
                                                             list of tables, but
                                                             they’re a good starting
                                                             point.




Just make sure each group contains unique data — just
the asset information in one group, only the supplier
data in another, and so on.




                         Build a database I: Design tables
                           for a new Access database
From groups, fields
                                                               The next step in your
                                                               design is to list the
                                                               fields for each table.




In an Access table, columns are called fields and
individual records are called rows.

You plan your fields by deciding the specific information
each of your groups should capture.




                           Build a database I: Design tables
                             for a new Access database
From groups, fields
                                                                 For your asset
                                                                 database, you’ll
                                                                 probably want to list
                                                                 each item and
                                                                 information about
                                                                 each item, such as
                                                                 purchase dates and
                                                                 costs.


As part of this, try to reduce each field to its smallest
logical component.

In a good design, a field represents a single piece of
data, and the name of the field clearly identifies that
data.


                             Build a database I: Design tables
                               for a new Access database
From groups, fields
                                                                As you work, you may
                                                                find yourself wanting
                                                                to use data from one
                                                                table in another.




For example, the picture shows that the Assets group
includes fields for suppliers and support.

That’s natural — you’re seeing how you need to relate
your tables, and we’ll discuss those relationships in just
a bit. For now, include all the fields you think each table
should have.

                            Build a database I: Design tables
                              for a new Access database
From groups, fields
                                                               As you work, you may
                                                               find yourself wanting
                                                               to use data from one
                                                               table in another.




Finally, in case you’re wondering, you don’t plan rows.
Those come naturally as you enter data in your fields.




                           Build a database I: Design tables
                             for a new Access database
Plan your primary keys
                                                                A final step in your
                                                                plan is to add a
                                                                primary key field to
                                                                each of your tables.




A primary key is a field, or a combination of fields, with a
value that makes each row in a table unique.

Primary keys are another way to avoid duplicating your
data, because you can never duplicate a value in a
primary key field.


                            Build a database I: Design tables
                              for a new Access database
Plan your primary keys
                                                              Access provides
                                                              several ways to create
                                                              primary keys.




You can use existing values such as part numbers, but
only if each value will always be unique. Once you
define a field as a primary key, Access never lets you
enter duplicate values in that field.

Also, your key values should never change, because if
they do your tables may become out of synch.

                          Build a database I: Design tables
                            for a new Access database
Plan your primary keys
                                                             Access provides
                                                             several ways to create
                                                             primary keys.




If your existing data won’t work as a primary key, you
can use an AutoNumber field. Access simply increments
the value in that field by one whenever you add a new
record.




                         Build a database I: Design tables
                           for a new Access database
Plan your primary keys
                                                               So how do you plan
                                                               primary keys?




Start by adding an “ID” field, such as “Asset_ID”, to each
of your tables.

Or, if you want to use multiple columns of data to create
a composite key, make a note next to each column you
want to use.


                           Build a database I: Design tables
                             for a new Access database
A quick look at relationships
                                                               So, you’ve divided
                                                               your data into tables
                                                               and listed the fields
                                                               and primary keys.
                                                               How do you relate
                                                               your data back
                                                               together?




You create relationships by using the primary key field
from one table as a field in another table.

The picture shows this. You can see how the primary
keys in the Suppliers and Support tables have become
fields in the Assets table. Those duplicate fields in the
Assets table are called foreign keys.

                           Build a database I: Design tables
                             for a new Access database
A quick look at relationships
                                                                At this point, you may
                                                                be thinking, “Hang on,
                                                                I’ve just duplicated
                                                                some data!”




Don’t worry, this kind of duplication is okay. Primary key
values are small, and you can’t extract meaningful
information from your database unless you use them in
relationships.

So, as a final step in your design, indicate your foreign
key fields.

                            Build a database I: Design tables
                              for a new Access database
Suggestions for practice
1. Start your plan, thinking about the purpose of your database and its uses.

2. Explore a sample database template.

3. Explore ways to avoid redundant data without creating tables.




Online practice (requires Access 2007)




                        Build a database I: Design tables
                          for a new Access database
Test question 1
What is the function of a primary key? (Pick one answer.)




1. To uniquely identify each record in a table.

2. To encrypt and decrypt your database.

3. To help ensure you enter data in the correct table.




                         Build a database I: Design tables
                           for a new Access database
Test question 1: Answer
To uniquely identify each record in a table.



All your tables must have a primary key field.




                        Build a database I: Design tables
                          for a new Access database
Test question 2
A good database design helps ensure that your data is what?
(Pick one answer.)



1. Always backed up.

2. Complete and accurate.

3. Duplicated so it’s easier to find.




                         Build a database I: Design tables
                           for a new Access database
Test question 2: Answer
Complete and accurate.



Completeness and accuracy are essential for making sound decisions.




                         Build a database I: Design tables
                           for a new Access database
Test question 3
How many tables should a well-designed database contain?
(Pick one answer.)



1. As many as necessary to capture all your data without
   redundancy.

2. One, with many fields.

3. Two.




                       Build a database I: Design tables
                         for a new Access database
Test question 3: Answer
As many as necessary to capture all your data without redundancy.



That can be one table, or dozens.




                       Build a database I: Design tables
                         for a new Access database
Quick Reference Card
For a summary of the tasks covered in this course, view the
Quick Reference Card.




                   Build a database I: Design tables
                     for a new Access database

				
DOCUMENT INFO
Description: Microsoft Access Spare Parts Template document sample