mod 4 set 2 acess

creating and saving a table in datasheet view

One way to create a new table is to add a blank table and add fields directly in Datasheet view. On the Create tab, in the Tables group, click the Table button to insert a new table. The new table opens in Datasheet view. Access adds the first field automatically as the primary key with the data type AutoNumber and the name ID. To add a new field, begin typing data for the first record. Access names the first field Field1. Access will apply a data type based on what you type. For example, if you type a date, Access will automatically apply the Date/Time data type to the field. Press Tab or Enter to enter data for the next field. Repeat steps 3 and 4 to enter all the data for the first record. Save the table by clicking the Save button on the Quick Access Toolbar. In the Save As dialog, type the table name in the Table Name box and click OK. another method To save a database object, you can use the keyboard shortcut Ctrl + S.

using a parameter query

A parameter query is a type of select query that allows the user to provide the criteria. When you create a parameter query, you specify the field or fields the query will use to limit the records in the results just as you would if you were entering the criteria yourself. However, you don't specify the exact criteria. Instead, you enter a prompt the user will see when the query is run. The user then enters the exact value or values to use as the criteria. To create a parameter query: In Design view, create a select query or open the existing query that you want to change to a parameter query. In the appropriate cell in the Criteria row, instead of entering specific criteria, type the prompt the user will see, enclosed in brackets. Ideally, the prompt will give the user direction as to what data to enter. Examples of parameter query prompts include [Enter last name] and [Enter appointment date]. Run the query to test it. Notice that before results display, the Enter Parameter Value dialog appears with the prompt you created. Enter a value in the box and click OK. The results of the query display only records that match the value you typed in the Enter Parameter Value dialog. In Figure AC 4.44, the query results are limited to records where the value in the LastName field is Swanson. Typing Swanson in the Enter Parameter Value dialog has the same effect as typing Swanson in the Criteria row for the LastName field. tips & tricks When you use a parameter query as the record source for a form or report, the user will see the Enter Parameter Value prompt whenever the form or report is opened. tell me more You can prevent users from entering a parameter input in the wrong data type. On the Query Tools Design tab, in the Show/Hide group, click the Parameters button. The Query Parameters dialog opens. In the Parameter column, enter the parameter prompt exactly as you entered it in the query grid. In the Data Type column, select the appropriate data type. Click OK. Now if a user attempts to use a parameter value with the wrong data type, he or she will see an error message rather than incorrect query results.

creating a basic report based on a table or query

A report displays data from a table or query in a format suitable for printing. Like forms, reports depend on a record source for their data. Unlike forms, you cannot enter new data into a report. The easiest way to create a new report is to use the Report button. To create a basic report based on a table or query: In the Navigation Pane, select the table or query record source for your report. On the Create tab, in the Reports group, click the Report button. When you save the report, notice that the default name in the Report Name box is the same as the name of the table or query on which the report is based. Type a new name if you want to use something else. In addition to the report title at the upper left side of the report header, the report includes the date and time in the upper right corner of the header and the page number centered in the page footer. tips & tricks The basic report layout is a simple tabular grid with each record displayed as a new row similar to the Multiple Items form. If your report has many columns, this format may not fit on a single page.

changing data type

After you have created your databases tables, you may find that you want to change the data type for a field. Before changing the data type, carefully consider the characteristics of any data you've already entered in the table. Will changing the data type invalidate current data? For example, if you change the data type from Short Text to Number, Access will delete all non-numeric data. To change the data type for a field from Datasheet view: Click anywhere in the field you want to change. On the Table Tools Fields tab, in the Formatting group, expand the Data Type list, and select the data type you want. Access will warn you that some data may be lost. If you are sure you want to continue, click Yes. If the change will require Access to remove data from the table, Access will show a second warning telling you how many records will lose data. You will only see the second warning if the change will delete data. Once you click Yes, the data type change is made and you cannot undo it. tips & tricks Changing data type could have unexpected consequences. It's best to plan carefully and create the tables before entering data. However, if you need to change the data type after values are entered, make a backup copy of the database before proceeding. tell me more If the field you are changing is the primary or foreign key in a relationship, Access may require you to delete the relationship before changing the data type. You can reestablish the relationship after the change is made. another method You can also change data type from Design view, but we do not recommend it. When you change a field's data type from Design view, Access does not warn you that you may lose data. You could potentially remove all the data from a field if you change from one data type to another data type that is incompatible with the data already entered.

creating a table in design view and setting the primary key

