Microsoft Access 2003

Access 2003

This courseware teaches the fundamentals of Microsoft Access 2003. Access 2003 is a relational database management program; it allows you to create and manage desktop and client/server database applications.

Content

With Microsoft Access 2003, you can create table to contain your information, queries to retrieve information from the table, and forms and reports to make the information available to users in various ways.

Delivery

Typically, the database files you create in Access 2003 have an .mdb extension. You can, however, use Access 2003 to export data to a wide variety of other formats.

Creating Files

About Access 2003 Files 

• Microsoft Access 2003 lets you create two Main types of files: database files (with an .mdb extension) and project files (with an .adp extension)

• A database file is the actual information repository - it is a collection of related data which has to do with a particular subject. For example, if you owed a retail chain, you might want to place sales and store information in a database. You could organise this data into tables; you could group employee information in one table, customer information in another, and store information in yet another.

• A project file is a file which connects to the database - it is the basis for client/server applications. For example, a project file might contain forms, reports, or modules; it would not contain any data or data-definition-based objects (like tables). Think of a project as an "in-between" file: it servers as the interface for actual database file. Since a project is a more advanced feature. this foundation-level training course does not discuss them in any great detail.

Creating a new database file

• From the main menu, choose File>New

Click Blank Database. in the File New Database dialog box, specify a location and a name for your database file. Then click Create: 

• The Database dialog box appears, to let you start adding objects and information to your database. We will deal with these features in a future section of this course: 

Note: The default file format is for Access 2003 is "Access 2003 is "Access 200," To change the default file format to "2002-2003," Select Tools > Option. and click on the the Advanced tab in the displayed dialog box. Choose Access 2002-2003 from the Default File Format drop down menu, and click Apply. Click OK to close the dialog box.

Opening and Closing a Database File

Opening a database file

• From the main menu, choose File > Open

Understanding new Access 2003 security features

• If this is your very first time opening a file in Access 2003, you may see an Access warning dialog box stating that unsafe expressions are not blocked. In order to stop the display of this warning dialog box every time you open a file, you will need to download jet 4.0 Service pack 8, which is available at http://www.Microsoft.com.

Note: if you do not see any warning dialog boxes when you open a database, please disregard these instructions.

• If you know the origin of the file, and are absolutely certain that it is trustworthy, click Open. If you are unsure, click cancel

• Under default security settings in Access 2003, this warning will be displayed every time you open a file in Access.

Note: it is possible - although not recommended - to avoid the display of this warning dialog box by lowering the Access 2003 security level from Medium to Low. Be careful - such an action may expose your computer to potentially destructive macro viruses. Consult the various Access Help tools for details on how to change sexy levels.

Closing a database file

• From the main menu, choose File> Close.

Access 2003 interface

When you have completed this learning module you will have seen how to: 

• Use the menu bar.
• Use the Database toolbar.
• Use the task pane.
• Use the status bar.
• Use the Office Assistant.
• Customise the toolbars.

Access 2003 Workspace

Using the menu bar

• The menu bar contains all of the Access 2003 menu options. With these menu options, you can access most of the Access 2003 features. The options available in each menu often depend on the object you are working with and the mode of Access 2003.

• By default, the Menu bar appears under the title bar, at the top of the screen. However, you can place this bar wherever you want on the screen.

• To move the Menu bar, find the dotted Greg line at the far left of the menu bar. Place your mouse on this line. The mouse indicator changes to a four-arrow symbol. This means you can drag the bar to your desired location:

USING THE DATABASE TOOLBAR

The Database toolbar is a bar of buttons which act as shortcuts to many of the Access 2003 features which are most often used. This toolbar is very similar to the standard toolbar in other office 2003 applications, such as word and Excel. 

By default, the database toolbar appear at the top of the screen, just below the menu bar. Like the Menu bar, though, you can place this bar wherever you want it on the screen.

To move the Database toolbar, find the dotted Greg line at the far left of the Database Toolbar. Place your mouse on this line: the mouse indicator changes to a four-arrow symbol. This means you can' drag the bar to your desired location:

.

Using the task pane

• The task pane is a window which acts like a dialog box, except you can keep it on the screen as you work. Depending on the task it relates to, it provides access to different features.

• Access 2003 has five new task panes, in total, there are eight task panes: Getting Started, Help, Searching Results, File Search, Clipboard, New File, Template Help, and Object Dependencies.

• You  can changes the type of task pane by clicking the Other Task Panes down arrow in the top right corner of the task pane. The eight task pane types are then available on a drop-down menu.

