Medium 9781449382377

Access 2010: The Missing Manual

Views: 2006
Ratings: (0)

Unlock the secrets of this powerful database program and discover how to use your data in creative ways. With this book’s easy step-by-step process, you’ll quickly learn how to build and maintain a complete Access database, using Access 2013’s new, simpler user interface and templates. You also get practices and tips from the pros for good database design—ideal whether you’re using Access for school, business, or at home.

The important stuff you need to know:

  • Build a database with ease. Store information to track numbers, products, documents, and more.
  • Customize the interface. Build your own forms to make data entry a snap.
  • Find what you need fast. Search, sort, and summarize huge amounts of information.
  • Put your data to use. Turn raw info into printed reports with attractive formatting.
  • Share your data. Collaborate online with SharePoint and the Access web database.
  • Dive into Access programming. Get tricks and techniques to automate common tasks.
  • Create rich data connections. Build dynamic links with SQL Server, SharePoint, and other systems.

List price: $27.99

Your Price: $22.39

You Save: 20%

 

24 Slices

Format Buy Remix

1. Creating Your First Database

ePub

Although Microsoft wont admit it, Access can be intimidatingintimidating enough to trigger a cold sweat in the most confident office worker. Even though Microsoft has spent millions of dollars making Access easier to use, most people still see it as the most complicated Office program on the block. Theyre probably right.

Access seems more daunting than any other Office program because of the way that databases work. Quite simply, databases need strict rules. Other programs arent as obsessive. For example, you can fire up Word, and start typing a letter straightaway. Or you can start Excel, and launch right into a financial report. But Access isnt nearly as freewheeling. Before you can enter a stitch of information into an Access database, you need to create that databases structure. And even after youve defined that structure, youll probably want to spend more time creating other useful tools, like handy search routines and friendly forms that you can use to simplify data lookup and data entry. All of this setup takes effort and a good understanding of how databases work.

 

2. Building Smarter Tables

ePub

In the previous chapter, you learned how to dish out databases and pop tables into them without breaking a sweat. However, theres bad news. The tables youve been creating so far arent up to snuff.

Most significantly, you havent explicitly told Access what type of information you intend to store in each field of your table. A database treats text, numbers, dates, and other types of information differently. If you store numeric information in a field that expects text, then you cant do calculations later on (like find the average value of your bobblehead dolls), and you cant catch mistakes (like a bobblehead with a price value of fourscore and twenty).

To prevent problems like these, you need to define the data type of each field in your table. This is the central task youll tackle in this chapter. Once youve mastered data types, youre ready to consider some of the finer points of database design.

All data is not created equal. Consider the Dolls table you created in Chapter1 (Creating a Simple Table). Its fields actually contain several different types of information:

 

3. Mastering the Datasheet: Sorting, Searching, Filtering, and More

ePub

In Chapter1, you took your first look at the datasheeta straightforward way to browse and edit the contents of a table. As youve learned since then, the datasheet isnt the best place to build a table. (Design view is a better choice for database control freaks.) However, the datasheet is a great tool for reviewing the records in your table, making edits, and inserting new data.

Based on your experience creating the Dolls table (Creating a Simple Table), you probably feel pretty confident breezing around the datasheet. However, most tables are considerably larger than the examples youve seen so far. After all, if you need to keep track of only a dozen bobbleheads, then you really dont need a databaseyoull be just as happy jotting the list down in any old spreadsheet, word processor document, or scrap of unused Kleenex.

On the other hand, if you plan to build a small bobblehead empire (suitable for touring in international exhibitions), you need to fill your table with hundreds or thousands of records. In this situation, its not as easy to scroll through the mass of data to find what you need. All of a sudden, the datasheet seems more than a little overwhelming.

 

4. Blocking Bad Data

ePub

Even the best database designer has spent a sleepless night worrying about the errors that could be lurking in a database. Bad data is a notorious problemit enters the database, lies dormant for months, and appears only when you discover youve mailed an invoice to customer Blank Blank or sold a bag of peanuts for $4.99.

The best way to prevent these types of problems is to stop bad data from making it into your database in the first place. In other words, you need to set up validation rules that reject suspicious values as soon as someone types them in. Once bad data has entered your database, its harder to spot than a blueberry in a swimming pool.

This chapter covers the essential set of Access data validation tools:

Duplicates, required fields, and default values are the basics of data integrity.

Input masks format ordinary text into patterns, like postal codes and phone numbers.

Validation rules lay down strict laws for unruly fields.

 

5. Linking Tables with Relationships

