Introduction to Microsoft Access and Databases 3/8

Exporting database objects to HTML
Exporting database objects to ASP : Create a data access page
View "Working with Queries > Queries: What they are and how they work" in MS Access 2000 help
Create an SQL Query in MS Access
Selecting data from the database

Matching character strings: LIKE

* Exporting database objects to HTML

  1. In the Database window, click the name of the table, query, or form, you want to export, and then on the File menu, click Export.
  2. In the Save As Type box, click HTML Documents (*.html;*.htm).
  3. Click a shortcut on the Places Bar or click the arrow to the right of the Save In box and select the drive or folder to export to.
  4. In the File Name box, enter the file name.
  5. Select the Save Formatted check box to: save the table, query, or form in a format similar to its appearance in Datasheet view, enable the AutoStart check box, and display the HTML Output Options dialog box after you click Save in step 6.
  6. Select AutoStart if you want to display the results in your default Web browser.
  7. Click Save.

* Exporting database objects to ASP : Create a data access page by using a wizard

  1. In the Database window, click Pages under Objects.
  2. Click the New button on the Database window toolbar.
  3. In the New Data Access Page dialog box, click Page Wizard.
  4. Click the name of the table, query, or view that includes the data that you want to base your data access page on.

Note   You don't need to do this step now — you can specify the record sources for the page in the wizard.

  1. Click OK.
  2. Follow the directions in the wizard dialog boxes.

If the resulting page doesn't look the way you want, you can modify the page in Design view.

Note When you create a page by using a wizard, Microsoft Access automatically saves the page as an HTML file in the current folder and adds a shortcut to the page in the Database window. Place the mouse pointer over the shortcut in the Database window to display the path to the file.

* View "Working with Queries > Queries: What they are and how they work" in MS Access 2000 help

SQL, originally an acronym for “Structured Query Language” is a unified language for defining, querying, modifying and controlling the data in a relational database. Its name is officially pronounced “ess-cue-ell” (according to the American National Standards Institute), but many people say “sequel”.

* Create an SQL Query in Microsoft Access

  1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. Without adding tables or queries, click Close in the Show Table dialog box.
  4. Right click once, and select SQL View in the pop up menu.
  5. Write out a simple query using the SELECT field FROM table statement, using one of the tables in your database. (e.g.

SELECT CompanyName FROM Customers;

using the NorthWind database).

  1. Click on the Run icon to test the query.
  2. Repeat step 4 to return to SQL View.

* Selecting data from the database

Relational operations – projection, selection and join – allow you to tell the system exactly what data you want to see. Projection selects columns, selection selects rows, and join brings together data in related tables.

All three of the data retrieval operations are expressed with the SQL keyword SELECT.  Here’s a simplified example of its syntax:

SELECT select_list

FROM table_list

WHERE search_conditions

The SELECT . . . FROM statement accepts fields from a table in the format

SELECT field1, table2.field2, table3.field2, field3

FROM table1, table2, table3

The table name prefix can be dropped from the field specified in the SELECT statement if the field name is unique among the tables being analyzed in the SELECT statement (e.g. field1 and field3 above). However, the prefix is required for non-unique fields (e.g. table2.field2 and table3.field2 above)

Example 1 :

SELECT title_id, publishers.pub_id, pub_name, address, city, state

FROM titles, publishers

WHERE titles.pub_id = publishers.pub_id

In this example, the query selects the title_id, publishers.pub_id (this implies the pub_id field in the publishers table – the name of the table is explicitly mentioned, because pub_id is not a unique field name in this example – the titles table contains a pub_id field too), pub_name, address, city and  state fields from the titles and  publishers tables, for all the cases when titles.pub_id = publishers.pub_id.

Example 2 :

SELECT pub_name, address, city, state, qty_ordered

FROM publishers

WHERE (qty_ordered > 50

       AND NOT qty_ordered > 100)

OR state = ‘California’

In this example, the query selects the pub_name, address, city, state and qty_ordered fields from the publishers table, for all the cases where the quantity ordered is between 50 and 100, or where the publisher was from California.

Example 3 :

SELECT title_id, ytd_sales* price – advance

FROM titles

This example illustrates how arithmetic operations can be carried out in a query.

 

* Matching character strings: LIKE

Some problems can’t be solved with comparisons : e.g. “His name begins with ‘Mc’ or ‘Mac’ – I can’t remember the rest”. In a case like this, wildcards are required, and are represented in SQL with the % sign and the LIKE keyword.

e.g.

SELECT au_lname, city

FROM authors

WHERE au_lname LIKE ‘Mc&’ or au_lanme LIKE ‘Mac%’

EXERCISE:

(to be added later, so as to co-ordinate with other sections)