Another way to create a new table is in Design view. In Design view, you create the fields without entering data. You must specify the field name and data type for each field. Notice that the Design view window is divided into two panes. The top pane lists the table fields. The bottom pane is the Field Properties pane. It displays details about the selected field. You will learn more about using the Field Properties pane in other skills. To create a table in Design view: On the Create tab, in the Tables group, click the Table Design button. Type the name of the first field. Press Tab. Expand the list of data types, and select the data type you want. Press Tab again. Optional: Type a useful description of the field. Press Tab to go to the next field. Repeat steps 2- 4 to enter all the fields for the table. Before saving the table, you should set the primary key field. Because the data contained in the primary key field must be unique for each record, primary keys are often IDs—product IDs, employee IDs, or record IDs. If your data does not already contain a field that is unique for each record, you can add a new field that uses the AutoNumber data type. Using an AutoNumber field ensures that each record has a unique numerical ID. If your table contains a field that you know is unique for each record (such as a previously established product ID, employee ID, or part number), you can set this field as the primary key. To set the primary key in a table: In Design view, click the field that is going to be the primary key. On the Table Tools Design tab, in the Tools group, click the Primary Key button. Notice that on the Table Tools Design tab, in the Tools group, the Primary Key button is highlighted to indicate that the selected field is the primary key and a key icon appears in the row selector at the left side of the field. In the Field Properties pane, the Indexed property is set to Yes (No Duplicates) because values in the primary key field must be unique. tips & tricks Once you establish a field as the primary key, Access automatically sets the Required property to Yes to ensure that each record has a unique primary key. another method In Design view, right-click the row selector and select Primary Key.

filter data using autofilter

By applying a filter to a database object, you display a subset of records that meet the filter criteria. AutoFilter displays a list of all the unique values in the field. This feature is available for table, queries, and forms. If you have used Microsoft Excel, AutoFilter will be familiar to you. To filter a datasheet using AutoFilter: Open the database object in Datasheet view. Click the arrow at the top of the column in the column that contains the data you want to filter for. At first, all of the filter options are checked. Click the (Select All) check box to remove all the checkmarks. Click the check box or check boxes in front of the values you want to filter for. Click OK. As shown in Figure AC 4.59, Access displays only the records that include the value(s) you selected. The field that is filtered displays a filter icon next to the arrow in the field header. On the Home tab, in the Sort & Filter group, click the Toggle Filter button to clear the filter. Click the Toggle Filter button again to reapply the filter. Notice that when the database object has a filter applied, the Filtered button is highlighted next to the navigation buttons at the bottom of the object. You can also click this button to toggle back and forth between the filtered view and the unfiltered view. To remove the filter, display the AutoFilter list again, and select the Clear filter option. Once you use the Clear filter command, you cannot use the Toggle Filter or Filtered button to show the filter again. You have to recreate it. tips & tricks Filtering with AutoFilter is temporary. When you close the database object, the filter is not saved. If you want a more permanent filter applied to a query, add criteria in Design view instead. another method To display the AutoFilter list, on the Home tab, in the Sort & Filter group, click the Filter button.

filter data using filter by selection

If a record that contains the data you want to filter for is visible, you can click the field and use the Filter by Selection feature. Besides filtering by matching exact values, you can filter for values that meet broader criteria. To filter by selection: Select the data you want to use as the filter criteria. On the Home tab, in the Sort & Filter group, click the Selection button and select a filtering option. The first option is to filter for only records that match the selected field value exactly. The second option is to filter for all records that do not match the selected field value. The other options will vary depending on the data type of the selected field. A Short Text field will include options to filter for records that contain or do not contain the text in the selected field. A Number or Currency field will include options to filter for records that are less than or greater than the selected field value. A Date/Time field will include options to filter for records that include a date/time on or before or after the selected date as shown in Figure AC 4.61. To remove all filters from a database object, on the Home tab, in the Sort & Filter group, click the Advanced button arrow and select Clear All Filters. tips & tricks You can apply filters to more than one field at a time. another method The filter options available by right-clicking a value are more extensive than those available from the Selection button menu. Right-click anywhere in the field you want to filter. Point to the Filters option and then click the filter option you want from the submenu. The Filters option will include the data type (for example, if the field is a Short Text field, the right-click menu will include Text Filters; if it is a Date/Time field, the right-click menu will include Date Filters). When you make a selection from the submenu, a dialog may open, allowing you to enter specific filter criteria.

adding fields to a form in layout view

