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
Note You don't need to do this step now — you can specify the record sources for the page in the wizard.
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
SELECT CompanyName FROM Customers;
using the NorthWind database).
* 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)