MS ACCESS Database Management System DBMS The computer

MS ACCESS

Database Management System (DBMS): The computer Software to manage, maintain database as well as view update and retrieve data is called database management system. Microsoft Access is a Database Management System (DBMS), designed primarily for home or small business usage. Access is an object-oriented relational database management system. The database objects in Access are: • A Table. It uses rows and columns to present the data in the database— rather like a spreadsheet. For this reason, when you view a table in an Access database, you are in what Access calls a Datasheet View. • A Query. It makes a request to the database, asking it to find for you some data that are stored in the database. • A Form. It gives the user another way of looking at the data in the database. Whereas a Table allows the user to view many records at once, a Form displays the contents of just one record at a time. • A Report is used to present a selected set of contents from the database in a format that is easy to mentally digest.

Microsoft Office Button When you create or open a database, it opens in a database window.

All about Tables • A Table is an arrangement of data in a row and column. • A field is a column in a table that contains particular information about a record. • A Primary Key is a field uniquely identifies every record in a table. • A Record is a set of information stored about a particular entry.

All about Tables • Opening up MS Access and creating a table.

Table Design Toolbar Creating Fields Defining data types

DATA TYPES: 1. Text data type is used to store all valid printable characters. 2. Memo data type is used to create a text field in which size of the values can vary widely. 3. Number data type enables you to enter numeric data. 4. Currency data type enables you to enter monetary data. 5. Date/Time data type can store date and time values. 6. Yes/No data type, also known as a logical type, stores data that can have only two values.

Field Properties Pane 1. Field size determines the amount of data that can be stored in a field.

2. Default Value is one that is displayed automatically for the field when you add a new record to the table. 3. Validation rule is a field property that sets limits or conditions on the data that can be entered in the field. 4. Validation text is a custom message that is displayed if invalid data is entered in a field or control where a validation rule exists. 5. Decimal places specifies the number of decimal places to use when displaying numbers.

6. Required enables you to enter a Yes value for Required if a field should always receive a value during data entry.

Forms • Forms are used to customize formats for adding, editing, deleting or displaying data.

Create a single item form: 1. In the Navigation Pane, click the table or query that contains the data that you want to see on the form. 2. On the Create tab, in the Forms group, click Form. Access creates the form and displays it in Layout view. In Layout view, you can make design changes to the form while it is displaying data. 3. To begin working with the form, switch to Form view. On the Home tab, in the Views group, click View, and then click Form View .

Reports • A report is a flexible way of viewing and printing summary information. It enables you to display information to the required level of detail.

Creating a Report: 1. 2. 3. 4. Open the Navigation pane. Click the table or query on which you want to base your report. Activate the Create tab. Click the Report button in the Reports group. Access creates your report and displays your report in Layout view. You can modify the report.

Queries • The process of accessing the database and retrieving data selectively is known as querying. The data thus retrieved can then be formatted according to user’s requirements. • Datasheet view of Query.

• Design view of Query.

To open tables or queries in Query Design view: 1. Activate the Create tab. 2. Click the Query Design button in the Other group. The Show Table dialog box appears. 3. Activate the Tables tab if you want to base your query on tables, activate the Queries tab if you want base your query on queries or activate the Both tab if you want to base your query on both tables and queries. 4. Click to choose the table or query on which you want to base your query. 5. Click Add. The table appears in the window. 6. Click Close. Access changes to Query Design view. 7. Use the options in Design view to create your query. You then click the Run button to display the results. You can save queries for later use.

Create a parameter query Creating a parameter is similar to adding a normal criterion to a query: 1. Create a select query, and then open the query in Design view. 2. In the Criteria row of the field you want to apply a parameter to, enter the text that you want to display in the parameter box, enclosed in square brackets. For example, [Enter the start date: ]

3. Repeat step 2 for each field you want to add parameters to. When you run the query, the prompt appears without the square brackets. 4. Fill in the value you’re looking for, and then click OK.

Import the data 1. 2. On the External Data tab, in the Import group, click Access. The Get External Data - Access Database import and link wizard opens.

3) In the File name text box, type the name of the source database or click Browse to display the File Open dialog box. 4) Select Import tables, queries, forms, reports, macros, and modules into the current database and click OK. The Import Objects dialog box opens. 5. In the Import Objects dialog box, on the Tables tab, select the tables you want to import. 6. Click OK to finish the operation.

Sorting In Datasheet View, place the cursor in the column that you want to sort by. From the Home group, go to Sort & Filter and click the Sort Ascending or Sort Descending buttons on the Table Datasheet toolbar. Deleting Records Delete a record on a datasheet by placing the cursor in any field of the record row and select from the Home tab, under records group, select Delete.

Macros • Macros are small programs that perform a specialized task every time they are run. Ex: I want to close a form.

Create a standalone macro 1. On the Create tab, in the Other group, click Macro. The Macro Builder is displayed. 2. Add an action to the macro: a) In the Macro Builder, click the first empty cell in the Action column. b) Type the action that you want to use, or click the arrow to display the action list, and then select the action that you want to use. c) Under Action Arguments, specify arguments for the action. To see a short description of each argument, click in the argument box, and then read the description on the right side of the argument. 3. To add more actions to the macro, move to another action row, and then repeat step 2 (b). Access carries out the actions in the order in which you list them.