Ms access 2007 sql query pdf

In some database implementations other data types exist such as Images for pictures or other data. However, the above three data types are most commonly used. This relationship allows us to specify that the Customer Nathanial Axe has both a Checking and a Savings account that were both opened on the same day: December 1, However, it is impossible to have a Detail record without a matching Master record.

For example, a Customer may not necessarily have any account information at all. However, any account information must be associated with a single Customer. Each table also must have a special column called the Key that is used to uniquely identify rows or records in the table. Values in a key column or columns may never be duplicated. In organizations, the job of analyzing the business and determining the appropriate database structure tables and columns is typically carried out by Systems Analysts.

A Systems Analyst will gather information about how the business operates and will form a model of the data storage requirements. From this model, a database programmer will create the database tables and then work with the application developers to develop the rest of the database application. For this tutorial, we will consider a simple banking business.

The bank has many customers who open and maintain one or more accounts. For each Customer, we keep a record of their name and address. We also assign them a unique CustomerID. We assign this unique identifier both for convenience and for accuracy. It is much easier to identify a single customer using their CustomerID rather than by looking up their full name and address.

In addition, it is possible for the bank to have two customers with the same name e. In such cases, the unique CustomerID can always be used to tell them apart.

In a similar fashion, all accounts are assigned a unique account number. An account can be either a checking account or a savings account. Savings accounts earn interest but the only transactions allowed are deposits and withdrawals. Checking accounts do not earn interest. We maintain the date that the account was opened. This helps us track our customers and can be useful for marketing purposes.

Finally, we maintain the current balance of an account. In the previous section, we gave the structure and some sample data for the Customer table and the Accounts table.

These will be used to support the data storage part of our Banking application. Database Applications In any database application, each of the tables requires a means to get data into them and to retrieve and modify the data at a later time. The primary way to get data into tables is to use data entry forms. The primary ways to get data back out of tables or to display data in tables are to use queries or reports. In a complete database application, all of the forms and reports are linked together in a Navigation Form — a single page that will have links to each of the parts of the application.

A large database application may have dozens of data entry forms and reports. For this tutorial, we will create a data entry form for each table, a query for each table and a report for each table. Creating and Viewing Tables Tables are the main units of data storage in Access. Recall that a table is made up of one or more columns or fields and that a given column may appear in more than one table in order to indicate a relationship between the tables.

From the business example discussed earlier, we concluded that two tables would be sufficient to store the data about Customers and their bank Accounts. We now give the step-by-step instructions for creating these two tables in Access. There are a number of ways to create a table in Access.

Access provides wizards that guide the user through creating a table by suggesting names for tables and columns. The other main way to create a table is by using the Design View to manually define the columns fields and their data types. In this tutorial, we will describe the steps to create a table using the Design View. Students are encouraged to experiment on their own with using the Create Table wizard. In Access , and , the Create New Table tab should already be highlighted and a new table named table1 created.

If this is not the case, click on the Create tab and click on the Table icon. Then pull down the View menu and choose Design View. The Table Design View will appear. In a later step, we will assign an appropriate name for this table. The next step is to define the Key for the table and to save the table. Recall that the Primary Key will be used to uniquely identify a record in the table in this case a Customer.

Highlight the CustomerID field and click on the Primary Key button on the button bar Notice that a small key appears next to the field name on the left side. Note: To remove a primary key, simply repeat this procedure to toggle the primary key off. As a final step, the table must be saved. Pull down the Office menu and choose the Save As menu item. A dialog box will appear where the name of the new table should be specified. Note that Access gives a default name such as Table1 or Table2.

Simply type over this default name with the name of the table. For this example, name the table: Customer Then click on the OK button. At this point, the new Customer table has been created and saved. Field names in Access can be up to 64 characters long and may contain spaces. However, the use of spaces in field names and table names is strongly discouraged.

If you wish to make field names easier to read, consider using an underscore character to separate words. However be certain no spaces appear before or after the underscore. A figure showing the design view with the new table definition filled in is given below: 1. Define a Primary Key for the Accounts table. Click on the AccountNumber field with the Right mouse button and choose Primary Key from the pop-up menu.

