Amal Nagm

Enter the sample data manually

1. On the Create tab, in the Tables group, click Table.
   Office Access 2007 adds a new, blank table to your database.
   Note You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.
2. Double-click the first cell in the header row and type the name of the field in the sample table.
   By default, Access denotes blank fields in the header row with the text Add New Field, like so:
   
3. Use the arrow keys to move to the next blank header cell and then type the second field name (you can also press TAB or double-click the new cell). Repeat this step until you enter all field names.
4. Enter the data in the sample table.
   As you enter the data, Access infers a data type for each field. Each field in a table has a specific data type, such as Number, Text, or Date/Time. Data types help ensure accurate data entry and help to prevent mistakes, such as using a telephone number in a calculation. You can specify the data type for each field in a table, and can change a field's data type under certain circumstances. For this sample table, you should let Access infer the data type.
5. When you finish entering the data, click Save
   Keyboard shortcut Press CTRL+S.
   The Save As dialog box appears.
6. In the Table Name box, enter Customers, and then click OK.

---

Copy the table to a spreadsheet program and then import it into Access

1. Start your spreadsheet program and create a new, blank file. If you use Excel, a new, blank workbook is created by default.
2. Copy the sample table provided in the previous section and paste it into the first cell of the first worksheet.
3. Using the technique provided by your spreadsheet program, name the worksheet Customers.
4. Save the spreadsheet file to a convenient location and go to the next steps.

Import the table into Access

1. In a new or existing database:
   On the External Data tab, in the Import group, click Excel.
   -or-
   Click More, and then select a spreadsheet program from the list.
   The Get External Data - Program Name Spreadsheet dialog box appears.
2. Click Browse, open the spreadsheet file that you created in the previous steps, and then click OK.
   The Import Spreadsheet Wizard starts.
3. By default, the wizard selects the first worksheet in the workbook (Customers, if you followed the steps in the previous section), and data from the worksheet appears in the lower section of the wizard page. Click Next.
4. On the next page of the wizard, select First row contains column headings, and then click Next.
5. The next page of the wizard offers you an opportunity to change field names and data types or to omit fields from the import operation, by using the text boxes and lists under Field Options. You should not do this for this example. Click Next.
6. On the next page of the wizard, in the Field Options box, select Yes (No Duplicates) from the Indexed list, and select Long Integer from the Data Type list. Click Next to continue.
7. On the next page of the wizard, select the Choose my own primary key option, choose CustomerID from the list, and then click Next.
8. By default, Access applies the name of the worksheet to your new table. Make sure that the table is named Customers, and then click Finish.
9. On the last page of the wizard, you have the option of saving the import steps for later reuse. Because you are importing a sample table, it is not recommended that you save the import steps.

Note If you don't have a spreadsheet program, you can copy the sample data to a text editor, such as Notepad.

---

Build the query

1. On the Create tab, in the Other group, click Query Wizard.
2. In the New Query dialog box, click Simple Query Wizard, and then click OK.
3. Under Tables/Queries, click the table that has the data that you want to use. In this case, click Table: Customers. Note that a query can also use another query as a recordsource.
4. Under Available Fields, double-click the Contact, Address, Phone, and City fields. This adds them to the Selected Fields list. When you have added all four fields, click Next.
5. Name the query London Contacts, and then click Finish.
   Access displays all of the contact records in Datasheet view. The results show all of the records, but show only the four fields that you specified in the query wizard.
   Review the initial SQL statement

   Click SQL View on the Access status bar; or, right-click the query object tab, and then click SQL View. Access opens the query in SQL view, and displays the following:

   SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City] FROM Customers;
    

   As you can see, in SQL the query has two basic parts: the SELECT clause, which lists the fields that are included in the query, and the FROM clause, which lists the tables that contain those fields.

   Note If you added the fields in a different order from the preceding procedure, the order that you used will be reflected in the SELECT clause.

   ---

6. Close the query, and note that your query is automatically saved.

Add criteria to the query

1. In the Criteria row of the City field, type london.
   Now, add two more criteria to make the results even more meaningful. Suppose that you only want to see the records in which both the address and the phone number are present.