ePub

The tables youve seen so far lead lonely, independent lives. You dont find this isolation with real-world databases. Real databases have their tables linked together in a web of relationships.

Suppose you set out to build a database that can manage the sales of your custom beadwork shop. The first ingredient is simple enougha Products table that lists your merchandisebut before long youll need to pull together a lot more information. The wares in your Products table are sold in your Orders table. The goods in your Orders table are mailed out and recorded in a Shipments table. The people in your Customers table are billed in your Invoices table. All these tablesProducts, Orders, Shipments, Customers, and Invoiceshave bits of related information. As a result, if you want to find out the answer to a common question (like, How much does Jane Malone owe? or How many beaded wigs did we sell last week?), youll need to consult several tables.

 

6. Queries That Select Records

ePub

In a typical database, with thousands or millions of records, you may find it quite a chore finding the information you need. In Chapter3, you learned how to go on the hunt using the tools of the datasheet, including filtering, searching, and sorting. At first glance, these tools seem like the perfect solution for digging up bits of hard-to-find information. However, theres a problem: The datasheet features are temporary.

To understand the problem, imagine youre creating an Access database for a mail-order food company named Boutique Fudge. Using datasheet filtering, sorting, and column hiding, you can pare down the Orders table so it shows only the most expensive orders placed in the past month. (This information is perfect for targeting big spenders or crafting a hot marketing campaign.) Next, you can apply a different set of settings to find out which customers order more than five pounds of fudge every Sunday. (You could use this information for more detailed market research, or just pass it along to the Department of Health.) But every time you apply new datasheet settings, you lose your previous settings. If you want to jump back and forth from one view to another, then you need to painstakingly reapply all your settings. If youve spent some time crafting the perfect view of your data, this process adds up to a lot of unnecessary extra work.

 

7. Essential Query Tricks

ePub

Every Access expert stocks his or her database with a few (or a few dozen) useful queries that simplify day-to-day tasks. In the previous chapter, you learned how to create queries that chew through avalanches of information and present exactly what you need to see. But as Access masters know, theres much more power lurking just beneath the surface of the query design window.

In this chapter, youll delve into some query magic thats sure to impress your boss, coworkers, and romantic partners. Youll learn how to carry out calculations in a query and perform summaries that boil columns of numbers down to neat totals. Youll also learn how to write super-intelligent filter expressions and how to create dynamic queries that ask for information every time you run them. These techniques are indispensable to the repertoire of any true query fanatic.

When you started designing tables, you learned that its a database crime to add information thats based on the data in another field or in another table. An example of this mistake is creating a Products table that has both a Price and a PriceWithTax field. The fact that the PriceWithTax field is calculated based on the Price field is a problem. Storing both is a redundant waste of space. Even worse, if the tax rate changes, then youre left with a lot of records to update and the potential for inconsistent information (like a with-tax price thats lower than a no-tax price). And dont even ask what happens if you need to add a separate TaxRate table that lists different tax rates for different locations.

 

8. Queries That Update Records

ePub

Queries are most famously known for their ability to show small subsets of huge amounts of information. This type of query is called a select query, and its the variety you learned about in the previous two chapters.

Many Access fans dont realize that queries have another identity. Not only can you use them to search for information, but you can also use them to change data. Queries that take this more drastic stepwhether its deleting, updating, or adding recordsare known collectively as action queries.

Action queries arent quite as useful as select queries, because they tend to be less flexible. You create an ideal query once and reuse it over and over. Select queries fit the bill, because youll often want to review the same sort of information (last weeks orders, top-selling products, class sizes, and so on). But action queries are trickier, because they make permanent changes.

In most cases, a change is a one-time-only affair, so you dont have any reason to hang onto an action query that just applies the same change again. And even if you do need to modify some details regularly (like product prices or warehouse stocking levels), the actual values you set arent the same each time. As a result, you cant create an action query that can apply your change in an automated fashion.

 

9. Analyzing Data with Crosstab Queries and Pivot Tables

ePub

Access is ready and willing to store all the details in your database. But sometimes you dont need to know everythinginstead you just want the big picture. You need a way to take your raw data, which may include hundreds or thousands of records, and summarize it in some meaningful way.

Youve already learned one way to analyze large volumes of information: with a totals query (Summarizing Data). Using a totals query, you can take a huge swatch of rows and reduce it to a few neatly grouped subtotals. In this chapter, youll learn about two more specialized options for crunching the numbers: crosstab queries and pivot tables.