Save the new Accounts table by pulling down the File menu and choosing the Save menu item. Fill in the name of the table: Accounts Then click on the OK button.

Viewing and Adding Data to a Table Data can be added, deleted or modified in tables using a simple spreadsheet-like display.

You can now create the two tables described earlier below: 1. Customer Table 2. Use the arrow keys and the delete or backspace keys to change the existing data. To delete a record, first navigate to the record of interest. Then pull down the Edit menu and choose the Delete menu item.

At this point in the tutorial, we have created two tables, Customers and Accounts, and added data to each one. In the subsequent sections, we will cover how to query and report on the data in the tables and how to create a user-friendly data entry form using the Access wizards. Creating Relationships Between tables Recall that one of the main characteristics of relational databases is the fact that all tables are related to one another.

In the Bank database thus far, the Customers table is related to the Accounts table by virtue of the CustomerID field appearing in both tables. Access has a means to make this relationship explicit using the Relationships screen. Access uses this information when designing reports, forms and queries that require more than one table to be displayed. To get started, make sure the Accounts table and the Customer table are both closed. Access will halt creation of any relationships if the table are currently opened.

To close a table, either right-click on the table name in the tab above the table and choose the close menu item, or click the small X to right above the table. Highlight both the Customers table and the Accounts table as shown below and then click on the Add button. Then click on the Close button to close this dialog box. Upon releasing the mouse button, the Edit Relationships dialog box will appear as below: Access will do its best to determine the Relationship Type almost always it will select One-to- Many.

This option puts constraints into effect such that an Accounts record can not be created without a valid Customer record, and Access will also prevent a user from deleting a Customer record if a related Accounts record exists. At this point, click on the Create button to create the relationship.

Close the relationships screen and select Yes to save the changes to the Relationships layout. If the relationship does not appear in the above fashion, highlight it and press the delete key to delete it. Then go back to the table design view and make certain that the CustomerID field is designated as the key of the Customers table. Then go back to the Relationships screen and try to recreate the relationship. Click on the Tables tab on the Access main screen 2.

Click on the New button. Choose the Design View and click the OK button. Fill in the name, data type and description of each of the fields in the table. Designate a primary key by clicking on one of the fields with the right mouse button and then choose Primary Key from the pop-up menu. Save the table by pulling down the File menu and choosing Save.

Close the new table by pulling down the File menu and choosing Close. To change the design of an existing table e. Highlight the name of the table to be modified and click on the Design button. Make the necessary changes. Close the table by pulling down the File menu and choosing Close. To add, delete or change data in an existing table: 1.

Highlight the name of the table to be modified and click on the Open button. Make the necessary changes to the data. Save the table data by pulling down the File menu and choosing Save. To create or edit relationships between tables: 1.

Pull down the Tools menu and select the Relationships menu item. To display tables, right click and choose Add Tables 3. To create new relationships, drag a key field from one table and drop it on the associated field in another table 4. To edit an existing relationship, double click on the relationship line. To delete an existing relationship, click on the relationship line and press the delete key. Queries can access a single table or multiple tables.

Queries can be used to carry out other tasks such as for creating formatted reports and data entry forms, and for exporting subsets of data to Excel or another software program. In this section, we show how to use the Access Wizards to create queries for a single table and for multiple tables.

Single Table Queries In this section, we demonstrate how to query a single table. For example, show only those bank customers living in GA. Creating a query can be accomplished by using either the query design view or the Query wizard.

In the following example, we will use the query wizard to create a query. To create a new query, click on the Create tab. Then click on the Query wizard button. The first step in the Simple Query wizard is to specify the table for the query and which fields columns should be displayed in the query output.

Three main sections of this step are: 1. Available Fields — Those fields from the table that can be displayed. Selected Fields — Those fields from the table that will be displayed. Notice that the available fields change to list only those fields in the Customer table.