• One example of a task pane is when you create a new database file (by choosing File > New). The various options for creating a new database file are then provided in the pane.

• As with toolbars, you can move the task pane around the screen. You can do this by dragging its title bar.

• If the task pane is too small for you, you can resize it. You can achieve this by positioning the Mouse indicator over a side edge or corner of the task pane. The mouse indicator changes to a Two-sided arrow, which means you can drag it to make the pane bigger or smaller;

Note: Task panes - including the five new Access 2003 panes - will be discussed in greater detail later in this training manual

Using the status bar

• The status bar appears at the bottom of the screen. It provides status bar might tell you what mode you are in or what column you are correctly applying changes to.

Customising the toolbars

• You can right-click any toolbar and choose Customize on the context menu to customize the appearance and behavior of Access 2003's toolbars:

• In the Customize dialog box, use the three tabs to specify which toolbars should appear and how you want the commands to works:

• When the Customize dialog box is showing, you can right-click any button or menu option to customise names, button images, properties, and so on:

Create a Query 

About queries

• A query similar to the idea of filters discussed earlier in this module: it is a way to search for data in your database. Unlike filters, thought, which can only work within a single datasheet (or table) at a time, queries can let you search multiple tables at once.

• Access lets you create five main types of queries: Select queries, parameter queries, Crosstab queries, Action queries, and SQL queries, For the purposes of this training module, only select queries are described.

Creating a new query in Design view

• In the Database window, click the Queries button on the toolbar along the left side of the window:

• Double - Cluck Create query in Design view. A query window opens. If you have multiple table in your database, the show table dialog box also opens;

• If you want the criteria to involve a mathematical operator (e.g., sales greater than $10,000). Place the cursor in a Criteria field and. click the Build button on the toolbar: 

• The Expression Builder dialog box opens. In this dialog box, use the pane at the top to build the expression. You can add operators by clicking the buttons below this pane.

• You can have Access show you common expression by clicking the folders in the bottom left corner of the dialog box and then double-clicking the entries which appears in the right panes to add them to the top pane: 

• When you have built the query to the appropriate specifications, click the Run button on the toolbar. Access returns all matching record in a new window:

• To re-show the Design window, click the view button in the top left corner of the toolbar: 

About creating a new query using a wizard

• The other method for creating a new query is by using a wizard. This wizard takes you through the various steps of building a simple query.

• The requirements for a building a query in this way are similar to those for building a query in Design view - you need to provide the same basic units of information. 

• The screens of the wizard may very, depending on the selections you made on an earlier screen. That's why the procedures below are broken up according to option selections. 

• If you need to revise your design. you can always click the Back button in the wizard to return to an earlier step.

Creating a numerical query using the wizard 

• In the Database window, click the Queries button on the toolbar along the left side of the window.

• Double-click Create query by using wizard. The simple Query Wizard opens.

• On the first page, select the field you want involved in the query. From the Table/Queries drop-down list, choose a table. The available fields from that table then appear in the Available Field list.

• Double-click a field to add it to the selected Fields list.

OR select one of the Available Fields and click the arrow > button.

• You can remove a field from the selected Fields list in the same way. Double-click one of the field from the selected Fields list.

• Make sure you include a numerical field.

• When you are finished, click Next. On the next page, tell the wizard how you want the information returned:

• Choose the Detail option if you want every field (of records which match the search criteria) returned. Choose the summary option if you want Access to summarize the numerical data. You can specify the summary action by clicking Summary Option and providing the desired information in the resulting dialog box. If you want Access to show you a count of all the records it finds, select that check box and click OK to close the dialog box. 

• Click Next to type a name for the query:

• To run the query and have the results returned to you, select Open the query to view information and click Finish.

• To hold off on running the query Andrea to view the structure of the query In Design view before running it, select Modify the query design and click Finish. A query window containing the parameters you have specified opens; you can modify the query as desired. Click the Run button on the toolbar to run the query and see the results: 

Creating a non-numerical query using the wizard

• In the Database window, click the queries button on the toolbar along the left side of ta window.

• Double-click Create query by using wizard. The simple Query Wizard opens: 

• On the first page, select the fields you want involved in the query. From the table/Queries drop-down list, choose a table. The available fields from that table then appear in the Available Fields list.

• Double-click a field to add it to the Selected Fields list (or select one and click the arrow > button).

• You can remove a field from the selected Fields list in the same way: double-click it or select it and click the 'Back' arrow < button.