Crosstab queries and pivot tables play the same role as the totals queries that youve already mastered. However, they present the data in a slightly different way. Crosstab queries use extra columns to pack summary information into an extremely tight table. Pivot tables use a drag-and-drop interface that lets you rearrange your summary on the fly to search for different trends and relationships. Both these features get plenty of usage in the toolkit of every Access expert.

 

10. Creating Reports

ePub

There are many reasons to create a hard copy of your lovingly maintained Access data. With a good printout, you can:

Carry your information without lugging your computer around. For example, you can take an inventory list while you go shopping.

Show your information to non-Access users. For example, you can hand out product catalogs, order forms, and class lists to other people.

Review details outside the office. For example, you can search for mistakes while youre on the commuter train home.

Impress your boss. After all, its difficult to argue with 286 pages of raw data.

In Chapter3 you learned how to print the raw data thats in a table, straight from the datasheet. This technique is handy, but it provides relatively few features. You dont have the flexibility to deal with large blocks of information, you cant fine-tune the formatting of different fields, and you dont have tools like grouping and summarizing that can make the information easier to understand. As youve probably already guessed, Access provides another printing feature that fills in these gaps. Its called reports, and it lets you create a fine-tuned blueprint that tells Access exactly how it should prepare your data for the printer.

 

11. Designing Advanced Reports

ePub

In the previous chapter, you learned to create simple reportsnicely formatted printouts that arrange information inside a single table. Simple reports are a great way to create a hard copy that has more polish than a datasheet printout. As you learned in the previous chapter, simple reports give you the fine-grained formatting you need to highlight important columns and values, and they handle long text fields gracefully, without wasting space or chopping off part of the data.

Simple reports are a great Access tool, but theyre still, well, simple. Their structure is their main limitation. No matter how you format or arrange your data in a simple report, Access always presents it as a table. In the real world, you may want your printed data to take other forms. You may want to transform your data into customer invoices, class attendance lists, or mailing labels. All these reports perform the same taskthey take the data in a table, and then arrange it on the printed pagebut none of them can be satisfied with a plain-vanilla report and its simple tabular structure.

 

12. Creating Simple Forms

ePub

So far, youve learned how to create tables that house your data, queries that search it, and reports that prepare it for printing. Youve also created action queries that automate big updates. But your actual database users (whether thats you or someone else) will spend most of their time on an entirely different job: daily database upkeep.

Database upkeep includes reviewing, editing, and inserting information. Real databases go through this process continuously. In a typical day, the staff at Cacophon Studios adds new students, the customer service department at Boutique Fudge places new orders, and the Gothic Wedding planners tweak the seating arrangements. Bobbleheads are bought, addresses are changed, purchases are logged, test scores are recorded, and your data grows and evolves.

You can perform your daily upkeep using the datasheet (Chapter3), but that isnt the easiest approach. Although the datasheet packs a lot of information into a small space, its often awkward to use, and its intimidating to Access newcomers. The solution is forms: specialized database objects that make it easier for anyone to review and edit the information in a table.

 

13. Designing Advanced Forms

ePub

As you learned in the previous chapter, forms can streamline day-to-day tasks and even give your database a sharp, distinctive look. To be a master database builder, you need to be able to craft top-notch forms.

In this chapter, youll take form building to the next level with a whole new arsenal of techniques. First, youll learn how to create a form in the no-holds-barred Design view, where you can tweak and polish every square inch of your form. Then, youll take a tour of Accesss different controls, and jazz up your form with links, tabbed panels, and buttons. Youll also learn how to work with linked tables by creating special types of forms called subforms that work in harmony with other forms.

In the previous chapter, you learned how to quickly create different forms using the ribbons buttons and the Form wizard. But serious form gurus take a different approachthey build a form by hand. There are two ways you go about this task:

 

14. Building a Navigation System

ePub

Throughout the last 13 chapters, youve assembled all the pieces for a first-rate database. But without a good way to bring them all together, theyre just thata pile of unorganized pieces.

The best Access databases include some way for people to jump from one part of the database to another. The goal is to make the database more convenient and easier to use. Rather than forcing you to hunt through the navigation pane for the right object, these databases start with some sort of menu form and let you work your way from one task to another by clicking handy buttons. This sort of design is particularly great for people who arent familiar with Accesss kinks and quirks. If the navigation system is built right, these people dont need to know a lick about Accessthey can start entering data without learning anything new.