Highlight one of the fields and then click on the right arrow button in the center between the two areas. Repeat this for each of the four fields to be displayed. When done with this step, the wizard should appear as below: Click on the Next button to move to the next and final step in the Simple Query wizard. In the final step, give your new query a name. Note the new query Customer Address appears under the Customers table. Single Table Queries Continued In the following example, we will modify the Customer Address query to only display customers in a certain state.

To accomplish this, we will make use of the Query Design View. Open up the Customer Address query in the design view by right-clicking on the name of the query. Then select the Design View menu item as shown below. In the top section, the table s used for the query are displayed along with the available fields. In the bottom section, those fields that have been selected for use in the query are displayed.

For this example, we will filter the records to only display those customers living in the State of Georgia GA. We will also sort the records on the City field. To sort the records on the City field, click in the Sort area beneath the City field. Note the use of single quotes to surround the characters.

Run the query by clicking on the Run button with the large red exclamation point. From the Access main screen, click on the Create tab. Then click on the Query Wizard button. Choose the Simple Query wizard option and click on the OK button. Then click the Next button.

In the next panel, you will be asked to choose between a detail or summary query. Choose detailed query and click on the Next button. Name the new Query : AccountsQuery and click on the Finish button. In the next part of the exercise, we will modify the query to sort the output on the account number and only display the Savings accounts. From the Queries tab on the Access main screen, highlight the AccountsQuery and click on the Design button.

Change the Sort order for the AccountNumber field to Ascending. Run the query by double-clicking on the AccountsQuery name. Finally, save and close the query to return to the Access main screen. Multiple Table Queries Up to this point, queries involving only one table have been demonstrated.

It is almost a given that queries will need to involve more than one table. For this example, assume that a manager would like to see a list of all of the customers and the type of account s that each one maintains at the bank.

Such a query requires data from both the Customers table as well as the Accounts table. In such queries, Access will rely on the Relationships established between tables to guide how the data will be assembled to satisfy the query.

Before proceeding with these next instructions, make certain the One-to-Many relationship between the Customers and Accounts table has been created see section Creating Relationships for a review of this process. To start the process of creating a multiple table query, highlight the Create tab and click on the Query Wizard button to create a new query.

The result from this step is down below: Click the Next button to continue. In the next step of the wizard, an option will appear to provide some level of Summary.

As with single table queries demonstrated previously, one can change the query definition in design view by adding filters e. In the second step of the wizard, click on the Summary choice instead of Details and then click on the Summary Options… button. The query wizard can be used to create simple queries that access a single table. It is also possible to then modify the query to sort or filter the records.

Creating a query using the query wizard: 1. From the Access main screen, click on the Queries tab. Then click on the New button. From the Queries tab on the main Access screen, click on the New button and choose the Simple Query wizard option.

If the table contains numeric fields, either detailed or summary information may be specified for the query. Finally, name the new query and click on the Finish button. As a final note, Forms and Reports can be created based on existing queries. In a previous section, we described how to add data to a table using a spreadsheet-like view of the data. Data entry forms offer a more user-friendly interface by adding labels for each field and other helpful information. Access provides several different ways of creating data entry forms.

As with queries, a data entry form can be based on a single table or it can work on multiple tables at the same time. In this section, we cover the basic steps for using a wizard to create a data entry form based on a single table. After this exercise, a multiple-table form is demonstrated. Creating a Single Table Form using the Wizard In this example, we will create a simple data entry form for the Customer table.

To begin the process, click on the Create tab on the Access main screen. As with the other components in Access, there are buttons for creating a New form, Open an existing form and Design an existing form. For this example, click on the New button to create a new form. Several buttons for creating a new form will appear.

For this tutorial, choose the Form wizard. In the first step of the Form wizard, we need to specify the fields from the Customer table that will appear on the form. In this case, we want all of the fields to appear. Move each of the fields from the Available Fields side over to the Selected Fields side as in the following figure. Then click on the Next button. This is similar to a paper form.