If you are starting with a blank form, you will need to add controls to display field data. The most common type of control is the text box control. Text box controls can display text, numbers, dates, and similar data. A text box control that displays data from a table or query field is called a bound control because it is connected (bound) to the field. Unbound controls are not connected to field data directly. Adding a field to a blank form places two controls in a stacked layout. A label control displays the name of the field on the left and a bound text box control displays the field data on the right. To add a field to a form in Layout view: If necessary, on the Form Layout Tools Design tab, in the Tools group, click the Add Existing Fields button to display the Field List pane. In the Field List pane, click the + in front of the table or query that contains the field(s) you want to add. If the table you want is not visible, click the Show all tables link in the Field List pane. Double-click a field name to add it to the form. A new bound control is automatically created at the top of the form, along with a label control. To add a second column to the stacked layout, click a field name in the Field List, and drag it to the right of the field you just added. Access displays an I-bar shape to indicate where the controls will be placed. If you double-click the field name instead of dragging it, Access will maintain the single-column stacked layout and add the new controls below the currently selected control. Continue adding fields and editing labels until your form is complete. You can add fields to any space in the layout grid using the click-and-drag method. If you look closely, you can see the dotted lines outlining the grid. Use these lines as a guide when you are dragging fields to the layout. To edit the text in a label control, double-click the control to place the cursor within the text. Edit the text normally. Save the form, and then switch to Form view to verify that it looks and behaves as you expect. tips & tricks You don't have to start with a blank form. Use these same techniques to add fields to any form. tell me more To delete a control, click the control once to select it and then press Delete or Backspace. If you delete a bound text box control, Access will delete the associated label control as well, if there is one. another method If you prefer working in Design view, you can add fields using the same procedures as described for working in Layout view.

inserting, deleting, and moving fields in design view

If you are working in Design view, you can always add new fields to the end of the field list by typing a new field name and selecting a data type. You can also insert fields between existing fields. To insert a new field in Design view: Click the field below where you want to insert the new field. On the Table Tools Design tab, in the Tools group, click the Insert Rows button. Enter the field name and select the data type. Enter a description if you want. When you are finished, save the table. To delete a field in Design view: Click anywhere in the field you want to delete. On the Table Tools Design tab, in the Tools group, click the Delete Rows button. When Access asks if you want to permanently delete the field, click Yes. To change the order of fields in Design view: Select the row by moving the mouse pointer over the row selector when the mouse pointer changes to an arrow . Access highlights the entire row to indicate that it is selected. Hold down the mouse button and drag to the new position. As you drag, the mouse pointer changes to the move shape and Access displays a thick black line to show you where the field will be placed. Release the mouse button to finish moving the field. tips & tricks When you move a field column in Datasheet view, the new position is reflected in Datasheet view only. It does not affect the actual design of the table. However, when you move a field in Design view, you are changing the design of the table, so the new field position is reflected in Datasheet view as well. another method To insert a field in Design view, right-click the row selector at the left side of the field and select Insert Rows to insert a new field row above the selected field. To delete a field in Design view, right-click the row selector to the left of the field name, and select Delete Rows.

adding a lookup field from a list

Lookup fields are useful for fields that reference a specific list of items. A lookup field does not need to reference data in another table or query. You can enter your own values to create a custom list. To review the procedures for creating new lookup fields, refer to the skill Adding a Lookup Field from another Table. To modify an existing field to use a lookup field with values you specify: Open the table in Design view. Click the field you want to modify to use a lookup list. Click the Data Type drop-down arrow and select Lookup Wizard... The first step of the wizard asks you to determine where your lookup list data will come from. Click the I will type in the values that I want. radio button. Click Next to go to the next step. Observe that Access enters 1 as the default number of columns for the list. If you want more than one column, you can change this value. Press Tab to go to the first cell in the first blank column. Type the values in the table exactly as you want them to appear in the lookup field. Click and drag the right border of the column header to make the column wider or narrower. You can double-click the right column border to autofit the column to the data. Click Next to continue. Access will keep the original field name. If you want to change it, type a new name. If you want to restrict data entry to only items in the list, click the Limit To List check box. Click the Finish button to complete the lookup list. Save the table. Switch to Datasheet view to test the new lookup field. You may need to adjust the column width to display the lookup data properly. tell me more If the field you want to change to a lookup field uses the Date/Time or Currency data type, you will need to change the data type to Number before you can modify it to be a lookup field.

importing data from excel