2. In the Criteria row of the Address field, type Is Not Null AND <>"". Do the same in the Criteria row of the Phone field.
   Note The criterion, Is Not Null AND <>"", is true whenever there is any known, non-empty value for the field for which it is a criterion. It is false whenever there is no known value (Null), or when the value is known to be empty (""). You can use this expression to check a field for a known, non-empty value.
3. Switch to Datasheet view to see the results.

Review the SQL statement with criteria

Click SQL View on the Access status bar, or right-click the query document tab and then click SQL View. Access displays the following SQL code:

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City] FROM Customers WHERE (((Customers.[Address]) Is Not Null And (Customer.[Address])<>"")   AND ((Customer.[Phone]) Is Not Null And (Customer.[Phone])<>"")   AND ((Customer.[City])="london"));
 

You will note that the SQL statement now has a WHERE clause. The criteria that you specify for query fields appear in SQL in the WHERE clause. In this case, they are combined by using the AND operator.

---

Specify alternate criteria sets by using OR

1. Switch back to Design view.
2. Remove the Is Not Null AND <>"" string from the Criteria row of the Phone field.
3. In the Or row of the Phone field, type Is Not Null AND <>"".
4. In the Or row of the City field, type london.
5. Switch to Datasheet view to see the results. All records that include either an address, a phone number, or both, and where City is London, are displayed in the query results.
   Note To specify more than two alternate criteria sets, use the rows below the Or row. Each row represents an independent set of criteria.

Review the SQL statement with revised criteria

Click SQL View on the Access status bar, or right-click the query document tab and then click SQL View. Access displays the following SQL code:

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City] FROM Customers WHERE (((Customers.[Address]) Is Not Null And (Customer.[Address])<>"") AND ((Customer.[City])="london"))  OR (((Customers.[Phone]) Is Not Null And (Customers.[Phone])<>"") AND ((Customers.[City])="london"));
 

You will note that two things have changed: the criterion that specifies that City is London now appears twice, and the criteria in the WHERE clause are now combined by using the OR operator.

---

Add calculations to the query

1. Open the query in Design view.
2. From the Customers table window, drag the BirthDate field to the first blank column in the design grid. You can also double-click the field name to automatically add it in the first blank column.
3. In the next column, in the Field row, type the expression that will calculate the age for each record. Type Age: DateDiff ("yyyy", [BirthDate], Date()).
   Age is the name you are using for the calculated field. If you do not supply a name, Access will use a generic name for the field, for example, EXPR1. The string following the colon (:) is the expression that supplies the values for each record. The DateDiff function calculates the difference between any two dates, and returns that difference in the specified format. The format, yyyy, returns the difference in years, and the [BirthDate] and Date() elements of the expression supply the two date values. Date is a function that returns the current date, and [BirthDate] refers to the BirthDate field in the underlying table.

   Note The calculation used for Age in this example is an approximation that may be slightly inaccurate, depending on the current month.

   Switch to Datasheet view. You see two additional fields, BirthDate and Age, in the result.

Review the SQL statement with calculations

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City], Customers.[BirthDate],  DateDiff("yyyy",[BirthDate],Date()) AS Age FROM Customers WHERE (((Customers.[Address]) Is Not Null And (Customer.[Address])<>"") AND ((Customer.[City])="london"))  OR (((Customers.[Phone]) Is Not Null And (Customers.[Phone])<>"") AND ((Customers.[City])="london"));
 

Summarize query values

1. Open the query in Datasheet view.
2. On the Home tab, in the Records group, click Totals.
3. Click the Total row in the Contact column.
   In the drop-down list, you can choose between None and Count. Because the Contact column displays text values, other functions, such as Sum and Average, are not relevant, and are therefore not available.
4. Select Count to count the number of contacts that are displayed in the result.
   The number 5 is displayed in the Total row.
5. In the Age field, select Average. Because the Age field evaluates to a number, it supports the Sum,Average, Count, Maximum, Minimum, Standard Deviation and Variance functions.
   Access displays the average age in the Total row.