You already know most of what you need to create a first-rate navigation system. Now you need a new perspective on databasesnamely, that they can (and should) behave more like ordinary Windows programs, and less like intimidating forts of data. In this chapter, youll learn different ways to add user-friendly navigation tools to a database. Youll learn how to show related information in separate forms, make a form appear when you first start the database, and create navigation forms (that is, forms that direct people to other forms). But first, youll start by taking a closer look at the navigation pane to learn how you can control navigation without creating anything new.

 

15. Automating Tasks with Macros

ePub

The secret to a long and happy relationship with Access is learning how to make it work the way you want.

As youve already seen, true Access fanatics dont use the ordinary datasheet to enter information. Instead, they create their own customized data entry forms. Similarly, Access fans dont print their data using basic yawn-inspiring tables. Instead, they create richly formatted reports that are just right for presenting their data. And Access pros definitely dont struggle through the same tedious series of steps to accomplish a common taskinstead, they create macros that make Access do the work for them.

A macro is a miniature program that you create and store in your database. Macros can range from the exceedingly simple (like a command that shows a form) to the mind-bendingly complex (like a conditional macro that checks how much raw meat you have in stock, and automatically prints an order in triplicate if your fridge is empty).

 

16. Connecting Macros to Forms and Tables

ePub

If all you do with macros is launch them from the navigation pane, youre missing out on the real fun. Macros become far more powerful when you attach them to forms and tables and use Access events to run them automatically when something important happens. For example, you can use events to run an email-sending macro when someone clicks a button, or to trigger a data-checking macro when someone edits a field.

In this chapter, youll start by learning how you can plug your macros into forms and controls and trigger them with Access events. Its time well spent, because youll use almost the same approach to hook up Visual Basic code in Chapter17. Next, youll see how to build smarter macros that evaluate conditions and make decisions. Finally, youll connect a macro to a table, so Access runs the macro when a specific type of editing action takes place.

The slickest macros work with the forms in your database. Using this combination, you can create macros that fire automatically when something happens (like when a button is clicked or a record is changed). You can also build much more flexible macros that dont have fixed argument valuesinstead, they can read the data they need from the text boxes on a form.

 

17. Automating Tasks with Visual Basic

ePub

Macros are plenty of fun, but they can do only so much. If you cant find a readymade macro action that does the job you want, you cant use a macro. No such limit applies in the world of Visual Basic code, where you can do just about anything (if you spend enough late-night hours at the computer keyboard).

Here are some examples of tasks you can accomplish with code, but not with macros:

Modify a whole batch of records at once.

Deal intelligently with errors so Access doesnt pop up a cryptic message.

Perform complex calculations. You can calculate an order confirmation code using a secret algorithm, or transform a line of text into Pig Latin.

Interact with other programs on your computer. For example, you can copy some data out of a table and into a Word document.

Write even more sophisticated validation routines that lock out bad data.

The goal of this chapter (and the next) isnt to make you a full-time code jockey. If thats your ambition, you can continue on to read entire books about programming Access. Instead, this chapter aims to teach you just enough to get the low-hanging fruit of Access programming. In other words, youll pick up enough VB smarts to use the popular and practical code tricks discussed in the next chapter. Youll also build a great foundation for future exploration.

 

18. Writing Smarter Code

ePub

In Chapter17, you dove headfirst into the world of Visual Basic code, writing routines that could show messages, respond to events, and modify forms. Along the way, you learned a fair bit about the Visual Basic language and the object-based system that gives VB its mojo.

Theres still more to explore. In this chapter, youll learn how to use VB code to solve some of the most common problems Access experts face. Youll focus on improving the Boutique Fudge database, which youve worked with throughout this book. However, the solutions youll use are so useful that youll want to incorporate them into your own databases.

But first, before you tackle these more advanced examples, youll start by brushing up on the Visual Basic language, learning how to defang errors, and taking a closer look at objects. These topics complete the Visual Basic picture and prepare you to become a true Access programmer.

Although you now know enough to react to events and change control properties, theres still a lot to learn about the Visual Basic language itself. In the following sections, youll learn how to use variables, conditional logic, and loops to write more powerful code. Finally, youll see how to use these features to build a more complex code routine that checks for invalid credit card numbers.

 

Load more


Details

Print Book
E-Books
Slices

Format name
ePub
Encrypted
No
Sku
9781449394172
Isbn
9781449394172
File size
2 KB
Printing
Not Allowed
Copying
Not Allowed
Read aloud
No
Format name
ePub
Encrypted
No
Printing
Allowed
Copying
Allowed
Read aloud
Allowed
Sku
In metadata
Isbn
In metadata
File size
In metadata