Pdf of ms access tutorial

To achieve this, pull down the list box next to the number 1: and choose the CustomerID field as shown in the figure below. This is similar to how a spreadsheet would display the data. Generally, reports use the tabular layout.

For this example, choose Tabular layout and set the page Orientation to Landscape so that all of the fields will fit across one page. This is shown in the figure below. Click on the Next button to continue. For this example, choose the Office style and click on the Next button to continue.

Finally, give a name for the new report: CustomerReport and then click on the Finish button to create, save and display the new report. Note that on some screens, the last field, Zip, may not display without scrolling over to the right.

Once the report is displayed, it can be viewed, printed or transferred into Microsoft Word or Microsoft Excel. Exercise: Creating a Single Table Report For this exercise, we will create a report showing all of the Accounts information. From the Access main screen, click on the Create tab and Click on the Report wizard.

Select all of the fields in the Accounts table by moving them all over to the Selected Fields side and then click Next 3. This is shown in the following figure: Click on the Next button to continue. Choose to sort the report on the AccountNumber field. Note that a new button will appear called Summary Options.

Choose the Balance field and select the Sum option. Choose the option to show both Detail and Summary data. Then click on the OK button. Click on the Next button. Choose a Block layout and click on the Next button. Choose the Corporate style and the click on the Next button. Finally, name the report: AccountsReport and click on the Finish button to create, save and run the report.

To close the report and return to the Access main screen, pull down the File menu and choose Close. Review of Creating and Running a Report As can be seen in the report exercise, there are many ways to create reports to show summation, sorting and layout of the data. Further study of Reports will show how to modify the layout using the Design View. Students are encouraged to work with the Report wizards to create different styles and types of reports. The forms are linked according to the relationship between the tables.

Click the Next… button to move to the next step. At this point MS Access detects that this form will involve data from two different tables. The second prompt asks how the forms should be created. The Subform is the form that will display the detailed data from the Accounts table. For Access , select the Office style and click the Next… button to move to the next step.

This step will not appear in MS Access or A switchboard is typically created after all of the forms and reports for a database application have been completed.

It can be used to guide the user to an appropriate set of forms and reports. Note that starting with Access , Switchboards are not available by default you will need to add this to the ribbon bar manually using the Options.

Access and make use of the Navigation Forms. In this section, a default switchboard will be created. From the Access main screen, click on the Database Tools tab and Click on the Switchboard Manager as shown in the figure below. If this is the first switchboard made for this database, you will be prompted to create a new one as shown below. Click the Yes button. The Edit Switchboard Page will appear as shown below: 4. Add a new switchboard item by clicking on the New… button.

The Edit Switchboard Item form will appear as shown below. Click on the OK button to save this new Switchboard item. Repeat the above step two more times to add Switchboard items for the Accounts DataEntry form and the Customer Report. Once completed, click the Close button. Then click the Close button once more to close the Switchboard manager.

From the main MS Access screen look for a new section labeled Switchboard items. Navigation forms take the place of Switchboards but provide much the same functionality. Namely, they are designed to give the user the ability to run forms and reports without having to hunt through all of the different menus and lists objects.

Typically a database application will have one main Navigation form that will appear when the database is opened. In this section, the basic steps for creating and running a Navigation Form in MS Access will be demonstrated. To get started, click on the Create tab on the Access or ribbon bar.

Under the section for Forms look for the item labeled Navigation Form. Note that this may be located on the button labeled Other Forms. Note that there are 6 different default styles of Navigation Forms. Each one places the buttons in a different configuration on the screem. Buttons can be aligned across the top, either side or some combination. For this tutorial select the Horizontal Tabs configuration which appears as the first item on the list. At this point a new Navigation Form will be created with a row of tabs across the top.

The first tab will be labeled [Add New] 4. To add items to the Navigation form, drag the items from the list on the left over to the spot on the Navigation Form labeled [Add New]. This is shown by the arrow in the above figure. The result is shown below. Next drag and drop the CustomerMasterForm as shown below. Next drag and drop the CustomerReport as shown below.

