Read Access Advanced text version

Access Advanced

Academic Computing Services A Division of Information Services

www.ku.edu/acs

Abstract: This document introduces users to more advanced functions of

Microsoft Access. Participants will learn how to change join types of queries and build more advanced queries, such as action queries. They will learn how to create reports based off SQL statements and automate a database with macros. They will also learn how to run the repair and compact utility. It is used in conjunction with the Access: Advanced workshop.

Contents

Introduction ....................................................................................................................... 2 Objectives ......................................................................................................................... 2 Prerequisites ..................................................................................................................... 2 Related Training Available from ACS................................................................................ 2 Query Join Types .............................................................................................................. 2 Creating Summary Queries............................................................................................... 4 Creating Parameter Queries ............................................................................................. 5 Creating Crosstab Queries................................................................................................ 6 Understanding Action Queries .......................................................................................... 7 Creating SQL Reports....................................................................................................... 9 Using Macros to Automate a Database .......................................................................... 11 Compacting and Repairing a Database .......................................................................... 16 Getting Additional Help ................................................................................................... 18

© 2003 University of Kansas. All rights reserved

ACS Computer Training Access Advanced

Introduction

Microsoft Access 2000 is a relational database management system. This type of database allows a user to store, retrieve, manage and analyze data quickly and effectively. This particular handout, in conjunction with the Access Advanced class, is designed to help users with some of the more advanced features of Access, such as query join types, advanced types of queries, SQL reports, automating a database with macros, and compacting a database.

Objectives

The goal of this workshop is to introduce participants to more advanced objects and ideas in Microsoft Access. After today's workshop, participants will be able to: · · · · · · Understand and change query join types Create and run parameter queries Create and edit the four type of action queries Create and edit reports based on SQL Create and execute macros Run the compact utility on a database

Prerequisites

It is assumed that the participants in this workshop are familiar with tables, queries and relationships, forms, and reports in Microsoft Access; have taken Access: Introduction, Access: Intermediate, Access Forms, and Access Reports; or have equivalent skills.

Related Training Available from ACS

All workshops offered by Academic Computing Services (ACS), a division of Information Services, are free to KU students, staff, faculty, and approved affiliates. The general public is also welcome to most workshops, but some ACS workshops require a registration fee for them. To learn more about or register for workshops, receive automatic announcements of upcoming workshops, and track workshops you've registered for and have attended, visit the ACS Web site at www.ku.edu/acs/train. You can also check our online schedule at www.ku.edu/acs/schedule for a list of class offerings and their availability. For further workshop related questions, please email [email protected]

Query Join Types

When relationships are created between tables in Microsoft Access, there are Join Properties that tell Access how the data is related. The Join Type tells Access which records to select. There are three different kinds of join types in Access you can choose from through the Join Properties dialog box.

Academic Computing Services A Division of Information Services

-2-

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Inner Join

The default join type when a relationship is created in Access is the Inner Join. This type of join, also known as an Equal Join, will only select records where the joined fields have matching values. When the query is run, Access joins the different records from the tables, so that it looks like one record.

Left Outer Join

A Left Outer Join shows all results in a query from the left side of the join, even if there is no match on the right side. For example, if you had a query that showed students enrolled in classes, and you wanted to check to make sure all of the students in the database were enrolled, you could change the join type to a left outer join. This type of join would show all of the students, and if there were any blanks on the right side (that would be the side that showed the classes the students were enrolled in), you would know not all of the students were enrolled.

Right Outer Join

The third type of join type to choose from in the Join Properties dialog box is a Right Outer Join. This type of join does the exact opposite of the left outer join; thus, it shows all records from the right side of the join even if there are no matches on the left. This join type is commonly used to check for errors in databases.

Note: In the Join Properties dialog box, you will not see the words Inner

Join or Outer Join. You will see an option of 1, 2, and 3. An Inner Join is the default join type of 1. A Left Outer Join is a 2 and a Right Outer Join is a 3.

