Microsoft office access forms and reports for dummies.pdf

Click the Close button to close the dialog box. Figure Make the data align with the labels by setting the text alignment for the field. Pulling the layout together to save paper After you move your labels and otherwise clean up the mess you started with as described in the previous sections , be sure to drag the bottom of each report band up to prevent your printer from spitting out a bunch of blank paper when you print your report.

Figure shows the completed report in Design view. You may want to com- pare the completed design with the preliminary design shown in Figure to see how the report was changed. Figure The completed report design is much cleaner than the original. To preview the report, click the View button. The finished report is compact but it clearly displays the data in an understandable format. What more could you ask for?

Figure The completed report also looks good in Print Preview mode. Adding Data from External Linked Tables Creating your reports within the same Access database that holds the records you need for the report is not always possible — or even desirable. Perhaps you need to use data from an Access database that is maintained by Mr.

Regardless of the reason, you can base Access reports on tables that exist in another Access database. Choosing a method for accessing external data To use data from an external table you must first establish a connection between your current database and the database file that holds the table you want to use. There are two ways you can create this connection:. This method places a static copy of the table into your database file.

If Mr. Likewise, if you make a change to the table after you import it, Mr. Obviously, this option has some merits. On the down- side, if Mr. Of course, in the interest of maintaining peace in the office, you may not want to point out to Mr. Smith that his changes to the tables can monkey with your report.

Rather, just be careful. If you know that the external database file will always be available, you prob- ably want to link to the external table instead of importing the table.

That way you can be sure that whenever you run your report it is accurate and up- to-date. If, on the other hand, the external database file might not always be available or it might be moved and break the link and you can live with a snapshot of the data as it existed when you imported it, importing the table may be your best choice.

Only you can choose the best option for your situation. Importing or linking external data After you decide which method you want to use to connect to external data to your report, you can create that connection. The process is pretty much the same whether you decide to import the data or link to it.

To create a connection to an external table in a different Access database file so that you can use the data in a new report, follow these steps:. Open the database file where you want to create your report. Doing so displays the Import or the Link dialog box shown in Figure The two dialog boxes are essentially the same except for their title. Figure Locate and select the external database file.

Select the external database file that contains the table you want to import or link to. If you need to navigate to another server or folder in the network, you can do that, just as you would when looking for any file in Microsoft Office.

Click Link or Import to display the dialog box showing the list of tables. Figure shows a list of tables. Once again, the two dialog boxes are quite similar. Figure Choose the tables you want to import or link. Select the tables you want to use and click OK. You can select multiple tables by holding down Shift or Ctrl as you click each table. You can select all the tables by clicking the Select All button.

After you click OK, the dialog box closes and the connection is created. The arrow indicates an active link between the table and your report. Figure Two tables are linked and one was imported. After you import or link the external tables, you can use them exactly as you would any table you created within the current database. Being able to use external tables in your Access reports gives you many additional possibilities for creating reports as well as forms and queries that you may not have considered before.

Whether you import the table or create a link to it, the table acts the same way. Q ueries enable you to choose which information is included in your forms and reports. This part shows you how to create very effective queries that improve your forms and reports. You also get an introduc- tion to the language that is at the heart of all Access queries, SQL — Structured Query Language — so that you can soup up your queries for even better results.

Chapter 5. I n Part I, I get you up to speed on the basics of queries, forms, and reports. In Part II, you get to tackle some really meaty topics. In this chapter you see how to effectively use multiple tables in a query so that you can get even more useful information from your Access databases.

Multi-table queries enable you to use information from two or more tables as if you had a single table containing a combination of the data from those multiple tables. Using information from two or more tables requires that there be a relationship between the tables so that Access can determine how to combine the data. Table relationships are fundamental and extremely important for making your Access databases powerful and efficient.

This chapter shows you how to create these relationships, as well. Understanding Multi-Table Queries Queries act almost as surrogates for tables. That is, you can use a query in place of a table in nearly any place where you would ordinarily use a table. For example, you can just as easily use a query as the basis for a report as you can use a table for that purpose. But if what you need is a data source, a query can easily serve the bill.

A query must ultimately have a table as the source of its data, but one query can build on the results from another query as long as the lowest level of the chain is a table. A query typically extracts a group of information from one or more tables by using criteria you specify. Instead of show- ing every record in a report or in a form, you can choose to include a subset of the records in order to bring some focus to the report or form. You can also choose to include only the fields you want.