At this point we have created a new navigation Form and added three items along the top of the page. Save the Navigation Form by right-clicking on the name of the form and choosing Save as shown below: 8. Close up the Navigation Form by right-clicking again on the name of the form and choosing Close menu item.

At this point the new Navigation Form has been created and saved. The next step will be to view the Navigation Form and navigate the different forms and reports linked to it. To view the Navigation Form, look for the Unrelated Objects group on the left side of the screen.

This is especially useful for end users so that they always see the same form when they open up the database. To set the Default Form, pull down the File menu and select the Options menu item. Click on the Current Database item on the left hand side. The next time this database bankdb. So as much as possible we want to try and suggest good data to be put into the tables and to reject any obviously bad data. Once bad data makes its way into the database it is often difficult to correct.

Records that are missing data are also a problem. So again it will always help the user any time we can provide a default value or provide good suggestions of what data to put in. Below are some additional properties that can be customized to provide better data input quality.

In general these techniques should be used at the time tables are created. MS Access will use these properties when creating data entry forms and reports. When adding a new data record it is helpful to supply as many default values as is reasonable so the user does not have to type in as much data. To set a default value, highlight the name of a column field in the table Design View and then set the Default Valueproperty accordingly.

In the example below the Default value for Balance has been set to 0: Also consider putting the Required property into play to ensure the user will put something in the field. In the above example the Required property is set to No. Setting this to Yes will force the user to enter data. Validation Rules put limits around the values that are allowed to be stored in a column field. Rules can be set up compare the data a user enters and then either accept or reject that value.

In case the data is rejected by the rule, a message from the Validation Text property will be displayed. In this example, the Balance column field will be validated to make sure it can never be a negative number. Rather than have the user type in these values, we can change the default text box display of a field to a Combo Box.

A Combo Box looks like a text box but it has a small arrow on the right hand side. Clicking on the arrow presents a list of possible values. For this example we will modify the Customer table and provide a Combo Box with a list of suggested values for the State column field. Change the Row Source property to: Value List. This setting allows us to type in a list of suggested values separated by semicolons. Type in the value list in the Row Source property.

Leave the rest of the properties as the default values. Make sure the Limit to list property is set to No. This way if a Customer comes in from another state not on the list, the user can type it in. At this point the properties look like: 7.

Save the current table design and then close up the Design View. In some cases the values that can be supplied for a field can come from another table or some external data source. So rather than typing in a static list of possible values, the Row Source property can be set to a Query.

A common situation occurs when we need to supply the value for a foreign key. The CustomerID column is the key of the Customer table. When it appears in the Accounts table, the CustomerID column is a foreign key. When supplying a value for the CustomerID column in the Accounts table, we are restricted to using only existing CustomerID values that exist already in the Customer table.

Close any open tables or forms and open the Accounts table in Design View. For the Row Source property pull down the list and select the Customer table. Change the Bound Column property to 1 7. Change the Column Count property to 3The above three property changes tell Access to do the following: When the user clicks on the Combo Box, query the Customers table.

Change the Column Widths property to 0. Change the Limit to List property to Yes. We many only use CustomerIDs that exist in the Customer table. At this point the properties for the CustomerID column in the Accounts table will look like the following: Save the design of the Accounts table and close it. Note that the Row Source property can be set to any existing table, query or even a SQL statement discussed later on.

Columns designated as Keys are a special case. Because of their function as a unique identifier, each value of key needs to be unique. For small databases we may be able to count on the user typing in a new unique number or text string for the key column each time a new record is created.

However for larger databases and especially in cases where the database is shared among many users, we need a way to guarantee unique key values are generated for each new record. MS Access provides a facility to deliver unique values by using the AutoNumber data type. Specifying a column field data type as AutoNumber will accomplish the following things: 1.

The data type will be an integer number. The first data record entered will be given number 1 and each new record afterwards will automatically be given the next number in sequence.

The user will not be able to click inside the AutoNumber field or change its values. Unfortunately once a table has been created and data added, a field can not easily be changed from a Number or Text to AutoNumber. So for this next exercise, we will create a new table for the Bank database and will make use of the AutoNumber data type. The Bank would now like to keep track of each transaction that occurs in an account.

For example, money can be deposited or withdrawn from the account. The bank will need to track the AccountNumber, the TransactionType, the TransactionAmount and the date and time of the transaction.

Start by closing up all of the existing tables. Click on the Create tab on the ribbon bar and then click on the Table icon. Right-click on the new table that has been created and select Design View. Save the new Transactions table and close the table Design View. Open up the Relationship Tools screen from the Database Tools tab on the ribbon bar.

Add the Transactions table to the Relationships design. Create a relationship from the AccountNumber column in the Accounts table to the AccountNumber column in the Transactions table.

The relationship should be One to Many and enforce referential integrity: 9. Save and close up the Relationship tools. We will make use of this data in the next part of the advanced Access tutorial. Make the default value for DateOpened column in Accounts table equal to the current date. So as much as possible we want to try and suggest good data to be put into the form fields, provide default values wherever possible, and apply some common-sense checks to make sure bad data is not entered.

Before we get into these details, we will start with a more complete overview of data entry forms. We develop applications forms, reports, menus, etc. An individual form focuses the attention of the user to one or a few of the tables at a time. In addition, a form can give prompts so the user knows what kind of input is expected and how data is to be entered and manipulated. By default, every form in Access has the capability to query existing data in a table, modify existing data and add new data records to the table.

Fields within a form correspond to columns in the database tables. There are four main types of forms that can be designed. Single Table Form. This form design contains a single Single Table Form with lookup field. This form design form corresponding to a single database table. This is contains a single form corresponding to a single the most basic type of form. First we will dig a bit deeper into the forms designer and the properties of forms.

While it is possible to create a form from scratch just by using the Design View, usually we can get a good start on a form by running through the Form Wizard. Then we can customize the form by playing around with the form properties in Design View.

For this exercise, we will create a data entry form for the Accounts table. For Access only choose the Office style and click on the Next button. Note that if you had already created this form during the prior tutorial, you will be asked to over-write the form with this new one. The Account Type field was then modified in the exercises to show a list of account types. Other table features that are also inherited from table design include the size of the text boxes related to the size of the columns , default values and column validation rules.

Form Themes in Microsoft Access and Forms and reports can be customized with a wide range of themes. A Theme consists of a set of background and foreground colors and fonts that are applied to the forms. MS Access and earlier versions had a small selection of themes that could be selected during the Form Wizard.

Access and later versions create the form first and then apply themes later on. To change the theme of the current form, click on the Home tab on the ribbon bar, then click on the View menu and finally Design View.

Add buttons, fields, labels and other objects to the form. The Design tab is shown below: To change the theme of the form, click on the Design tab on the ribbon bar and pull down the Theme menu.

In a similar fashion the color scheme of the form can be changed using the Colors menu and the font used for the labels and text boxes can be set using the fonts menu. Each label, text box, combo box and other object on a form also have their own set of properties.

To view the Form level properties, right-click on the form in Design View and select Properties from the pop-up menu. If the Selection Type does not say Form then pull down the list below and select Form from the list. Access has already set all of these properties when the form was created by the Forms Wizard. So by default there may not be a reason at this point to change any of the properties. However in later steps we may come back to this property page to make some changes.

For now, a few of the more important and useful properties to be aware of are: Property Description Record Source The source of data for the form. This source can be a query but most of the time it will be a table.

Recordset The type of Recordset object created from the record source. In virtually all cases this Type should be set to Dynaset since a Dynaset maintains consistency with the underlying table. A snapshot just shows the data at the time the form is opened so no data changes are reflected until the form is re-opened or refreshed. Fetch Defaults Yes indicates default values set in the underlying table will be populated in the fields when a new record is created.