• Do not include a numerical field. When you are finished , click Next. 

• On the next page, type a name for the query:

• To run the query and have the results returned to you, select Open the query to view information and click Finish.

• To hold off on running the query and to view the structure of the query in Design view before running it, select Modify the query design and click Finish. 

• A query window containing the parameters you have specified opens. Here you can modify the query as desired.

• Click the Run button on the toolbar to run the query and see the results:

Saving queries

• You can save a query in multiple ways.

• If you are working in the Database window you can click the queries button on the left. Then right-click an existing query and choose save as from the context menu. Type a new name to save the query with a different name. You can choose to save it as a query, from, report, or data access page.

• If you have the results window open, from the main menu choose File > Save As. Type a name for the query. You have the same save option as in the step above:

• If you have the query Design view open, from the main menu choose File> Save as. Type a name for the query. You have the same save option as in the step above.

Opening an existing query's result window

• Access shows all existing queries in the queries view of the Database window.

• To open a query, click' the queries button on the toolbar on the left side of the Database window:

• Double-click a query

• The results of the query appear in the window

Open an existing query in Design view

• Click the. queries button on the toolbar on the left side of the Database window.

• Select a query. Click the Design button at the top of the Database window.

Note: You can also right-click a query and choose Design View from the context menu:

Refining the result of query

• After you run a query, its results are displayed in a separate window. Take a moment to scan through the result; you may find that the information was not what you respected, or the wrong information appears. Often. An in correct query is the result of error in the criteria which you gave Access when constricting the query:

• You can revise a query's construction in Design view. To refine result, take a look at all of the parameters you have specified in Design view. Ask yourself whether the fields, tables, and search criteria you have specified are related to the desired information. 

• After you make some changes, you can re-run the query until you get the result you want:

About adding calculations to a query Design view

1. The procedure in using the wizard to create a simple query described earlier told you how to perform a mathematical operation on numerical result (which as summing a field, providing an average, or so on). You can also do this in Design view.

• When you display the results of a calculation in a field, keep in mind that Access doesn't add this field to the database; it does not change your table or data in any way. Every time you run the query, Access reruns the calculation so that the results are always based on the most current data in the database.

Adding calculations in Design view

• In the Database window, click the Queries button on the toolbar along the left side of the window.

• To open an existing query, select it and click the Database button, To start a new query in Design view. double-click create query in Design view.

• In Design view, build the query as you have learned to do in the earlier procedure about Design view: choose tables, fields and criteria you want to involve in the query. Make sure you include the numerical field on which you want to perform the calculation:

• When you are ready to add the calculation, click the tables button on the toolbar:

• Another row, called Total, is automatically added to the lower pane of the Design view window.

• Click in a Total cell beneath the column which represents the number you want to perform the operation on. For example, if you want to average unit prices, you should choose to display the Unitprice field (a numerical field)  and then click (under Unit price) in the total cell:

• A drop-down arrow appears in this. box. Click the drop-down arrow to view a list of calculations you can perform for that field. Choose one of the following options:

Group by allows you to define the groups you want to perform the calculations for (e.g. to show total sales by category, select Group By for the  category field).

Sum allows you to add up values in the returned data. This option is valid for Number, Date/Time Currency, and AutoNumber types of data.

Avg allows you to average the values of returned data. This option is valid for Number, Date/Time, Currency, and AutoNumber types of data.

Max returns the highest value of the returned data. Thus option is valid for Text, Number, Date/Time, Currency, and AutoNumber types of data.

Count allows you to count the entries in the returned data, not counting Null (blank) entries. This option is valid for Text, Memo, Number, Date/Time,  Currency, AutoNumber, yes/No, and OLE object types of data.

StDev returns the standard deviation of the returned data. This option is valid for Number, Date/Time, currency, and AutoNumber types of data.

Var returns the variance in the returned values. This option is valid for Number, Date/Time, Currency, and AutoNumber types of data.

First returns the first record in the group you are performing calculations on.

Last returns the last record in the group you are performing calculations on.

Expression allows you to create a calculated field by using an aggregate function. An aggregate function is a function like Avg. Sum, Count, or Var which you can use to calculate totals. The advantage to using an Expression calculation is that you can use multiple function in one expression. You create a calculated field by entering an expression into an empty Field cell in the query design grid.

Where allows you to specify criteria for a field which you are not using to define groupings. If you select this option for a field, Access will deselect the Show check box (I' so doing, hiding the field in the query results window).