Datasheet

 Access saves time by giving you great tools for importing data from
other sources, such as Excel worksheets (if you started in Excel and
have maxed out its usefulness as a data storage device) and Word
tables. This saves you from re-entering all your data and allows you to
keep multiple data sources consistent.
Creating databases with multiple tables
Whether your database holds 100 records or 1,000 records (or more), if you
need to keep separate tables and relate them for maximum use of the infor-
mation, you need a
relational database — and that’s Access. How do you
know whether your data needs to be in separate tables? Think about your
data — is it very compartmentalized? Does it go off on tangents? Consider
the following example and apply the concepts to your data and see if you
need multiple tables for your database.
The Big Customer database
A large contracting business has a database of customers — past, present,
and potential clients — and wants to keep track of a lot of information on
them. For the current and past clients, the bigwigs want to store information
about the work that was done, what materials were used — paint colors, tile
designs, carpet styles, preferred fixtures, and so on. For potential customers,
they want to keep track of when and how they’ve contacted them with mail-
ings, phone calls, and visits from sales reps. Imagine keeping all of that in a
single table — with everything from the customer’s name to what wallpaper
was used in the bedroom.
For a complex database like this one, you’d need multiple tables, as follows:
 One table would house the customer contact information — names,
addresses, phone numbers, fax numbers, and e-mail addresses. A field
one might also include would be customer number, which makes each
record unique, and in that number, one or more of the characters could
be used to differentiate between different customer types — past, cur-
rent, or potential.
 A second table would contain the customer number again (as a way
to link or connect the two tables) and also the customer’s status
information — what work was done (kitchen, bathroom, painting,
restoration, any number of established classifications) and what was
charged for the work.
 A third table, again containing the customer number, would include the
customer’s preferences for paint manufacturers and colors, wallpaper,
tile, countertops, fixtures, carpet, and so on. Because you don’t have to
fill in every field in a record, if no carpeting was done for a particular
customer, for example, that field can be left blank.
11
Chapter 1: Getting to Know Access 2007
05_046120 ch01.qxp 11/15/06 1:57 PM Page 11