Many small companies try to keep data in Excel spreadsheets. Eventually, however, the data and the needs of the organization may outgrow the functionality of Excel. Their data should be moved to a database. By importing data from individual worksheets into separate database tables, you can define relationships and create user-friendly forms for future data entry. To import data from Excel into a new table: On the External Data tab, in the Import & Link group, click the Excel button. The Get External Data - Excel Spreadsheet dialog opens. Click the Browse... button to open the File Open dialog. Navigate to find the spreadsheet that contains the data you want to import. Click the file name and then click Open, or double-click the file name. The option to import the source data into a new table is selected by default. Click OK. If your spreadsheet contains more than one worksheet, select the worksheet you want to import. If you want to import a specific named range instead of an entire worksheet, click the Show Named Ranges radio button and then select the named range you want to import. The preview box shows the columns in the worksheet you selected. Click Next. If the first row of your spreadsheet corresponds to field names, click the First Row Contains Column Headings check box, and Access will automatically use the column headings as field names. If you forget to check this option, Access will name the fields Field1, Field2, etc., and the spreadsheet heading row will be imported as the first record in the table. Click Next. The next step allows you to specify properties for each of the fields. You can make changes here or modify field properties after the data import is complete. Click Next when you are finished modifying field properties. To rename a field, click to select the field in the preview image and then type the new name in the Field Name box. To specify a data type for a field, click to select the field in the preview image and then select the data type from the Data Type drop-down list. To exclude a field from the import, click to select the field in the preview image and then click the Do not import field (Skip) check box. By default, Access will add a new AutoNumber field to the table to use as the primary key. If the worksheet already includes a column with a unique identifier for each record, click the Choose my own primary key. radio button and select the column name from the drop-down list. Click Next to finish the import. Access will suggest a name for the table using the Excel file name. You can modify the name or accept the suggestion. Click Finish. After the import is complete, you have the option to save the import steps so you can easily run the same import again later. Check the Save import steps check box. If you save your export specifications, you can run the export again later by clicking the Saved Imports button on the External Data tab, Import group. Click the Close button to close the Import - Excel Spreadsheet dialog. Each row in the Excel spreadsheet is imported as a record in the new table. If there are errors, and Access is unable to import some of the data, you will see a warning message. tips & tricks When you import data, a separate copy of the data is inserted into your database. Changes you make to the records in your database do not affect the original copy of the data. tell me more You can also import data from Excel and append the records to an existing table. Refer to the skill Adding Records to a Table by Importing for more information. another method To import data from an Excel spreadsheet, right-click any table in the Navigation Pane, point to Import, and click Excel.

working with attachment fields

One of the most useful enhancements to recent versions of Access is the ability to create Attachment fields to store files as attachments to records. Attachments can be pictures, Word documents, or almost any other type of data file. For security reasons, Access will not allow program files (for example, .exe or .bat files) or any files greater than 256 MB as attachments. To add an Attachment field from Datasheet view: At the far right side of the table, there is a column with the header Click to Add. Click the arrow to expand the list of available field types, and click Attachment. Notice that you cannot rename the Attachment field. It is designated by a paperclip icon. (You can, however, rename the field in Design view.) Once you've added the Attachment field, use the Attachments dialog to add and manage attachments for each record. To add an attachment: Double-click the Attachment field in the record to which you want to add the attachment. The Attachments dialog opens. Click the Add... button and browse for the file you want to add. Double-click the file to add it, or click the file once, and then click the Open button. Click OK to save the attachment and close the Attachments dialog. In Datasheet view, the number next to the attachment icon in each record tells you how many attachments there are. Records can have multiple attachments in a single Attachment field. tips & tricks Attachment fields cannot be changed to another data type. tell me more The attachment is not visible in the table. However, when you start working with forms and reports, the attachment is available for display in those database objects.

creating a new blank form in layout view

One way to start a new form is to begin with a blank form and add fields from tables and queries manually in Layout view. To create a new blank form directly in Layout view, on the Create tab, in the Forms group, click the Blank Form button. Notice that there are no records in the new form. The new blank form does not have a record source defined. The form is an empty layout until you add controls. tips & tricks Access will sometimes select a table or query to use as the record source for the form based on the object selected in the Navigation Pane or based on the first table listed in the Navigation Pane. If this is not the record source you want to use for the form, don't worry. The record source will be updated as you add fields. You can start a new blank form directly in either Layout view or Design view. Layout view may be easier to work with, but if you need to add advanced controls, you'll need to work in Design view instead.

creating a new blank report in layout view

One way to start a new report is to begin with a blank report and add fields from tables and queries manually in Layout view. To create a new blank report directly in Layout view, on the Create tab, in the Reports group, click the Blank Report button. Notice that there are no records in the new report. The new blank report does not have a record source defined. The report is an empty layout until you add controls. tips & tricks Access will sometimes select a table or query to use as the record source for the report based on the object selected in the Navigation Pane or based on the first table listed in the Navigation Pane. If this is not the record source you want to use for the report, don't worry. The record source will be updated as you add fields. You can start a new blank report in either Layout view or Design view. Layout view may be easier to work with because you can see data in the report as you work.

using the simple query wizard