This layout is suitable for viewing data one record at a time. This is similar to how a spreadsheet would display the data and is suitable for displaying multiple records of data at a time. This is suitable for viewing a single record at a time as with the columnar layout. For this example, choose the columnar layout as shown in the figure below and click on the Next button.

Access will show several sample display styles that determine how the form will appear, including elements such as fonts, colors and the background used in the form. Access and skips this step in this wizard and provides styles that can be applied to the form in Design mode.

For this example, for MS Access , select the Office style as shown below and click on the Next button. To move to the next or previous record, use the record navigation bar at the bottom of the form: The buttons on the navigation bar perform the following functions: Go to the first record. Go to the previous record. Go to the next record. Go to the last record. Go past the last record to add a new record.

To close the form and return to the Access main screen, pull down the File menu and choose Close. To open the form at any time, highlight the form name under the Forms tab on the Access main screen and click on the Open button.

One quick final note on forms. When the form is created, MS Access looks at how the table is designed and creates the form based on the properties of the table. If you make any changes to the table, you will need to re-create the form again in order to see those table changes reflected in the form. One example where this might happen is is if you change a column in your table from a text box to a combo box, or if you add or remove any columns from your table.

Exercise: Creating a Single Table Form For this exercise, we will create a data entry form for the Accounts table created in a previous exercise. Select the Accounts table and all of the available fields and click on the Next button.

Choose a Tabular layout and click on the Next button. For Access choose the Office style and click on the Next button. Name the form: AccountsDataEntry Then click on the Finish button to create, save and view the new form.

Choose a table and a form wizard 2. Specify the fields columns that will appear in the form 3. Specify the layout for the form 4. Unlike queries, however, reports add formatting to the output including fonts, colors, backgrounds and other features. Reports are often printed out on paper rather than just viewed on the screen.

They can also be attached to e-mail and exported and posted as web pages. In this section, we cover how to create simple reports using the Report wizard. Creating a Single Table Report using the Wizard In this example, we will create a simple report for a single table using the Report wizard.

As with the Queries and Forms, we begin by selecting the Create tab from the Access main screen. To create a new report, click on the Report Wizard button. In the next step of the Report wizard, is used to specify the fields from the Customer table that will appear on the report. A grouping level is where several records have the same value for a given field and we only display the value for the first records. In this case, we will not use any grouping levels so simply click on the Next button as shown below.

In the next step, the sorting order of the report can be specified. For this example, we will sort the records on the CustomerID field. To achieve this, pull down the list box next to the number 1: and choose the CustomerID field as shown in the figure below. This is similar to how a spreadsheet would display the data. Generally, reports use the tabular layout. For this example, choose Tabular layout and set the page Orientation to Landscape so that all of the fields will fit across one page.

This is shown in the figure below. Click on the Next button to continue. For this example, choose the Office style and click on the Next button to continue.

Finally, give a name for the new report: CustomerReport and then click on the Finish button to create, save and display the new report. Note that on some screens, the last field, Zip, may not display without scrolling over to the right. Once the report is displayed, it can be viewed, printed or transferred into Microsoft Word or Microsoft Excel. Exercise: Creating a Single Table Report For this exercise, we will create a report showing all of the Accounts information.

From the Access main screen, click on the Create tab and Click on the Report wizard. Select all of the fields in the Accounts table by moving them all over to the Selected Fields side and then click Next 3. This is shown in the following figure: Click on the Next button to continue. Choose to sort the report on the AccountNumber field. Note that a new button will appear called Summary Options.

Choose the Balance field and select the Sum option. Choose the option to show both Detail and Summary data. Then click on the OK button. Click on the Next button. Choose a Block layout and click on the Next button. Choose the Corporate style and the click on the Next button. Finally, name the report: AccountsReport and click on the Finish button to create, save and run the report.

To close the report and return to the Access main screen, pull down the File menu and choose Close. Review of Creating and Running a Report As can be seen in the report exercise, there are many ways to create reports to show summation, sorting and layout of the data. Further study of Reports will show how to modify the layout using the Design View.