Simply stated, using multiple tables in your queries gives you more flexibility, just as using multiple tables in a database gives you many benefits. The following list gives you a few of the many good reasons to use multiple tables to hold your data:. For example, if you have a contact database, you can enter basic information about a company, such as the address, when you add your first contact at that company. After that, you can then simply refer to the same information when you enter additional contacts from the same company.

A good example of this efficiency is when telephone area codes change and you can update the records for everyone in the old area code in a single step. This also makes it easier for users to find the data they need. These are all great reasons to use multiple tables in a database, of course, but how do they relate to using multiple tables in a query?

A multiple- table query often provides the best way to bring that information together. You can probably think of a number of other examples of how using multiple tables in a query can help you accomplish your specific goals. Just as Access databases have thousands of functions, there are countless ways to use the information they contain.

Establishing relationships between multiple tables Storing various pieces of information in different tables is easy. When informa- tion in two tables is related through linking, you can find records in each of the tables that are somehow connected. For example, Figure shows two related tables in an auction database.

The two tables share a common piece of information that makes finding related records in the two tables much easier. If you study Figure , you notice that there are no common fields between the two tables. Figure demonstrates one of the important points about using data from multiple, linked tables — that you can combine information in useful ways very easily. When you combine the two, you can create a list of the items that were won by each of the bidders.

This is called a one-to-many relationship between the tables. Figure These two tables are related because they share a common value. Adding Multiple Tables to Queries In Chapter 2, I show you how to use both a Query Wizard and the Query Design view to create simple queries with a single table as the source of the data.

Now I give you the lowdown on building queries that include fields from more than one table. Using fields from another query works exactly the same way except that you choose a query instead of a table as the record source. Selecting multiple tables upfront The method you use to select the tables for your query depends on the option you select to begin the query. Then you select the next table that you want to use and add its fields to the query.

You can add more than one table at a time by selecting all the tables you want to use before you click the Add button see Figure Figure Select all the tables you want to add to the query and click Add.

You can use whichever method you prefer to add the tables to the query. Adding the tables in Design view means that you still need to add the fields you want to the query see Figure ; on the other hand, you may prefer doing the Design view method because you can more easily control the order in which the fields appear in the query. Table links Figure When you add the tables to the query in Design view, none of the fields are auto- matically added to the design grid.

Notice that in Figure the links between the tables in the query automati- cally appear when you add those tables to the query design. In this case, the selected tables are the same ones shown in Figure , so the tables are linked the same way as they were in the earlier figure. You can display the linked fields by scrolling the field list with the scroll bar along the right side of the list of fields, or you can drag the edges of the list to expand the list so more fields are visible.

If you add multiple tables to a query and no links appear in Design view, you have to create the links before you can run the query. One way to add the links is to drag the field name from one table to the other, making sure, of course, that you drop the field onto the field that it is supposed to link to in the second table. Each table in a query must be linked to at least one other table in the query. In fact, if you have three or more tables in a query and every table is linked to every other table directly, your database design is probably pretty messed up!

I show you a tool in Chapter 20 that may help you deal with this type of problem. If the new table that you want to add already has an established table rela- tionship with at least one of the tables in your query, you simply need to open the Show Table dialog box and add the new table.

Follow these steps:. The Show Table dialog box appears. Add the new table by selecting it and clicking Add. How can you tell whether the new table is related to one of the existing tables in the query? After you save the query design, add the new table to the query and look for a link to an existing table.

Then create the link before trying to add the table again. Choosing the fields to use You have several methods you can use to add fields from the query tables to the design grid.

They include the following:. Adding the fields to the grid is the easy part. Choosing the fields that should be included and the order in which they appear is just a touch more compli- cated. There are no hard-and-fast rules about which fields you should include or their order in the design grid, but the following guidelines may help you decide what will work best in your queries:. That is, if you intend to do any grouping, add the grouping fields first in the order that you plan to use them to group the records — with the least selective grouping such as State coming before more selective grouping such as City.

The results can be a simple datasheet view, a form, or a report. The idea here is to keep the results just a bit cleaner and easier to use. For example, you might not need to include fields like automatically generated indexes that Access uses to create unique records.