Queries allow you to display and manipulate a subset of data from a table. A table of customer information may include many fields that you don't need on a daily basis. You can use a query to generate a more manageable list displaying just the customer first names, last names, and phone numbers. Queries can also be used to combine data from related tables into a single database object. If you want a list of appointments including the full customer name, use a query to show fields from both the Appointments and Customers tables. A query can combine data from both tables because there is a one-to-many relationship between the Customer field in the Appointments table (which stores the customer ID number) and the CustomerID field in the Customers table. To review the concept of a one-to-many relationship, refer to the skill Understanding and Viewing Table Relationships. Use the Simple Query Wizard to create a simple select query. A select query displays data from one or more related tables or queries, based on the fields that you select. To create a query using the Simple Query Wizard: On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, Simple Query Wizard is selected by default. Click OK. Click the Tables/Queries drop-down arrow. Click the first table or query from which you want to add data. To add a field to the query, double-click the field name in the Available Fields list to add it to the Selected Fields list, or click the field name and then click the button. To add data from another table or query, click the Tables/Queries drop-down arrow again and make your selection. Add the field or fields you want to include in the query. When you have added all the fields you want, click Next. The radio button to create a detail query is selected by default. The detail query shows every field you selected for every record. Click Next. Give the query a meaningful title. To see the results of the query immediately, verify that the Open the query to view information. radio button is selected. Click Finish. Notice that the query results datasheet looks like a table datasheet. It has the same navigation buttons at the bottom. However, the Table Tools Fields tab and the Table Tools Table tab are not available when you are viewing the query results datasheet. You cannot manipulate the structure of the underlying tables when you are viewing query results. tips & tricks You do not need to include the related primary key field and foreign key field(s) in the query.

setting the default value property

The Default Value property adds a preset value to the field. Entering a value in the Default Value property can save time during data entry. For example, if most of your employees live in California, use CA as the default value for the State field. As shown in Figure AC 2.23, when a field has a default value set, the Default Value button appears highlighted. Whenever a new record is created, the default value is entered into this field automatically. To add a default value in Datasheet view: Select the field to which you want to add a default value. On the Table Tools Fields tab, in the Properties group, click the Default Value button to open the Expression Builder. In the Expression Builder, enter the numerical value, text, or formula you want to use as the default value for the field. The default value expression always begins with the = sign, which Access adds for you. If the default value is a text string with a space or other special character in it, you must enclose it in quotation marks. If the text is something simple, such as CA, you do not need to type the quotation marks. Access will add them for you. Click OK. You can also set the default value from Design view: Select the field. In the Field Properties pane, click in the Default Value box and type the numerical value, text, or formula you want to use as the default value. If you want to use the Expression Builder, click the Build... button at the right side of the Default Value box. Save the table.

modifying the field size property

The Field Size property limits the number of characters that can be entered in a text field. The default size for a Short Text field is 255 (the maximum size for a Short Text field). Limiting the field size can ensure that data are entered properly. For example, if you want entries in the State field to always use the two-letter state abbreviation, limit the field size to 2. To modify the Field Size property in Datasheet view: Select the field. On the Table Tools Fields tab, in the Properties group, type the new field size in the Field Size box and press Enter. If you are making the field size smaller, Access will warn you that the smaller size may result in data loss, as Access will delete any data in the field that exceeds the new field size limit. Click Yes to continue with the change. To modify the Field Size property in Design view, select the field and then enter the new field size value in the Field Size box in the Field Properties pane. If you change the property in Design view, be sure to save the table. tips & tricks You cannot limit the size of Long Text fields. tell me more If the selected field is a Long Text field, the Memo Settings button controls two properties exclusive to Long Text fields: Rich Text Formatting allows you to add text formatting. Append Only saves a history of changes made to the text in the field.

creating a report using the report wizard

The Report Wizard walks you step by step through the process of creating a report. The Report Wizard allows you to combine fields from more than one table or query and gives you more layout and design options than using the basic Report button from the Create tab. To create a report using the Report Wizard: On the Create tab, in the Reports group, click the Report Wizard button. The Report Wizard opens. The first step is to expand the Tables/Queries list and select the underlying table or query for your report. The Available Fields box displays all the fields from the table or query you selected. Double-click a field name to move it to the Selected Fields box or click the field name once to select it and then click the button. Click the button to add all the available fields with a single click. If you want to include fields from more than one table or query, repeat steps 2 and 3 until you have selected all the fields you want in your report. Click the Next button to go to the next step. If you selected fields from related tables, the next step in the wizard asks how you want to organize the data in the report. Select the table that contains the field you want to use as the main grouping in the report, and then click Next. You will have the opportunity to add additional grouping levels in the next step. If you selected fields from only one table or query, you will not see this step. Use grouping levels to organize the data into subgroups by the value of a specific field. Select the field you want to group by and then click the button. You can add multiple grouping levels and reorder them if necessary using the Priority up and down arrows. When you are finished selecting grouping levels, click Next. Next, specify how you want the data in each subgroup sorted. Expand the sort level list and select the field you want. You can include up to four fields to sort by. Click Next. Select the report layout, and select whether you want to print in Portrait or Landscape orientation. Click Next. Give your report a meaningful title, and choose whether to preview how the report will look when printed (Print Preview view) or to modify its design (Design view). Click Finish to save the report. tips & tricks Use Portrait orientation for reports with few columns; use Landscape orientation for reports with many columns. If you selected to open the report in Print Preview, when you close Print Preview view, the report will display in Design view. If you want to modify the report design, switch to Layout view where you can see report data in the layout. tell me more You can modify your grouping or sorting choices later by editing the report in Layout view. For more information, refer to the skill Grouping Records in a Report. If the report does not look exactly the way you want, you can resize and move controls. Refer to the skills Resizing Controls and Moving and Arranging Controls.

adding design elements to form and report headers

The form header or report header is the section directly above the detail section where the data are displayed. Access allows you to add three common design elements to the header quickly and easily: a logo or other small image, a title, and the current date and/or time. To add a logo or other small image to the header: On the Form Layout Tools Design tab or the Report Layout Tools Design tab, in the Header/Footer group, click the Logo button. In the Insert Picture dialog, browse to find the image you want to use as the logo, select the file, and then click the Open button. The image is added to the upper left corner of the header. To add a title to the header: On the Form Layout Tools Design tab or the Report Layout Tools Design tab, in the Header/Footer group, click the Title button. An unbound text control with the name of the database object is added to the header, just to the right of the logo (if there is one). To change the title, click in the box and modify the text. To add the date and/or time to the header: On the Form Layout Tools Design tab or the Report Layout Tools Design tab, in the Header/Footer group, click the Date and Time button. The Date and Time dialog opens. Check the boxes for the date and/or time formats you want. Click OK to add the date and time options you selected to the upper right corner of the header. tell me more While these features are available from both Layout view and Design view, it is much easier to work in Layout view because you will see a live preview of the formatted header. In Design view, you will not see the actual date and time in the date and time controls. Instead, Design view displays the formulas used in the unbound text controls to calculate the current date and time: =Date( ) and =Time( ).

using and in a query

Use and in your criteria construction to make a query more specific by limiting query results to records that meet multiple criteria. To find records that meet two or more conditions in different fields, enter each of the criteria in the Criteria row under the appropriate field. When you enter multiple criteria in the Criteria row, the query will return only those records that meet the first criterion and the second criterion. To find all appointments scheduled for April 1, 2017 or later in the Salon, enter >=4/1/2017 in the Criteria row under ApptDate and "Salon" in the Criteria row under Location. The query in Figures AC 4.24 and AC 4.25 returns records where both of these conditions are met: appointments on April 1, 2017 or later AND the service location is Salon. To find records that meet more than one condition in the same field, enter both criteria separated by the word And in the Criteria row. To find all appointments scheduled from April 1, 2017 to April 7, 2017, enter >=4/1/2017 And <=4/7/2017 in the Criteria row under ApptDate. Hint: If you need more room to enter the criteria, you can click and drag the column header to make the column wider in the query Design grid. The query in Figures AC 4.26 and AC 4.27 returns records where both of these conditions are met: appointments on April 1, 2017 or later and appointments on April 7, 2017 or earlier. tips & tricks If you need more room to enter criteria, press Shift + F2 to open the Zoom window. When you are finished working in the Zoom window, click OK to return to the query Design grid. tell me more When you create a query in Design view, you are generating programming code in a language called Structured Query Language (or SQL). To view the SQL behind the query, switch to SQL view. On the Query Tools Design tab, in the Results group, click the View button and select SQL View.

using or in a query

Use or in your criteria construction to make a query broader by expanding the query results to records that meet any one of multiple criteria. To find records that meet any of the conditions, enter the criteria on separate rows in the query grid: Enter the first criterion in the Criteria row. Enter the second criterion in the or row (the row immediately below the Criteria row). To find all appointments for customers with the last name Smith or Clauson, enter Swanson in the Criteria row for the LastName field, and then enter Clauson in the or row for the LastName field as shown in Figure AC 4.28. Criteria do not need to be in the same field. When using an or construction with multiple fields, make sure that each criterion is on its own row. The query shown in Figure AC 4.30 will return records where the value of the LastName field is Swanson or the value of the Location field is Salon. tell me more You can add more or criteria by continuing to add criteria to the rows under the first or row.

creating relationships

When a lookup field in one table references values in a field in another table, Access will automatically create a one-to-many relationship between the tables for you. In other cases, you may need to manually create a relationship between two tables. Enforcing referential integrity ensures that related database records remain accurate. If a relationship has Enforce Referential Integrity checked, then the tables will conform to the following rules: You cannot add a record to the secondary table without an associated record in the primary table. You cannot make changes to the primary table that would cause records in the secondary table to become unmatched. You cannot delete records from the primary table if there are related records in the secondary table. To create a new table relationship and enforce referential integrity between the two tables: Open the Relationships window. On the Database Tools tab, in the Relationships group, click the Relationships button. To create a new relationship, click the primary key field name in the primary table and drag to the related field name in the secondary table. Review the relationship in the Edit Relationships dialog. Note the type of relationship. Click the Enforce Referential Integrity check box. Click the Create button. Notice the change to the relationship line. The 1 indicates the "one" table in the one-to-many relationship. The infinity symbol indicates the "many" table. When these symbols appear, you know that the relationship has referential integrity enforced tell me more To delete the relationship between two tables, right-click the relationship line, and select Delete. another method To open the Edit Relationships dialog, you can also: Double-click any field name showing in the Relationships window. Double-click any empty area of the Relationships window. When you open the Edit Relationships dialog with either of these methods, you will need to select the primary table from the Table/Query list at the left side of the dialog. When you make a selection, Access will populate the dialog with the existing relationships.

moving fields in datasheet view

When you add new fields in Design view it can be tricky to remember whether the field will be added to the right or left of the active field. If you add a field to the wrong place in the datasheet, you can always move it. To move a field in Datasheet view: Select the column by moving the mouse pointer over the column header and clicking when the mouse pointer changes to an arrow . Access highlights the entire column to indicate that it is selected. Hold down the mouse button and drag to the new position in the datasheet. As you drag, the mouse pointer changes to the move shape and Access displays a thick black line to show you where the column will be placed. Release the mouse button to finish moving the field. tips & tricks It is important to understand that you are rearranging the fields in Datasheet view only. The underlying table design has not been changed. You can learn more in the skill Inserting, Deleting, and Moving Fields in Design View.

renaming fields

When you create a new, blank table in Datasheet view, Access names the fields Field1, Field2, and so forth by default. Change the field name by modifying the field properties: Click anywhere in the field to select it. On the Table Tools Fields tab, in the Properties group, click the Name & Caption button to open the Enter Field Properties dialog. In the Name box, type the new field name. This is the name as it will be referenced by other objects in your database. In the Caption box, type the field name as it should appear in labels and column headings. If you do not include a caption, Access will use the field name instead. In the Description box, type additional information about the field (if necessary). Click OK. another method Right-click the field name at the top of the column in Datasheet view and select Rename Field. When the field name appears highlighted, type the new name, and press Enter. Double-click the column header to highlight the field name. Type the new field name and then press Enter.

deleting fields in datasheet view

When you delete a field, you delete all the data in that field, and the action cannot be undone. To delete a field in Datasheet view: Click anywhere in the field you want to delete. On the Table Tools Fields tab, in the Add & Delete group, click the Delete button. When Access asks if you if you want to permanently delete the field, click Yes. another method In Datasheet view, right-click the field name at the top of the column and select Delete Field. In Datasheet view, select the field by clicking the column heading, and then on the Home tab, in the Records group, click the Delete button.

adding fields in datasheet view

You can always add a new field to a table by typing data in the blank cell at the end of a record, but what if you want to set up the table without entering data? To add a new field: On the Table Tools Fields tab, in the Add & Delete group, click the button for the data type you want to add. If there isn't a button for the data type you want, click the More Fields button to expand the Data Type gallery and make a selection from the gallery. Access adds the new field to the right of the active field. Type the new field name and then press Enter. Note that when you use the buttons in the Add & Delete group, the field is inserted to the right of whatever field was active. To add the new field at the end of the table, click the arrow in the Click to Add column at the far right side of the table and select the field type you want to add. another method You can also insert a new field by right-clicking any field name and selecting Insert Field. A new blank text field is inserted to the left of the field you selected.

combining and and or in a query

You can combine the and and or query constructions to create a very precise query. Enter the first set of criteria in the Criteria row. Enter the second set of criteria in the or row (the row immediately below the Criteria row). The query results will include records that meet all the criteria in the Criteria row or all the criteria in the or row. The query in Figure AC 4.32 will return records with appointments between April 1, 2017 and April 7, 2017 for customers with the last name Swanson or the location Salon. Because the date range criterion is the same for both or criteria, it must be repeated in both rows.

creating a query in design view

You can create a new query from scratch using Design view. As shown in Figure AC 4.7, the query Design window has two parts. The upper pane shows the tables referenced in the query. The lower pane shows the query grid where you specify which fields to include in the query. To create a select query in Design view: On the Create tab, in the Queries group, click the Query Design button. Query Design view opens with the Show Table dialog active. Double-click the name of each table you want to include in the query (or click the table name once, and then click the Add button). Click the Close button when you have added the tables you want. You must close the Show Table dialog before continuing to build the query. A complete field list for each table appears in the upper pane of the query Design window. When a relationship exists between two tables, a line connects the related fields. You may find it necessary to adjust the layout of the query Design window. Adjust the relative size of the two panes in the query Design window by clicking and dragging the horizontal border just above the query grid. Adjust the size of a table box to see more fields by clicking and dragging the bottom boundary of the box downward. Rearrange the table boxes in the upper pane of the query Design window by clicking the box header and dragging the box to a new location (similar to rearranging tables in the Relationships window). Remove a table from the query Design window, by right-clicking the table box header and selecting Remove Table. Add fields to the query using one of these methods: Double-click the field name in the table box. Click the field name and drag it to the design grid. Click in an empty cell in the Field row of the design grid, expand the list of available fields by clicking the arrow, and click the field name you want. To rearrange the order of fields in the query, move the mouse pointer to the top of the field column in the grid. When the mouse pointer changes to the shape, click to select the entire column, and then click and drag the column to the new position in the grid. To remove a field from the query grid, move the mouse pointer to the top of the field column in the grid. When the mouse pointer changes to the shape, click to select the entire column, and then press Delete. When you have added all the fields you want, run the query by clicking the Run button near the left side of the Ribbon (on the Query Tools Design tab, in the Results group). Press Ctrl + S to save the query. In the Save As dialog, enter a name for the query in the Query Name box. Click OK. tips & tricks If you want to include all the fields from a table in your query, click and drag the * symbol from the top of the table box to the field row. Notice that rather than listing each field from the table separately, there is only one field called table.* (where "table" is the name of the table). The * symbol represents a wildcard. Rather than look for specific field names, the query will look for all the fields in that table. So, if you later add fields to or delete fields from the table, you won't need to change the query design. tell me more To display the Show Table dialog again to add other tables to the query, on the Query Tools Design tab, in the Query Setup group, click the Show Table button. another method You can also view the results of a query by switching to Datasheet view using one of these methods: On the Query Tools Design tab, in the Results group, click the View button. Click the View button arrow, and select Datasheet View. Right-click the query tab, and select Datasheet View. Click the Datasheet View button in the status bar.

exporting data to excel

You can export Access data to a variety of other applications including Microsoft Excel. This is helpful if you want to share the data with someone who may not have Access or may not need to see the entire database. To export data to Excel: Open the database object or select it in the Navigation Pane. On the External Data tab, in the Export group, click the Excel button. In the Export - Excel Spreadsheet dialog, Access automatically suggests a file name based on the name of the object you selected. If you want to change the file name or the location of the saved file, click the Browse... button and make your changes in the File Save dialog. When exporting to Excel, be sure to check the Export data with formatting and layout check box to maintain data formats such as dates and currency styles. When the Export data with formatting and layout. option is checked, you also have the option to check the box to Open the destination file after the export operation is complete. If you have a table, query, or form open with specific records selected, you can elect to export only those records. Click the Export only the selected records. check box. Click OK to begin the export process. After the export is complete, you have the option to save the export steps so you can easily run the same export again later. Check the Save export steps check box. If you save your export specifications, you can run the export again later by clicking the Saved Exports button on the External Data tab, Export group. Click the Close button to close the Export - Excel Spreadsheet dialog. tips & tricks If you are exporting a report, the Export data with formatting and layout. check box is checked automatically. You do not have the option to uncheck it. another method In the Navigation Pane, right‐click the database object you want to export, point to Export, and select the export format you want to use.

adding text criteria to a query

You can refine a select query in Design view so it shows only records that meet specific criteria. Criteria are conditions that the records must meet in order to be included in the query results. Each field data type takes a certain type of criterion. Text criteria are used for text and hyperlink fields. To add text criteria to your query: Open the query in Design view. In the Criteria row, enter the text you want to match in the column for the appropriate field. For example, to include only records where the state is Colorado, enter the text criterion "CO" in the Criteria row under the State field. If you do not include the quotation marks when you enter text in the Criteria row, Access will place the text in quotation marks for you. Run the query to see the results. When the query is limited to records where the value in the State field is CO, there are 15 records in the results as shown in Figure AC 4.15. You can use the wildcard characters asterisk (*) and question mark (?) and the "like" construction in the query criteria to find inexact text matches. The * wildcard replaces any string of characters. The ? wildcard replaces a single character. To include all records where the data in the State field begins with the letter C followed by one unknown letter, enter the criterion Like "C?". The query in Figure AC 4.16 will return records for CA and CO. (There are no customers from CT in this database.) When the query criterion is expanded to include any record where the value in the State field is any two letter abbreviation beginning with C, there are 172 records in the results as shown in Figure AC 4.17. Use the * wildcard instead of the ? wildcard if you're not sure of the exact number of characters you're looking for. The query in Figure AC 4.18 will return records where the data in the State field begins with the letter C—including CA, California, CO, and Colorado. When the query criterion is expanded further to include any record where the value in the State field is any value beginning with C, there are 184 records in the results as shown in Figure AC 4.19. tips & tricks When you begin typing in the criteria cell, Access may display a list of possible functions you could use if you were building an expression. This can be annoying if you're trying to enter text criteria instead. To prevent this, type the text criteria within quotation marks. tell me more Wildcard characters are not limited to the beginning of a text string. You can place them at the end or at both the beginning and end of the text. The criteria construction Like "*Spa" will find all records with data that end with the letters "Spa". The criteria construction Like "*Spa*" will find all records with the letters "Spa" anywhere within the data. The criteria construction Not Like "*Spa*" will find all records with data that do not include "Spa" anywhere within the text string.