Students are encouraged to work with the Report wizards to create different styles and types of reports. The forms are linked according to the relationship between the tables.

Click the Next… button to move to the next step. At this point MS Access detects that this form will involve data from two different tables.

The second prompt asks how the forms should be created. The Subform is the form that will display the detailed data from the Accounts table.

For Access , select the Office style and click the Next… button to move to the next step. This step will not appear in MS Access or A switchboard is typically created after all of the forms and reports for a database application have been completed.

It can be used to guide the user to an appropriate set of forms and reports. Note that starting with Access , Switchboards are not available by default you will need to add this to the ribbon bar manually using the Options. Access and make use of the Navigation Forms.

In this section, a default switchboard will be created. From the Access main screen, click on the Database Tools tab and Click on the Switchboard Manager as shown in the figure below. If this is the first switchboard made for this database, you will be prompted to create a new one as shown below.

Click the Yes button. The Edit Switchboard Page will appear as shown below: 4. Add a new switchboard item by clicking on the New… button. The Edit Switchboard Item form will appear as shown below. Click on the OK button to save this new Switchboard item. Repeat the above step two more times to add Switchboard items for the Accounts DataEntry form and the Customer Report. Once completed, click the Close button. Then click the Close button once more to close the Switchboard manager.

From the main MS Access screen look for a new section labeled Switchboard items. Navigation forms take the place of Switchboards but provide much the same functionality. Namely, they are designed to give the user the ability to run forms and reports without having to hunt through all of the different menus and lists objects. Typically a database application will have one main Navigation form that will appear when the database is opened.

In this section, the basic steps for creating and running a Navigation Form in MS Access will be demonstrated. To get started, click on the Create tab on the Access or ribbon bar. Under the section for Forms look for the item labeled Navigation Form. Note that this may be located on the button labeled Other Forms. Note that there are 6 different default styles of Navigation Forms. Each one places the buttons in a different configuration on the screem.

Buttons can be aligned across the top, either side or some combination. For this tutorial select the Horizontal Tabs configuration which appears as the first item on the list.

At this point a new Navigation Form will be created with a row of tabs across the top. The first tab will be labeled [Add New] 4. To add items to the Navigation form, drag the items from the list on the left over to the spot on the Navigation Form labeled [Add New].

This is shown by the arrow in the above figure. The result is shown below. Next drag and drop the CustomerMasterForm as shown below. Next drag and drop the CustomerReport as shown below. At this point we have created a new navigation Form and added three items along the top of the page. Save the Navigation Form by right-clicking on the name of the form and choosing Save as shown below: 8. Close up the Navigation Form by right-clicking again on the name of the form and choosing Close menu item.

At this point the new Navigation Form has been created and saved. The next step will be to view the Navigation Form and navigate the different forms and reports linked to it. To view the Navigation Form, look for the Unrelated Objects group on the left side of the screen.

This is especially useful for end users so that they always see the same form when they open up the database. To set the Default Form, pull down the File menu and select the Options menu item.

Click on the Current Database item on the left hand side. The next time this database bankdb. So as much as possible we want to try and suggest good data to be put into the tables and to reject any obviously bad data. Once bad data makes its way into the database it is often difficult to correct. Records that are missing data are also a problem. So again it will always help the user any time we can provide a default value or provide good suggestions of what data to put in.

Below are some additional properties that can be customized to provide better data input quality. In general these techniques should be used at the time tables are created. MS Access will use these properties when creating data entry forms and reports. When adding a new data record it is helpful to supply as many default values as is reasonable so the user does not have to type in as much data. To set a default value, highlight the name of a column field in the table Design View and then set the Default Valueproperty accordingly.

In the example below the Default value for Balance has been set to 0: Also consider putting the Required property into play to ensure the user will put something in the field.

In the above example the Required property is set to No. Setting this to Yes will force the user to enter data. Validation Rules put limits around the values that are allowed to be stored in a column field. Rules can be set up compare the data a user enters and then either accept or reject that value. In case the data is rejected by the rule, a message from the Validation Text property will be displayed.

