Basic Access Notes

Basic Access Notes

• 1.
  Microsoft Access 1.Starting andExiting 1.1.Starting Access 1.2.Exiting Access 2.Creating and Opening a Database 2.1.Creating a New Database 2.2.Opening an Existing Database 3.Working with a Table 3.1.Creating a Table 3.2.Opening a table 3.3.Switch between views of a table 3.4.Adding Fields and Choosing Data Types 3.5.Customizing Fields 3.5.1.To move a field 3.5.2.To change a field name 3.5.3.To delete a field 3.5.4.To insert the field 4.Entering and Editing Data 4.1.Add new data in Datasheet or Form view 4.2.Delete a record in Datasheet or Form view 4.3.Edit the data in a field in Datasheet or Form view 4.4.Ways to work with data in a table’s datasheet 4.4.1.Cut, Copy and Paste 4.4.2.Sorting 4.4.3.Find and Replace 5.Creating and Using Forms 5.1.Create a form 5.2.Open a form 5.3.Switch between views of a form 5.4.Working in Design View of a form 5.4.1.Adding Fields 5.4.2.Add or remove a form header and footer or a page header and footer 5.4.3.Adding controls 5.4.4.Changing the properties of form or controls 6.Working with Queries 6.1.Create a simple select query with a wizard 6.2.Create a simple select query without a wizard 6.3.Open a query 6.4.Using Criteria 6.5.Some examples of criteria expressions 6.6.Create a parameter query that prompts for criteria each time it's run 6.7.Calculate a sum, average, count, or other total on groups of records in a query 7.Creating and Using Reports 7.1.Create a report 7.2.Open a report 7.3.Switch between views of a report 7.4.Print a report
• 2.
  1.Starting and Exiting 1.1.Starting Access Click the Start button, select the Programs option and then click the Microsoft Access option. 1.2.Exiting Access Click the File menu and then click the Exit command. 2.Creating and Opening a Database 2.1.Creating a New Database -Click the Start, select the Programs and click the Microsoft Access. -Click the Blank Access Database and then click OK. If you have already opened a database or closed the dialog box that displays when Microsoft Access starts up, click New on the toolbar, and then double- click the Blank Database icon on the General tab. -Specify a name and location for the database and then click the Create button.
• 3.
  2.2.Opening an Existing Database -Clickthe Start, select the Programs and click the Microsoft Access. -Click the Existing Database and select the file and then click OK. (If you have already opened a database) -On the File menu, click Open. -Find the database you want to open and click Open. 3.Working with a Table 3.1.Creating a Table -Click the Tables under Objects, and then click New on the Database window toolbar. -Double-click Design View.(or) Click Design View and click OK. - Define each of the fields in your table. - Click Save on the toolbar, and type a name for the table and click OK.
• 4.
  3.2.Opening a table -In the Database window, click Tables under Objects. -Click the name of the table you want to open. To open the table in Design view, click Design on the Database window toolbar. To open the table in Datasheet view, click Open on the Database window toolbar. 3.3.Switch between views of a table Click the View button on the toolbar. 3.4.Adding Fields and Choosing Data Types -Open the table in Design view. -Click in the Field Name column and type the name for the field. -Click in the Data Type column, click the arrow, and select the data type you want. -In the Description column, type a description of the information this field will contain. The description is optional. -If you want, set field properties for the field in the bottom part of the window. 3.5.Customizing Fields 3.5.1.To move a field -Select the field(s) you want to move. To select one field, click that field's row selector. To select a group of fields, drag through the row selectors of those fields. -Click and hold down the mouse button in the row selector again. Microsoft Access displays a thin horizontal bar just above the last selected row. -Drag the horizontal bar to the row just below where you want to move the fields. Note: Changing field order in table Design view changes the order in which fields are stored in the table and also changes the column order in the table's datasheet. 3.5.2.To insert the field -Click in the row below where you want to add the field and -Click Insert Rows on the toolbar. 3.5.3.To delete a field -Select the field(s) you want to delete. -Click Delete Row on the toolbar. 3.5.4.To change a field name -Double-click the field name you want to change. -Type the new field name. -Click Save on the toolbar to save your changes.
• 5.
  4.Entering and EditingData 4.1.Add new data in Datasheet or Form view -Open a Datasheet, or form in Form view. -Click New Record on the toolbar. -Type the data you want, and then press TAB to go to the next field. 4.2.Delete a record in Datasheet or Form view -Open a Datasheet, or form in Form view. -Click the record you want to delete. -Click Delete Record on the toolbar. 4.3.Edit the data in a field in Datasheet or Form view -Open a Datasheet, or form in Form view. -To edit data within a field, click in the field you want to edit. -Type the text you want to insert. Sort Ascending Add Record Cut, Copy, Paste Sort Descending Find Delete Record 4.4.Ways to work with data in a table’s datasheet 4.4.1.Cut, Copy and Paste Click Cut, Copy and Paste on the toolbar to edit selected text, fields, whole records, or the entire datasheet. 4.4.2.Sorting Click the field you want to sort by, and then click Sort Ascending or Sort Descending on the toolbar. 4.4.3.Find and Replace Click Find on the toolbar to find a value in a field in every record in which it occurs. Click Replace on the Edit menu to replace the value that you find.