You can delete fields by clicking the column header to select the field in the query design and pressing the Delete button on your keyboard. You can use and adapt these guidelines to best suit your needs. Making Access use the correct table The second row of the query design grid shows the table that is the source for the field in that column. If all of your query designs in the past have been single table queries, you might not have given the table entry much thought.

But when you begin creating multiple-table queries, the table entry can be very important. Commonly, you may have fields with the same name in different tables. For example, you might use State as a field name in several different tables in a single database. Imagine for a moment that you have two tables in your database that use State as a field name.

The first table lists the names and addresses of your customers. The second table lists the items that were won by bidders in a recent auction. But sup- pose you accidentally used the State field from the items table and that table showed the state where the item originated instead of the state where the buyers live. And when have you known a tax collector who had a sense of humor? That is, if you have already selected a field and then change the source table by making a different selection in the Table cell, the field name remains the same even if no field by that name exists in the new table.

If you change the source table, be sure that the field exists because otherwise the query results will be meaningless. Linking to External Databases in Your Queries Chapter 4 briefly touches on the idea of using information from external data- bases in your reports. Here I expand on that idea and consider some addi- tional ways that you can use external data with your queries.

Understanding the types of external data you can use If you read Chapter 4, you already know that you can use data that exists in another Access database. Fortunately, Access can work with information from many differ- ent types of databases without a whole lot of fuss and bother. Access uses drivers — small helper applications — to import or link to external data. For example, Access has built-in drivers that provide the ability to import or link to the following types of files:.

Any changes that are made options. You can choose to import data or to link to the external data after you import the infor- to the data. If you import data, the copy of the mation, you must choose to link to the external data that you import only exists within your data instead of importing it.

Every driver provides a different level of support for features that are native to other file types. In some cases, you may not be able to use every type of data that a foreign file can contain. Even after you successfully import or link to external data, keep in mind that some types of information make no sense in an Access database. For exam- ple, if you link to an Excel worksheet that is laid out as a database but which contains formulas in the database range, those formulas mean nothing in Access.

In order to use that information, though, you need to bring it into Access. Open the Access database where you want to use the external data. The Link dialog box appears see Figure Figure Use the Link dialog box to link to external data. Select the type of file you want to use from the drop-down Files of type list box at the bottom of the dialog box.

After you select the type of file, select the file you want to link to in the file list. Click the Link button to continue. If you are prompted to select an index file, select the file and click Select. If there is no index file, click Cancel to bypass this step. A message box appears, telling you that the file was successfully linked see Figure Figure Access informs you that the link was successfully created.

Click Close to close the Link dialog box. The linked table will appear in the Tables list with an arrow in front of the table name to indicate that it is a linked table. Figure You cannot modify most of the properties in linked tables. See Chapter 6 for information on how to use a query to create a new table. Understanding Relationships in Queries In order to use multiple tables in a query, the tables have to be related.

In the following sections, I give you a closer glimpse at those relationships; you can see how to create the relationships necessary for adding extra tables to a query. Chapter 5: Creating Multi-Table Queries Getting to know how tables are related When you add related tables to a query, Access shows you that the tables are related by drawing lines between the tables in the Query editor.

You must make sure that the table relationships are established before you begin creat- ing your queries. Except in unusual circumstances, all the tables in a typical Access database are generally related in some way. The relationships may be distant and travel through several intermediate tables, but tables that are unrelated to any other tables usually signify that either the relationships have not yet been established, or that the unrelated table may not actually belong in the database.

Although you can view table relationships in the Query editor, Access pro- vides a better option for working with table relationships. The Relationships window, shown in Figure , not only displays any existing relationships, but it also enables you to create and edit relationships. In Figure , two relationships are displayed. Figure The Relation- ships window enables you to view, create, or edit table relation- ships. Access includes the symbols showing the type of relationship between the tables if you choose to enforce referential integrity for a link.

You can create relationships between fields in two different tables as long as those fields contain related information and are of the same type you can link an AutoNumber field to a Number field if the Number field has its Field Size property set to Long Integer or Replication ID.

Making sure that you choose the right relationships You need to choose the right relationships. Tables can be related to each other in several differ- ent ways:.

This is the most common type of relationship. For example, in the typical relation- ship between a customer table and an orders table, each customer is usually assigned a unique ID in the customer table.