In this example, the Balance column field will be validated to make sure it can never be a negative number. Rather than have the user type in these values, we can change the default text box display of a field to a Combo Box. A Combo Box looks like a text box but it has a small arrow on the right hand side.

Clicking on the arrow presents a list of possible values. For this example we will modify the Customer table and provide a Combo Box with a list of suggested values for the State column field. Change the Row Source property to: Value List.

This setting allows us to type in a list of suggested values separated by semicolons. Type in the value list in the Row Source property. Leave the rest of the properties as the default values. Make sure the Limit to list property is set to No. This way if a Customer comes in from another state not on the list, the user can type it in. At this point the properties look like: 7.

Save the current table design and then close up the Design View. In some cases the values that can be supplied for a field can come from another table or some external data source. So rather than typing in a static list of possible values, the Row Source property can be set to a Query.

A common situation occurs when we need to supply the value for a foreign key. The CustomerID column is the key of the Customer table. When it appears in the Accounts table, the CustomerID column is a foreign key. When supplying a value for the CustomerID column in the Accounts table, we are restricted to using only existing CustomerID values that exist already in the Customer table. Close any open tables or forms and open the Accounts table in Design View.

For the Row Source property pull down the list and select the Customer table. Change the Bound Column property to 1 7. The semi-colon can appear at the end of the last clause or on a line by itself at the end of the SQL statement. The following illustrates what a SQL statement for a simple select query might look like in Access:. This example SQL statement reads "Select the data that is stored in the fields named E-mail Address and Company from the table named Contacts, specifically those records in which the value of the field City is Seattle.

If an identifier contains spaces or special characters such as "E-mail Address" , it must be enclosed in square brackets. A SELECT clause does not have to say which tables contain the fields, and it cannot specify any conditions that must be met by the data to be included. More information about how you use these clauses is presented in these additional articles:.

Like Microsoft Excel, Access lets you sort query results in a datasheet. An ORDER BY clause contains a list of the fields that you want to use for sorting, in the same order that you want to apply the sort operations.

For example, suppose that you want your results sorted first by the value of the field Company in descending order, and — if there are records with the same value for Company — sorted next by the values in the field E-mail Address in ascending order.

Note: By default, Access sorts values in ascending order A-Z, smallest to largest. Use the DESC keyword to sort values in descending order instead. Sometimes you want to work with summarized data, such as the total sales in a month, or the most expensive items in an inventory. For example, if you want your query to show the count of e-mail addresses listed for each company, your SELECT clause might resemble the following:. The aggregate functions that you can use depend on the type of data that is in the field or expression that you want to use.

For more information about the available aggregate functions, see the article SQL Aggregate Functions. If you want to use criteria to limit your results, but the field that you want to apply criteria to is used in an aggregate function, you cannot use a WHERE clause. For example, if you only want the query to return rows if there are more than one e-mail addresses associated with the company, the HAVING clause might resemble the following:.

When you want to review all the data that is returned by several similar select queries together, as a combined set, you use the UNION operator. The SELECT statements that you combine must have the same number of output fields, in the same order, and with the same or compatible data types.

When you run the query, data from each set of corresponding fields is combined into one output field, so that the query output has the same number of fields as each of the select statements. Note: For the purposes of a union query, the Number and Text data types are compatible. When you use the UNION operator, you can also specify whether the query results should include duplicate rows, if any exist, by using the ALL key word.

For example, suppose that you have a table named Products and another table named Services. Both tables have fields that contain the name of the product or service, the price, warranty or guarantee availability, and whether you offer the product or service exclusively. Although the Products table stores warranty information, and the Services table stores guarantee information, the basic information is the same whether a particular product or service includes a promise of quality.

You can use a union query, such as the following, to combine the four fields from the two tables:. SQL syntax. Access SQL: basic concepts, vocabulary, and syntax. Notes: Access ignores line breaks in a SQL statement. Need more help? Expand your skills. Get new features first.

Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions.