Cape May County ETTC Technology Proficiency Courses

TPC15-2 Intermediate Access part 2

Using an Access Database in a Word Mail Merge

    1. View this page - http://www.ssw.com.au/SSW/Database/HowToDoMailMergefromWord.htm
    2. Now Start Word. Start a Mail Merge as shown in the instructions above. Use the file called mailmerge.mdb when you go to Open a Data Source. Use the table called address.

Queries

    1. Close all databases. Open the test1database.
    2. From the database Window select the Query tab. Click the New button.
    3. Choose Design View. Click on the Customer List database and click Add. Close the Show tables window.
    4. The top half will list the tables (or other queries) used in this Query. The bottom half will list the fields to be shown and what criteria to use. The Criteria is the selection method for data.
    5. Click in the first column and Add the field name (use the drop down menu button). In the 2nd column add city. Make sure the checkbox is checked for each field. If unchecked, the field will not be shown.
    6. In the city field column, click the cell labeled Criteria and type in Tucson. This will list records with the city field equal to Tucson.
    7. Click on the Datasheet View button and view your results. Go back to the Design View.
    8. Now click in the sort cell and choose ascending. Go to Datasheet View.
    9. Save the Query.
    10. Delete the text from the first field Criteria cell.
    11. Add amount owed field.
    12. In the amount owed field column, type in >100 in the Criteria cell. View the Query.
    13. Now erase the >100 from the criteria cell. Type >100 in the cell below. View the Query.
    14. Try some of the other selection Criteria below

>= 200

<> 200

>= "N"

Between 2 And 5

Between #2/2/95# An #2/3/98# (for a date field)

Between [enter first value] and [enter second value]

In (A, B, C)

Null

Is Not Null

Like "A"

Not "A"

Not "A*"

Right([Zip Code],2="11")

Examine this site (good review of query wizards) - http://www.rose-hulman.edu/WCC/Software/Documentation/Workshops/acs97b/acs97b-5.html

 

Relationships Between Tables

Types of relationships - http://msdn.microsoft.com/library/officedev/off2000/accondeterminerelationshipsfordatabases.htm

Using relationships –http://databases.about.com/compute/databases/library/weekly/aa031801a.htm

  1. Close all databases and open the customer database.
  2. When you use multiple tables for a query, Access needs to establish a relationship. Start a new query. Add the customers and orders database. Notice how a simple link is established. Between the field that is shared by both tables.
  3. Add customerID and last from the customers database and product and quantity from the Orders table.
  4. View the query.
  5. Close the query. Do not save it.
  6. GO to the database window. From the toolbar, click the Relationships button.
  7. Add the customers and orders tables. Close the Add Tables window.
  8. Point to the customerID field in the table list for the customers table.
  9. Drag from customerID in the customers table over to the customerID field in the orders table. When you are pointing to the middle of the customerID field in the orders table, let go of the mouse button.
  10. Now click the join types button. Make sure the first option is selected. Click OK.
  11. Close the tables, if asked to save layout, click Yes.
  12. Create a new query. Add the customers and orders table. Select customer ID and product from the orders table. View the query.
  13. Close the query and do not save.
  14. Go back to the Relationships window and double click on the link (must be pointing in the middle of the link).
  15. A dialog button should open. Click the join types button. Select the second option. Click OK.
  16. Create a new query. Add the customers and orders table. Select customer ID and product from the orders table. View the query.
  17. Close the query and do not save.
  18. Open the Relationships window. Click on the link between the two tables (need to try and point right at the middle and right on the line) and right click to delete the relationship.
  19. Now clear the layout completely and save the layout.

Lookup Columns

    1. Open the Table called Products and become familiar with the fields.
    2. Now open the Orders Table.
    3. Go to design view and insert a new field called product2. For the Data type choose Lookup Wizard. Go through the wizard. Choose the Products table and choose the products2 field.
    4. Be very careful creating a lookup column. This creates a relationship between the two tables. A lookup column can not be deleted until the relationships are removed (commands located in the Tools menu).
    5. Go to design view and click in the product2 field for any of the records.

last modified 5/15/01