That ID can appear in the orders table many times — once for each order placed by the customer. Usually, Access automatically creates the correct type of rela- tionship, which is usually a one-to-many relationship. In Chapter 15, you can find out how to make Access use a different type of relationship if necessary. Setting up table relationships The relationships between the tables in your database are what make your database into a relational database and therefore far more efficient and useful than a flat-file database like one you might create in an Excel worksheet.

Setting up table relationships does require some initial planning. This means that you have to include fields that are common to two or more tables and that contain information that is shared between the tables. Say you have a Customer ID field and a Catalog Number field; these are but two examples of the types of fields that are likely to occur in multiple tables — you use the same customer ID in both a customer table and in an orders table.

Likewise, you use a catalog number in both an inven- tory table and an order detail table. To add table relationships in your data- base, follow these steps:. Open the Access database in which you want to create the table relationships. Figure Open the Relation- ships window to create new table relation- ships. Click the field in the first table that you will use to link to the second table, hold down the left mouse button, and drag the pointer onto the related field in the second table.

You may need to first scroll the field display in the two tables to make the two fields visible. Release the mouse button. The Edit Relationships dialog box appears, as shown in Figure Figure Use the Edit Relation- ships dialog box to specify the parameters for the relationship. Select the Enforce Referential Integrity check box if you want Access to make sure that certain rules are applied when you make changes to the records in the tables.

Referential integrity insures that you cannot delete or otherwise change data under specific circumstances. If you chose to enforce referential integrity, choose the Cascade Update Related Fields check box if you want Access to automatically update the related tables when the primary table is modified.

For example, if you assign new customer ID numbers in the customer table, you want the orders table to reflect those new ID numbers, right? If you chose to enforce referential integrity, choose the Cascade Delete Related Fields check box if you want Access to automatically delete the associated records in the related tables when the primary table is modified by deleting a record. For example, if you remove a customer from the customer table, you should also delete any remaining records for that customer in the orders table.

Click the Join Type button. The Join Properties dialog box appears, as shown in Figure What Access calls a join is what I would call a relationship. A join simply brings records from various tables together. In most cases the first choice is the correct one because you want to match the records between the two joined tables.

The other two choices create what is called an outer join, where records from either the left or the right table are included even if the other table does not have match- ing records. Click OK to close the Join Properties dialog box. Click Create to create the link or join between the two tables.

Repeat Steps 3 through 11 for each of the table pairs where you want to create a relationship. After you finish creating the table relationships, those relationships appear in the Relationships window. You can drag the tables and resize them as neces- sary to make the links between the tables easier to visualize. This command displays an on-screen report showing the relationships, and clicking the Print button gives you a printed the report.

You might need to move the tables to fit one printed page. Y ou can do a lot more with queries than simply selecting data to show in a datasheet view, form, or report. This capability opens up whole new worlds of tasks that you can accomplish with Access because you can easily make either mass changes or very selective ones simply by creating the proper query. In this chapter, you see what you can do with a data-modifying query.

The topics include creating new tables by using a query, updating existing infor- mation by using a query, removing specific data with a query, and adding new records with a query. They give you the ability to automate a broad range of actions in your Access databases without requiring you to master some strange-looking and difficult-to-understand computer program- ming language, such as SQL Structured Query Language.

Knowing what you can modify One of the most frustrating things about using queries to modify your data- bases is simply getting a handle on just what you can do with a query. Oh sure, you already know that you can use a query to select specific records and specific fields, but what else can you do with a query? Just what kinds of modifications are possible by using queries?

However, you can use an append query to add enough records to change the next value used indirectly. Append and make-table queries are different kinds of action queries. Action queries are designed for the specific purpose of modifying data in a table.

Queries can only exist within an existing database. After all, a query is really intended to manipulate data, as opposed to modifying the basic building blocks of your database. You can easily make those types of changes yourself and use queries for what they do best. Being Called to Action: Data-Modifying Queries When you begin creating a new query, Access automatically sets the query type as a select query. In Access, queries that modify data are called action queries. If you want to create an action query you must select the query type when the query is open in Design view.

Those new records can come from a single table or they can be built up from records in two or more tables. Any records that match the specified criteria are completely deleted even if you only specify some of the existing fields in the query.

If you only want to remove certain field values from a table without deleting the complete records, use an update query instead of a delete query.

When you want to delete only some fields but not all the records , this is your action query of choice. SQL-specific queries can also modify data. I often find that I need to create a couple of different types of queries and then run those queries in the proper sequence in order to accomplish a par- ticular goal. For example, to create a table that contains the billing informa- tion for an auction, you might first create a make-table query that builds a new table containing information about the prices of the items that were sold at the auction.

Chapter 6: Modifying Data with Queries A simple mistake such as choos- you later regret having made. For that matter, you should probably make sure that all the options in the Confirm section are selected. Click OK to close the dialog box and apply the changes. Creating New Tables with Queries Okay, you may find this a little strange, but I happen to think that make- table queries are actually kind of fun to create and use. After all, can you think of another way that you can so quickly create a new table and fill it with selected data from a couple of different tables?

It almost seems magical the way a make-table query allows you to be both creative and extremely productive at the same time. Why you would want to create new tables There are often several different ways to accomplish the same goal. If you need to summarize the data in the report and perform calculations based on various subtotals, your report design could become quite intricate and difficult to troubleshoot or even to verify.

As an alternative to that option, you may want to consider using a make-table query to first bring together the basic data for your report. After you set up the new table, you can continue to use the same complex report design or you can use another query to perform the calculations, and then base your report on the already summarized data. In addition, having the data in a new table makes exporting that data to another application such as Excel, Word, or even QuickBooks a snap.

Creating your first table with a make-table query A make-table query enables you to create a new table by using records from one or more existing tables. For example, you can convert an existing select query into a make-table query that creates a table that shows the winning bidder for each item in an auction. Create the select query that you want to use as the basis for your make-table query. Figure shows an example query. Be sure that you save the query before continuing. You want to make sure that your query returns the expected results before you convert the query into a make-table query.

Figure shows a sample query that returned the expected results. Figure Run your select query to make certain that it actually produces the results you intended. The Make Table dialog box appears, as shown in Figure Enter a name for your new table in the Table Name text box.

Be careful not to use the name of an existing table unless you really do want to completely replace the existing table with the new table. If you want to create the new table in a different Access database file, select the Another Database option button and then specify the name of the database file in the File Name text box.

You can click the Browse button if you prefer to browse for the file and avoid the possibility of typing the filename incorrectly. Figure Enter the name for the new table that you want to create. Click the OK button. The Make Table dialog box closes and you return to Design view. The only visual clues to show that your query is now a make-table query and not a select query are the description in the Design view title bar and the icon that is displayed on the Query Type icon on the toolbar.

Save the query before continuing. A warning message similar to the one in Figure appears. The warning message indicates the number of records that will be added to your new table. Figure This warning tells you what will happen next and gives you a chance to change your mind. Click Yes to continue and create your new table. Chapter 6: Modifying Data with Queries If, instead of displaying a message similar to the one shown in Figure , Access displays a message telling you that an existing table will be deleted before you run the query, be sure that the correct table name is shown before you click the Yes button.

Otherwise, you could destroy an existing table that you actually want to keep. With update queries, you can make changes throughout a database quickly and efficiently.

Imagine for a moment how bored you would be to search through a table containing several thousand records to find and manually change a specific value. For example, suppose you have a database that contains, among other things, a customer list of several thousand people and businesses. For this more complex situation you really need to use an update query to make the changes easily and efficiently. Creating an update query example You can use an update query to modify a series of records.

In the following steps, I use the example mentioned in the previous section where the tele- phone area code needs to be changed for a group of clients. Create the select query that you want to use as the basis for your update query.

Figure shows an example query in which the state must be NV, and the city must be Reno, and the phone area code must be In this case the Phone field just contains the area code, not the whole tele- phone number.

Figure Create your select query that selects the records you want to update. Figure shows the results of running the query. Figure Verify the query results before you update the records. Chapter 6: Modifying Data with Queries 3. In the Update To row of the Phone field, enter the new value you want to apply to the selected set of records. Figure shows how the query looks after being changed to an update query and having the update value added. Access displays a message similar to the one shown in Figure to tell you how many records will be updated.

This number should be the same as the number of records that the select query selected. Figure Access tells you how many records will be updated.

Click Yes to continue and update the records. Figure shows the result of running the update query and then creates a new select query to show all the records where the state is NV. I specifically set up this example so that it would produce results that you might not expect. Figure This is a sampling of the records after the update query was run.

Understanding the update query results If you refer to Figure , you might be scratching your head. In my example, the area code for any cities other than Reno remained as and most of the Reno records were changed to But why is there a record for Reno without an entry in the Phone field? The answer is simple. The select query I set up had three criteria.

To be selected and therefore updated , each record had to match all the criteria. One of the criterions was that the existing value in the Phone field had to be Deleting Data with Queries Any action query has the potential for being destructive, but delete queries are the ones that seem the scariest. After all, with just a couple of clicks a delete query can permanently wipe out years of data entry work. Delete queries remove all trace of the selected records from your database.

Did I mention that you should be careful? As an alternative to permanently deleting records by using a delete query, you may want to consider adding an active field to your table and marking the records you were thinking of deleting as inactive. That way, you can retain those records for possible future use, but exclude them from any reports by only including active records. Creating a delete query is virtually an identical process to that of creating an update query.

Create a select query, as shown in Figure In this case, I specified that the state must be NV and the phone area code field is empty Access refers to this as a null value. When you change the query type, all the selected records are slated for deletion, so make sure the results are correct before moving on. Figure Create the select query that selects the records you want to delete. Doing so changes the query design slightly, as you can see in Figure Figure The delete query is ready to delete records from your database.

Click Yes to continue and delete the records. See Chapter 5 for more informa- tion on referential integrity. Adding New Data with Queries Compared with delete queries, append queries seem almost tame.

Instead of permanently removing records from your database, an append query adds new records to a table. Any existing records remain untouched and you can sleep a little easier.

Even though append queries seem benign, you should still exercise a bit of cau- tion with append queries just as you should with any other action query. The old saying garbage in, garbage out certainly applies here! The process is pretty much the same no matter which type of action query you want to create. You can read more about parameters in Chapter Creating an append query generally follows the same path as any of those other types of action queries.

However, you have some additional options that may make an append query even more useful to you. The purpose of an append query is to add new records to a table. This simple fact opens up a whole bunch of possibilities:. You can also use something called database replication to synchronize differ- ent copies of a database, but the process can be pretty complex to set up and administer. The following steps show you how to use an append query to add records to a table. Here are the steps you need to follow:.

In this case you use the link option, but you need to consider how you want to add records on an ongoing basis before making a choice. The various sections presented in this document will help you to build a solid knowledge foundation creating Forms in Microsoft Office Access Preview the PDF.

It is never too late to start learning and it would be a shame to miss an opportunity to learn a tutorial or course that can be so useful as Microsoft Access Forms especially when it is free! Your email address will not be published. Would you like to change to the site?

Wallace Wang. Packed with straightforward, friendly instruction, this update to one of the bestselling Office books of all time gets you thoroughly up to speed and helps you learn how to take full advantage of the new features in Office After coverage of the fundamentals, you'll discover how to spice up your Word documents, edit Excel spreadsheets and create formulas, add pizazz to your PowerPoint presentation, and much more. The fun and friendly approach of Office For Dummies makes doing Office work easy and efficient!

Besides writing computer books, Wallace also enjoys performing stand-up comedy just to do something creative that involves human beings as opposed to machines. File Name: microsoft access for dummies pdf free download. Create a Database in Microsoft Access for Beginners. Because Access is part of Microsoft Office , you can use many of the tech-. Free download Simulation with Arena. Free download Systems Analysis and Design Methods. Free download Teach Yourself C. Free download The Economics of Knowledge.

Free download The Little Windows 98 Book. Free download The Moviegoer. Free download The Undomestic Goddess. Free download Turn the Ship Around! Free download We Need New Names. Free download Windows Registry. Free download Windows 95 Black Box. Free download Windows 98 Made Easy. Free download Windows Nt 4 Answers! Free download Windows Nt 4 Workstation Unleashed. Free download Windows Nt 4. Free download Windows XP.

Free download Xview Programming Manual Vol. Free Ebook Access Bible. Free Ebook ASP. Free Ebook Design for Community. Free Ebook Download Confessor. Free Ebook Download Multimedia Maths. Free Ebook Download Spring Fever.

Free Ebook Download The Chamber. Free Ebook Excel for Engineers and Scientists.