Changing Join Types

There are a several places you can change a join type, but one of the most common places is inside the Join Properties dialog box. To change a join type you can do the following: 1. Open the query whose join type you need to change in Design View. 2. Double click on the join line to activate the Join Properties dialog box and change the join type. 3. Click OK and run the query through the Run or Datasheet View button.

Academic Computing Services A Division of Information Services

-3-

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Creating Summary Queries

Sometimes you might want to quickly group and summarize information inside of a query without running a report. This type of query is called a Summary Query or Totals Query. A Summary Query can group specific information together and run a calculation for a specified field based on that group. The following steps take you through the process of creating one. 1. Create a query in design view, adding whichever fields you'll need to do the grouping and the field you'll be summarizing.

Note: You need at least two fields for a summary query, one to use for

grouping and the other to use for the calculation. 2. Click on the Totals button to add the Totals: row to the design grid.

3. Change the Group By option of the field you want to perform a calculation on by clicking beside the words Group By and clicking the drop-down arrow. 4. From the list, choose the function you want performed on the field. The following is an example: 5. Click on the Run button 6. Save the query as needed. or Datasheet View button to run the query.

Academic Computing Services A Division of Information Services

-4-

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Creating Parameter Queries

There are many different types of queries you can create in a Microsoft Access database. Parameter queries are one such type. This kind of query is very convenient to a user because it allows them to change the criteria value for the query each time it is run. Every time this type of query is run, a dialog box will appear, prompting the user to enter in criteria. To create a Parameter query, do the following: 1. Create a query in Design View; adding whichever fields you will need for your query. 2. In the field you want the parameter in, type the parameter prompt in the criteria in square brackets. The following example might take place in a field such as state.

3. Save the changes to the query and click the Run or Datasheet View button. 4. When the query is run, the following prompt will appear:

Academic Computing Services A Division of Information Services

-5-

© 2003 The University of Kansas

ACS Computer Training Access Advanced

5. Type in the criteria and press Enter or click the OK button. 6. The query will run only showing records that meet the criteria asked for.

Note: Custom forms or dialog boxes can also be created as a prompt for

parameter queries. These are nice to be able to use when you want to have a custom dialog box with more options that will run a report when the criteria is entered. To create this type of dialog box, you will need to know how to work with form properties and macros as well.

Creating Crosstab Queries

Crosstab queries are a type of query that displays data and can summarize (using functions such as sum, average, etc...) data by grouping the information using field data as column headers and using up to three different fields of information as row headers. One of the easiest ways to create a crosstab query is through the Crosstab Query Wizard. Here are the steps for going through the wizard: 1. Click on the Queries button on the object bar and click on the New button. 2. In the dialog box, choose Crosstab Query Wizard and click OK. 3. In the first step of the wizard, choose the table or query whose fields you want to use in your crosstab and click on the Next button. 4. In the next step, double click on the field whose value you want to use as the row heading. You'll be able to choose up to three fields if needed. Click the Next button when finished. 5. The third step of the wizard looks very similar to the second step, but this time choose which field's values you want to use as the column header by clicking on the name of the field one time. Click the Next button to continue. 6. The fourth step of the wizard will prompt you to choose what kind of calculation you want for each column and row intersection. Choose the field you want to use to do the calculation under the Fields: column and choose the function you want to use from the Functions: column by clicking one time on each choice. Make sure you keep the Yes, include row sums box checked if you want this included as well. Click the Next button to go to the last step. 7. Type in a name for your new crosstab query. Click the Finished button once you're finished. Your query will either open in Design View or run for you to see the records, depending upon which choice you made in the last step.

Academic Computing Services A Division of Information Services

-6-

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Note: If you need to make any structural changes to your query once it's ran,

you can change the view to Design View and make changes as necessary.

Understanding Action Queries

Some types of queries can perform an action to a table. These are called Action queries. Access contains four types of action queries: Make-Table, Delete, Update, and Append action queries.

Make-Table Query

A Make-Table query does exactly as it says; it makes a table based off one or more tables and the fields and records that you choose. There are several reasons a make-table query might be needed. Here are a few of the following: · · · To export information from several tables out of your database Creating a history of records instead of deleting records Making a back-up copy of a table

These are just a few reasons why a make-table query might be what you need. An example of when you could use a make-table query would be if you have to export information out of your database from a query that contains a lot of tables and records. Exporting them from the query could take a lot of time, especially if the tables in the query are linked in your database. Instead of doing it that way, you could make a table to freeze the information and then export the records. Doing it this way, would take a lot less time.

Note: If you are re-running a make-table query many times and do not want

the table to be written over each time it's run, you can rename the table right after running the make-table query.

Delete Query

A Delete query deletes records you specify, by adding criteria, from one or more tables. For example, if you had a product or service that was being discontinued, you could delete those records, if they were no longer needed. The most important thing to remember about a Delete query is that it will delete the entire record.

Update Query

Sometimes you might have a group of records that have a field that needs updating. Maybe find and replace won't work in this case, because each value updated will be something different, but you need to update them all at the same time. For instance, let's say that a group of employee's receives a 3% raise, and you want to update them all together. You might be able to use an update query to update the entire field for each record at the same time.

Academic Computing Services A Division of Information Services

-7-

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Append Query

An Append query adds records from one table into another table. Append queries can be helpful in several different ways. · Appending records to another table based on some criteria. For example, you might choose to append any students that have unpaid parking tickets instead of trying to look for them throughout the table or query, especially if you want to mail just those students a notice. You might choose an append query after importing information from another source, instead of typing in all of the record information. For example, if you receive a report containing new student information, you could append the students into your student table instead of typing it all in.

·

Creating an Action Query

Once you've decided which type of query it is you need, you can easily create it. To do this, do the following: 1. Create a select query in Design View by adding only the fields you need.

Note: The only fields you'll need to add are the ones that will make a new

table; the ones that you're using information from to append to an existing table; the field(s) that needs updating; or any field you're using to designate criteria. .

2. Change the query type using the Query Type button

Note: Dependent upon which type of query you've chosen, depends on what

happens next. For instance, if you choose a Make-Table query, you will be prompted to give the new table a name. If you choose an Append query, you'll be prompted to choose which table you want to append the records to. Then, an Append To: row will be added to the design grid. If the field names are identical, Access will match up the correct fields in the new row. Otherwise, you will need to choose the field names from the drop down arrow as you click in each cell. If you choose an Update query, an Update To: row will be added to the design grid. Choosing a Delete query will add a Delete row to the design grid and place Where under field names. Then, you must add criteria as needed to the criteria field if you only want to delete certain records that meet a condition.

3. Add in the necessary criteria for the query and click on the Datasheet View button to check your information.

Note: This is an important step because once you change the query to an

Action query and run it with the Run button, the changes requested would be made to the table you choose. You cannot click on undo to undo the changes!

Academic Computing Services A Division of Information Services

-8-

© 2003 The University of Kansas

ACS Computer Training Access Advanced 4. Once you've checked the information you're about to append or change, click on the Run button to make the changes. You'll be prompted several times to click on OK to make the changes and given several warnings that changes are about to be made to a table. 5. After running the action query, save the query only if you will need to run it again. Be careful saving it though. Remember; each time the query is run, it will make a change.

Note: You can always save the query and change the options in Design View

before running it again.

Creating SQL Reports

Many Access databases contain reports that summarize information from a query that is built from many different tables. The only problem with building all of your reports this way is that if you change the design of a query by taking out fields, and those fields are in the report, then you'll have to keep fixing your reports. This could end up taking a lot of time if you frequently change your query design. One way around this type of problem is to have Access automatically generate an SQL statement for the Record Source property by using the Build button to create a query in Design View. Choosing to embed a SQL statement in the Record Source property of a report can do away with these types of problems. The record information will change as the data in the tables change, but you won't have to worry about changing fields. Here's how it works. 1. Click on the Reports button in the database window. 2. Double click Create Report in Design View. 3. Double click the Report Selector box to open up the Report Properties dialog box. 4. Choose the Data tab and click in the Record Source property.

Build Button

5. When you click in the Record Source property, you will see a drop down arrow with a Build button beside it.

Academic Computing Services A Division of Information Services

-9-

© 2003 The University of Kansas

ACS Computer Training Access Advanced 6. When you click on the Build button, the SQL Statement: Query Builder will be invoked. When this happens, your screen will change and look similar to the following:

7. In the Show Table dialog box, double click the tables you want to base the SQL Statement from or click on the tables and click the Add button. 8. Once all of the tables are added, you can close the Show Table dialog box. 9. Add the fields that you want to be able to use in the report and/or fields you need for criteria. 10. Once you've finished, click the File menu and choose Close or click the corner close window button. You'll be returned to the Design View of the report. 11. Close the Report Properties dialog box and the Field List box should open. If for some reason it doesn't, click on the Field List button to open the list. 12. Once the Field List button is open, you can choose the fields you want to build your report. Continue to build the report as you normally would and save your changes.

Note: If you have already created a query that contains most of the

information you'd like to embed into the Record Source property of a report, you can open the query and change the view to SQL View, copy the SQL statement, and paste it into the Record Source property of the report.

Academic Computing Services A Division of Information Services

- 10 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Using Macros to Automate a Database

When you create an Access database, there may be times when you need certain events to happen automatically. For instance, you might want a user to be able to click on a button to export information into a desired program or format, or maybe there are specific fields that have to be filled in before a record can be saved. Macros can automate these types of tasks. A macro consists of one or more actions that perform specific operations, such as opening and maximizing a form, generating an email to send information from your database, or running objects such as forms or reports. These types of actions are called events. An event can be an action caused by a user, such as a mouse click, form or report opening, data being changed, or using keyboard shortcut keys; or events can be triggered by the system, such as a program opening or setting an action to be performed on certain dates and times. When you associate events with event properties, you can tell Access to run a macro or event procedure when the event occurs.

Note: An event procedure is a process that is automatically executed due to

a user performing an action, a program code, or triggered by the system.

When you specify a particular action from the action list or set of actions for a macro, you will have to set what are known as Action Arguments. The arguments tell the macro more specifically how to carry out the action requested and/or where to carry out that action. There are several different ways in which macros can be created. They can be macros that run when a button is clicked; when the mouse is moved; when a form or other object is opened or closed; when a control on a form receives or loses focus (when the user moves into or out of that field); when information in a control or record is changed; or when certain conditions are met. There are also many different types of macros that can be created.

Note: Each of the following types of macros can be used together or

separately. For example, you could have a macro group that contains certain macros that run based on conditions and/or events and are assigned to an event property of a control.

Types of Macros

Macro Groups

Macro Groups are macros in which there are several macros grouped together because they perform the same type of action or were created for one object, such as a particular form. Grouping these related macros together in one place helps make it easier to keep track of them. Whenever you group macros together though, even though the macro group will have a name, each individual macro in the group will also have a distinct name. That way, if you want to run one of the macros in the group by itself, you have that option.

Academic Computing Services A Division of Information Services

- 11 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Conditional Macros

Macros can also contain Conditional Expressions when needed. These types of conditional macros run only if certain conditions or criterion are met. For example, if a user was filling out information in a form, and they left a field null (no value or data in the field), you might want a macro to run that would open a custom dialog box telling them to fill out the field they left null. You might also want the focus to immediately shift back to that field (their cursor would be back in the field). However, if they did fill in the data for that field, then you wouldn't want that macro to run. This type of macro would be a conditional macro.

Macros Based on Events

Events are actions initiated by the user or the system. These can be actions such as opening or closing objects, clicking a button with the mouse, or changing data in a control. As you create macros, sometimes you'll want them to run based on these types of events. To do this, you'll want to attach them to event properties. Event properties are what cause a macro or event procedure in Visual Basic to run when a particular event occurs. For example, if you attach a macro name that maximizes a window to the On Open property of a form, when the form opens, the form window will always maximize. When you find the event property you need, you can click in the property and click on the Build button and choose Macro Builder. When you do this, the Design View of a new macro window will open and prompt you to name your macro. Once you've created your macro, don't forget to save the changes before closing the window and returning to the form or other database object. If you forget to do this, Access will prompt you to save anyway.

Creating Basic Macros

There are several ways that you can create macros in your database. One way to create a macro is by going through the macro window. Here's how: 1. In the Macro window, click on the New button. 2. Click in the Action column. 3. When the drop down arrow appears, click on the drop down arrow and choose the action you want. 4. When you've chosen an action, press Tab on your keyboard to move to the Comment field. This field is useful to explain what each action does and is commonly used for documentation. 5. If the action you've chosen has arguments, they will appear for you to fill in the information. 6. Once you've chosen all of the needed actions and filled in the arguments, make sure you save the macro by clicking on the Save button. Name the macro and click OK. Once you've finished, either run the macro by clicking on the Run the macro window. For further running options, see Running Macros.

Academic Computing Services A Division of Information Services

button or close

- 12 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Creating Macro Groups

To create a macro group, do the following: 1. In the Macro window, click on the New button. 2. Click on the Macro Names 4. Tab to the Action column. 5. When the drop down arrow appears, click on the drop down arrow and choose the action you want. 6. When you've chosen an action, press Tab on your keyboard to move to the Comment field. This field is useful to explain what each action does and is commonly used for documentation. 7. If the action you've chosen has arguments, they will appear for you to fill in the information. 8. If the macro you're creating has more than one action, skip the macro name column and continue choosing the actions and filling in the arguments you need for the macro. 9. Once you've chosen all of the needed actions and filled in the arguments, go back to the macro names column and name the next macro. Continue choosing actions and filling out the argument information for each macro. 10. Once you've finished, make sure you save the macro group by clicking on the Save button. Name the macro and click OK. button.

3. The Macro Name column will appear for you to type in the first macro name.

Creating Conditional Macros

To create a conditional macro, do the following: 1. In the Macro window, click on the New button. 2. Click on the Conditions 4. Tab to the Action column. 5. When the drop down arrow appears, click on the drop down arrow and choose the action you want. 6. When you've chosen an action, press Tab on your keyboard to move to the Comment field. This field is useful to explain what each action does and is commonly used for documentation. 7. If the action you've chosen has arguments, they will appear for you to fill in the information. a. If the macro you're creating has more than one action and you want the condition to continue, in the conditions column use ... in the conditions column to tell Access to continue the condition.

Academic Computing Services A Division of Information Services

button to open the condition column.

3. Type in the condition that you want and press Tab.

- 13 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced b. If you do not want the condition to continue, skip the conditions column and continue choosing the actions and filling in the arguments you need for the macro. 8. Once you've finished, make sure you save the macro by clicking on the Save button. Name the macro and click OK.

Note: You can also create conditional macros from an event property in a

form from a control's event property.

Macros Based on Events

There might be times when you're creating a form and need to create a macro that will run when certain events are performed. One example of this would be to create a macro that maximizes a form when the form opens. Another example of this might be to run a macro based on a certain condition, such as a control being left null. You could also create a macro group at that time. Here's one example: 1. In the event property of the control in which you want to attach a macro name, click on the Build button. 2. Choose Macro Builder and click OK. When you do this, the design window of a macro will open and you will automatically be prompted to save the macro. 3. Follow any of the steps as mentioned above and create your macro. Click on the Save to save the macro again. If you do not do this, when you close the macro window, you'll be prompted to save again. 4. Once back inside of the form, test the macro. If it works like you want it to, click on the Save button. If it does not work like you think it should, try Debugging the macro by Single Stepping through it. For more information about single stepping through a macro, see Debugging Macros.

Academic Computing Services A Division of Information Services

- 14 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Running Macros

You'll also find there are several different ways in which you can run a macro, dependent upon which type of macro you create. If you create a conditional macro, the macro will only run when the condition is met. If you create a stand-alone macro, there are several different ways you can choose to run it. Here are just a few of the following: · · · · · · Double click the name of the macro in the macro window Open the macro in Design View and click on the Run button. Create a button and place it on one of your toolbars. Create a new toolbar containing the macro button(s) you want. Create a command button that you can click on to run the macro. Assign a macro to a key combination. o o · To do this, you'll have to save the macro under a special name called Autokeys. This type of macro is a macro group.

Make the macro run automatically when the database opens.

Academic Computing Services A Division of Information Services

- 15 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced o To do this, you'll have to save the macro under a special name called AutoExec. arguments. For more information about the different actions and their arguments, open up Help and on the Answer Wizard tab, type in Actions. Access should list many of the different ones for you and list the arguments for each action. Also listed might be some examples of how and when to use the action.

Note: Microsoft Access has many types of macro Actions each with different

Debugging Macros

When a macro does not run correctly, you'll usually receive some sort of error message telling you that the macro action isn't available and you'll be given an option to halt the macro. In order to find out where the problem occurred, you can try debugging a macro by using the Single Step button to go through each action one at a time very slowly. Here's how: 1. Open the macro you want to single step through by clicking on the Design View button. 2. Click on the Single Step button to turn on single stepping.

3. Click on the Run button to perform the first action. 4. Click on the Step button each time you're ready to go to the next action. 5. When the macro fails, you'll receive a dialog box indicating that the action isn't available and to click on the Halt button.

Compacting and Repairing a Database

As you create and change your database, it will grow, even as you delete objects in it you no longer need. The larger your database grows, the more cumbersome it can become. Running the Compact and Repair Database... will help to reduce the size of the database, so that it will work more smoothly. Here is how you can run it. 1. You can compact the database with it closed or open. If the database is shared with other users, you will have to make sure nobody else has it opened when you run the utility. a) If you compact the database when it's open: i. ii. iii. iv. i. ii. Click on the Tool menu and hover over Database Utilities. Click on Compact and Repair Database... The database will close and you will see a blue dotted line run across the Status bar at the bottom of the screen above Start. Once it has finished compacting, the database will reopen. Keep Access open and click on the Tool menu and hover over Database Utilities. Click on Compact and Repair Database...

b) If you compact the database when it's closed:

Academic Computing Services A Division of Information Services

- 16 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced iii. iv. v. Locate the database you want to compact in the Database to compact from dialog box. Either double click on the name of the database or select the database name and click the Compact button. In the Compact Database Into dialog box, if you want to compact the database back into itself instead of making a copy of it, select the database name again and click on the Save button. The following dialog box will appear, asking you if you want to replace the existing database:

vi.

vii. viii.

Click on Yes. The repair and compact utility will run, and you will be able to see the status along the Status bar at the bottom of the screen above Start.

2. Once you have compacted your database, you'll be able to notice the size difference by looking at the details in the Open dialog box or through Windows Explorer.

Academic Computing Services A Division of Information Services

- 17 -

© 2003 The University of Kansas

ACS Computer Training Access Advanced

Getting Additional Help

ACS provides consulting and Q&A help in a variety of ways: 785/864-0200 [email protected] www.ku.edu/acs/help To evaluate this course online, please visit www.ku.edu/acs/training/evaluation

Last Update: 05/10/2002

Academic Computing Services A Division of Information Services

- 18 -

© 2003 The University of Kansas

Information

Access Advanced

18 pages

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate

1009417


Notice: fwrite(): send of 199 bytes failed with errno=104 Connection reset by peer in /home/readbag.com/web/sphinxapi.php on line 531