• 6.
  5.Creating and UsingForms 5.1.Create a form -In the Database Window, click Forms under Objects. -Click the New button on the Database window toolbar. -In the New Form dialog box, Click Design View (or) Click Form Wizard (or) Click AutoForm the wizard that you want to use. -Click the table or query that contains the data you want to base your form on. -Click OK. 5.2.Open a form -In the Database Window, click Forms under Objects. -Click the form you want to open. -Click the Open button to open the form in Form view, or the Design button to open the form in Design view. 5.3.Switch between views of a form Click the View button on the toolbar.
• 7.
  5.4.Working in DesignView of a form Field list 5.4.1.Adding Fields Adding controls -Click the Field List button on -Click the Toolbox button the toolbar to display a floating on the toolbar. list box with the fields. Toolbox Properties -Click the control (such as -Click and drag the required text boxes, labels, check field from the Field List box boxes, etc.) and draw on the to the Detail area of the form. form. 5.4.2.Add or remove 5.4.3.Changing a form header and footer the properties or a page header and footer of form or -Open the form in Design view. controls -Click Form Header/Footer or -Click the Page Header/Footer Properties on the View menu. button on the toolbar. To select the form click the form selector To select the required control click it. 6.Working with Queries 6.1.Create a simple select query with a wizard -In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
• 8.
  -In the NewQuery dialog box, click Simple Query Wizard. -Click OK. -Click the name of the table or query you want to base your query on, and then select the fields whose data you want to retrieve. -Follow the directions in the wizard dialog boxes. In the last dialog box, you can choose either to run the query or to see the query's structure in Design i 6.2.Create a select query without a wizard -In the Database window, click Queries under Objects, and then click New on the Database window toolbar. -In the New Query dialog box, click Design View. -Click OK. -Select the Tables/Queries/Both tab on the Show Tables dialog box. (If the Show Tables dialog box is not already opened, click Show Table on the toolbar) -Select the required table or query and click Add and then click Close. -From the field list box, double-click the required field. -Click Save button on the toolbar. -In the Save As dialog box, type the name and click OK 6.3.Open a query -In the Database window, click Queries under Objects -Click the query you want to open, and then click Design/Open on the Database window toolbar. Switch between views of a query Click the View button on the toolbar.
• 9.
  View Save Total 6.4.Using Criteria -Open a query in Design view. -Click the Criteria cell for the field you want to set criteria for. -Enter the criteria expression by typing it or by using the Expression Builder. (To display the Expression Builder, right-click in the Criteria cell, and then click Build.) 6.5.Some examples of criteria expressions Field Expression Description StaffName "Mg Mg" Staff name is Mg Mg StaffName Like "S*" Staff name starts with the letter S. Sex “m” For male staff Dob Between #1/5/70# And Uses the Between...And operator to display #1/10/75# staff no earlier than 5-Jan-70 and no later than 10-Jan-75. Dept "che" Or "phy" Uses the Or operator to display Department to phy or che. Salary >=7500 Salary is greater then 7500 6.6.Create a parameter query that prompts for criteria each time it's run -Open a select query in Design view. -In the Criteria cell for each field you want to use as a parameter, type a prompt enclosed in square brackets. E.g >=[Type the Salary] LIKE [Enter the first character to search by: ] & "*" (Microsoft Access will display this prompt when the query is run.) -To view the results, click View on the toolbar, and then type a value for the parameter. 6.7.Calculate a sum, average, count, or other total on groups of records in a query -Open a select query in Design view. -Click Totals on the toolbar. (Microsoft Access displays the Total row in the design grid.) -For the field or fields you want to group on, leave Group By in the Total cell. -For each field you want to calculate, click its cell in the Total row, and then click one of the following aggregate functions: Sum, Avg, Min, Max, Count. -If you want, enter criteria to affect the results of the calculation. -If you want, sort the results. -Click View on the toolbar to view the results.
• 10.
  7.Creating and UsingReports 7.1.Create a report -In the Database Window, click Reports under Objects. -Click the New button on the Database window toolbar. -In the New Report dialog box, Click Design View (or) click Report Wizard (or) click AutoForm the wizard that you want to use. -Click the table or query that contains the data you want to base your report on. -Click OK. 7.2.Open a report -In the Database Window, click Reports under Objects. -Click the report you want to open. -Click the Design button to open the report in Design view, or click the Preview button to open the report in Print Preview. (You can use the navigation buttons at the bottom of the Print Preview window to move between pages.) 7.3.Switch between views of a report Click the View button on the toolbar. 7.4.Print a report -Select the report in the Database window, or open the report in Design view or Print Preview. -On the File menu, click Print. -Enter the settings you want in the Print dialog box. -Click OK. (Tip To print a report without bringing up the dialog box, click Print on the toolbar.)