Filter Sets any filtering criteria for when the form is opened. By default no filters are applied but can be added by the user once the form is displayed. Filter on load If a filter is set, Yes indicates the filter will be applied as the form is loading. Order By Specifies the order of the records as they are displayed in the form.

Data Entry Yes indicates the form can only be used to add new data records. No is the default. Allow Yes indicates this form can be used to add new records. Additions Allow Yes indicates this form can be used to delete existing records. Deletions Allow Edits Yes indicates this form can be used to edit existing records.

Allow Filters Yes indicates users can add filters to diplsay a subset of the records in the form. Record Locks Indicates how data should be locked when multiple users are working on the same data. Locks are used to prevent two users from overwriting the same data.

This is the default but may not be reasonable if you plan to share this data with multiple users. All Records indicates all records in the table are locked when a user edits one record. Edited Record indicates only the current record being edited will be locked. The Format properties tab for a form controls the appearance of the form including scrollbars, headings, default layout of fields, etc. Some handy properties include: Property Description Caption Form Caption — this is the name that appears on the top tab when the form is open.

Default View Default view when form is opened. View Allow Yes indicates the form can be shown as a data sheet. We will revisit Events in a later tutorial. In later tutorials we will revisit these form-level properties to customize how the form behaves. The Data tab for CustomerID properties is shown below: As with the form-level properties, Access sets the default properties for each field during the Forms Wizard process to create a new form.

Most of these properties can and probably should be left with their default values. However a few properties will be modified to change the behavior of the fields. Property Description Control Source The source of data for the field. The choices here are limited to the columns available in the table or query specified in the form-level Record Source property. Row Source Properties that control the source of values for the Combo Box. Note that this set of properties were set by the table design properties introduced Bound Column in the Advanced Database Table Design tutorial Input Mask Set a template for data to be input.

For example, a mask for a U. Default Value Specifies a default value for new records. This can also be set in the table level properties. Validation Sets a validation rule for data entered by the user. For example, this can be used to limit Rule and the range of values for a particular field.

Note that these are similar to the properties that Validation are set by the table design properties introduced in the Advanced Database Table Design Text tutorial Enabled Yes indicates the field is available in form view. Locked Yes indicates the data in the field is locked and can not be change.

Auto Expand Yes indicates a value will be pre-filled if the first few letters are typed in. In some cases, we may also wish to display some data from a different table — one that is not the main table the form is based on. In our bank database example, One Customer can have Many accounts. When Accounts is the table used for the form, and we want to show some Customer data that matches the account, we follow the CustomerID relationship from the Accounts table back to the Customer table and use this relationship to bring in the additional data to the form.

To help make this connect we will use a function to look up the CustomerID in the Customer table. Access supports a large number of functions that can carry out different operations including various math, accounting and finance calculations, date and time data manipulation, text formatting, data type conversion and database manipulation.

For this example we will specifically use the DLookUp function that is designed to find a specific record in a data table based on a search criteria. Pull down the Design ribbon bar and click on the Text Box icon: 2.

Macros are mini computer programming constructs. They allow you to set up commands and processes in your forms, like, searching, moving to another record, or running a formula. Microsoft Access and Excel are very similar yet very different.

Here, are some important difference points between both of them-. Note: We assume you have the latest Microsoft Access installed which comes bundled with Microsoft Office package. You will find the list of installed programs. Before we create a Database, lets quickly understand the holistic picture of what Database is, with particular reference to MS Access. Result : The below window will appear. All the Database templates are displayed below.

Step 2 We can select any template by clicking on it. Click on Contact Template for further reverence. Step 6 Optionally, you can click on any of the objects from left navigation pane and open that object for further references and work.

For, E. The first step in this Microsoft Access tutorial to store data in the database is creating a Table where data will reside. Post creation of the table, we can keep inserting the rows in the table. Step 1 First Click Create tab. Then from Tables group, click Table. Step 2 Table Dialog box appears. Fore More e-Books like this, click on Ebooks. Click here to view the book. Click Here to Download The Book.

Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.