Microsoft Access 2007 Tutorials for beginners - Microsoft Access

Microsoft Windows Forums Microsoft Offic... Microsoft Acces... Microsoft Access 2007 Tutorials for beginners

9/12/2007 11:05 PM

	soumya
2910 posts
feelwindows.blogspot.com/
Forum Guru

Microsoft Access 2007 Tutorials for beginners

Database normalization

Database normalization is the process to organize the data of a data base. It includes the creation of tables and the establishment of relations among them according to designed rules by eliminating the incoherent redundancy and dependencies.

Redundancy data waste disk space and create maintenance problems. If there is to change data that exist in more of a place, they are due to change exactly of the same form in all his locations. A change in the direction of a client is much easier to implement if the data only store in the table Clients and not in some other place of the data base.

Microsoft Access 2007 uses the relational model for the database management which is based on the logic of predicates where the data is represented by mathematical n-ary relations. Each relation is represented by the composition of fields stored in tables that represent these n-ary relations.

To transform a database in a relational database there are several normalization forms to apply:

First normal form

1) Eliminate the repeated groups of the individual tables.

2) Create an independent table for each related data set.

3) Identify each data set related with a primary key.

Second normal form

4) Create independent tables for sets of values that are applied to several records.

5) Relate these tables to an external key.

Third Normal form

6) Eliminate the fields that do not depend on the key.

Record values that are not part of the key of that record do not belong to the table. In general, whenever the content of a group of fields can be applied to more of an only registry of the table, it considers placing these fields in an independent table.

There are two additional normal forms (fourth and fifth) but both are rarely used.

Relationships in Access 2007

7) Open TutorialDB.

8) Click on Database Tools and select Relationships. Remember that the database must be normalized before.

Illustration 1: Normalize the database and create relationships between tables and data.  Click here to view larger image.

A blank screen will appear. You must add tables.

9) Right click on the empty screen and select the Show table menu item.

Illustration 2: Normalize the database and create relationships between tables and data.  Click here to view larger image.

10) Add all tables in the database.

Illustration 3: All tables must be added.  Click here to view larger image.

11) Arrange tables in the diagram. Then click and holds the AreaId field in Areas table and drag it over AreaId fields in Fields tables.

12) Release the mouse button to create the relationship.

Illustration 4: Edit relationships properties.  Click here to view larger image.

13) Edit Relationship properties and press the Create properties.

14) Repeat steps 12 and 13 to create all needed relationships.

Illustration 5: Normalized database.

Once normalized the data base, it is possible to design queries that take advantage of the relational model.

Create Queries

Queries are those that truly make the work in a data base. They can make several different functions. Its function commonest is to recover specific data of the tables. The data that wish to see usually are distributed by several tables and, thanks to the queries; you can see them in a single data sheet. In addition, since normally you do not wish to see all the records simultaneously, the queries allow you to add criteria for "filtering" the data until obtaining the whished records. The queries often serve as origin of registries for forms and reports.

Some queries are "updatable", which means that it is possible to edit the data of the table’s through query’s datasheet. If it works with an updateable query, remember that the changes also take place in the tables, no only in the query’s datasheet. There are two basic types of queries: those of selection and those of action. A selection query simply recovers the data and causes that it become available for his use. The results of the query can be seen in the screen, be printed or be copied to the paper holder. Or can be used like data source for forms or reports.

An action query, as its name indicates, makes a task with the data. The action queries can serve to create new tables, to add data to existing tables, to update data or to eliminate data.

15) Open TutorialDb.

16) Select the Create tab on Options Tape and select the Query Wizard command.

Illustration 6: Create a query.  Click here to view larger image.

17) Select the Simple Query wizard Option and press OK button.

Illustration 7: Simple query wizard.

Open a database through Microsoft Office Access button

18) Select the Files table from Table / Queries list. Then select Filename, Comments and Directory fields from the Available Fields list.

Illustration 8: Configure the query’s fields.

19) Press the NEXT button.

20) Assign a name to the query.

Illustration 9: Set the query’s name. You can see the data or modify the query design.

21) Press the FINISH button. You will see the data.

Illustration 10: Query’s results.  Click here to view larger image.

22) Now, you will create a more refined query. Select the Create option in the Options Tape, and select the Query design command.

Illustration 11: Query design view.  Click here to view larger image.

23) Double click on each table name to add it to the query designer.

Illustration 12: Query design view.  Click here to view larger image.

Notice that the relations between tables are added at time of there is added to the query designer

24) Double click on each Field on File table that you want to show. Skip the TypeId, AreaId and ThemeId fields.

25) The fields Filename, Comments and Directory will appear at bottom grid. You can deselect the show option. This option will hide the field but it will still remain in the query for other purposes (for example, you can count the distinct instances of the file name).

Illustration 13: You added the main query fields.  Click here to view larger image.

26) Now, add the additional fields (those related to the TypeId, AreaId and ThemeId keys) by clicking in AreaName from Areas table, TypeName from FileTypes table and Themename from Themestable.

Illustration 14: You added the related table’s fields.  Click here to view larger image.

27) Right click on Query1 name and select the SAVE menu item.

Illustration 15: Save the query.  Click here to view larger image.

28) Now set a name to the query and save.

Illustration 16: Set the query name.

29) Select the Run command to view the query’s results.

Illustration 17: Run the query.  Click here to view larger image.

30) Access 2007 will take each related field from related tables using the relational model data retrieval theory.

Illustration 18: Query Results.

If you try to change any related field (like AreaName, ThemeName or TypeName) all instances of these fields will be changed. For example if you change the value “SCM” at the ThemeName in the first row, all instances of this field will be changed automatically in the query and the base table (Themes) will be changed too.

Illustration 19: Changing a related field will affect all instances of this field and the base table.

31) Now you will create a crosstab query. Select the Simple Query wizard Option and press OK button.

32) Select the Crosstab Query wizard Option and press OK button.

Illustration 20: Crosstab query wizard.

33) Select the List Files table from Table / Queries list. The Both option must be selected first.

Illustration 21: You can see a design preview.

34) Press the NEXT button.

35) Select the AreaName as row to summarize and press the NEXT button.

Illustration 22: You can see a design preview.

36) Select the TypeName as column to summarize and press the NEXT button.

Illustration 23: You can see a design preview.

37) Select the Count as summarize operation and press the NEXT button.

Illustration 24: You can see a design preview.

38) Set the query name to FilesQtyByType and select the view the query Options. Press the FINISH button.

Illustration 25: Now, you can see the query results.

Illustration 26: Query results.  Click here to view larger image.

Source: MS TIPS AND TRICKS

9/12/2007 11:08 PM

	soumya
2910 posts
feelwindows.blogspot.com/
Forum Guru

Re: Microsoft Access 2007 Tutorials for beginners

Thanks to his improved interface and its capacities of design, that do not require deep knowledge on data bases, Microsoft Office Access 2007 facilitates the pursuit and the creation of information.

Illustration 1: Office Access 2007 provides a library of pre designed solutions. Click here to view larger image.

1) Pre designed solutions: The new Introduction screen includes a series of pre designed data bases. Use it as a template for your own database.

2) User interface oriented to results: Office Access 2007 has been updated with a renewed appearance. The new user interface oriented to results varies according to the context and it has been optimized so that it is more efficient and easy to use.

3) Improved exploration: The new navigation panel offers a global view of tables, forms, queries and data. You can create customized groups to organize and even to see all the forms and data related to a single table.

4) Create tables quickly: Office Access 2007 facilitates the direct work on a data sheet to create and to personalize tables.

5) Import contacts from Microsoft Office Outlook 2007: Office Access 2007 facilitates the interchange of records of individual contacts between Access and Office Outlook 2007.

Illustration 2: Office Outlook 2007 integration. Click here to view larger image.

6) Filter and order your data: Office Access 2007 facilitates the data filtering. For the text, number and date data types, several filtrate options are available.

Illustration 3: Office Access 2007 filtering options. Click here to view larger image.

7) Work with multi valued fields: Office Access 2007 admits complex data types, so you can create columns that accept more than one value in each cell. For example, if it assigns a task more than a person, it will be able to include both names in the cell. The Windows SharePoint Service’s technology is compatible with these complex data types that help to guarantee the symmetry of data between the local data stores and the based ones on Web.

8) Enclose documents and archives to the data base: Their applications can lodge information that can be more interesting and useful than before. It can enclose several archives - like, for example, photography, documents or spreadsheets, - to individual records within the data store to facilitate the queries. If the file is not in a compressed format, Office Access 2007 will compress it automatically; this way, it will save space in the hard disk.

9) Design of interactive forms: Office Access 2007 includes an interface of design of forms WYSIWYG (what it sees is what is obtained). It can design and modify the form design in real time in the screen - and obtain a previous forms view at design time. With the interface of design WYSIWYG, you can create forms very quickly; in this way, you can spend more time working and dedicate less time to questions of design and format.

10) Rich text: Office Access 2007 provides enriched text compatibility for the data stored in tables.

Illustration 4: Edit forms in Design View. Click here to view larger image.

Report Creation

Use Office Access 2007 to have the complex data organized by consolidating the data in significant information, which facilitates a decision making better based.

11) Interactive report design: With the new functionality of Office Access 2007, you can see how the data will appear in a report at design time. By means of new interface WYSIWYG, you can manipulate the report design directly while you examine the data in the report designer, so that it does not have to execute the report to see the aspect that it will have in the page. This facilitates the creation of information of professional aspect and saves time.

12) Interactive access to information in forms and reports: The new way of data exploration causes that the information are more accessible. Use keys CTRL+B to look for registries, to copy data in other applications and to obtain detailed records to publish the data.

13) Information grouping in reports: The improved grouping panel provides a clear image of data groupings to obtain a previous view of the modifications as they are applied to the information. You can easily add total, subtotal, counts and other elements that help to analyze the data. Also you can group the information in one or several layers and add subtotals.

Illustration 5: Edit reports in Design View. Click here to view larger image.

Shared Information

Office Access 2007 allows to easy compile information and makes it available to other users with more security and flexibility.

14) Collect data through Office Outlook 2007: Office Access 2007 simplifies the process of get information from other users. It automatically creates the data summary form in Microsoft Office InfoPath 2007 or HTML in the mail message’s body. You can send the form to the addressees by Outlook’s contacts or from the own Access. Office Outlook 2007 processes the incoming forms and keeps the data in the data store of Office Access 2007.

15) Web Collaboration with Windows SharePoint Services: The Web sites based on Windows SharePoint Services provide a place in which the equipment can be communicated, share documents and work in a project. With Office Access 2007, you can publish Access‘s files in libraries or move the application to Windows SharePoint Services.

16) Make a Windows SharePoint list’s follow up with Office Access 2007: In order to have a more complete experience, you can follow up the information in lists of Windows SharePoint Services with the Office client Access 2007.

17) Work off-line with Windows SharePoint Service’s lists: With Office Access 2007, you can work with Windows SharePoint Services off-line. If you travel, for example, you can conserve a local copy of a SharePoint’s lists in the portable equipment, where it will be able to publish and to consult.

18) E-mail Notifications and RSS: The users can subscribe to e-mail notifications by adding, eliminating or modifying records. In addition, the subscriptions Really Simple Syndication (RSS) users can be subscribed to sources listed in Office Outlook 2007.

19) Mobile connectivity: Also it is possible to obtain access to the Windows SharePoint Service’s lists through mobile telephones, so the remote users can be updated on the changes conducted in the enterprise information.

20) Compatibility with PDF and XPS: With Office Access 2007, you can keep a report in PDF or XPS format for impression, shipment and distribution by electronic mail. Keeping the report in PDF or XPS file allows easy distribution that includes all the characteristics of that format and it does not require that the other users have Office Access 2007 to print or to review the report.

Security

Office Access 2007 facilitates the information administration requirements through the best transparency of the data in administrable environment.

User Interface

The new user interface is the result of an exhaustive investigation and tests of effectiveness. Its design allows finding the necessary commands more easily.

General information

21) The new user interface includes several elements that define how you will interact with the product. The most significant element of the new user interface is the Options Tape (at the window upper edge) that contains command groups. It provides a unique location for the commands and replaces the menus and toolbars.

Illustration 6: The Options Tape. Click here to view larger image.

22) In Office Access 2007, the main cards of the Options Tape are Start, Create, External Data and Data Base Tools. Each card contains related command groups and these groups include some of the other elements of the new user interface, like the gallery, that is a new type of control that presents/displays the options visually.

23) When Office Access 2007 starts it appears the page Introduction to Microsoft Office Access. This page shows what it is possible to be made to begin to work in Office Access 2007.

Illustration 7: Getting started screen. Click here to view larger image.

You can create a data base from a blank form or from a template. Also it is possible to be gone directly to Microsoft Office Online to obtain more data on 2007 Microsoft Office system and Office Access 2007, or, to click in and to use the menu to open an existing data base.

Options Tape

The Options Tape replaces the previous versions Menu Bar and toolbars. Also it provides the main commands in Office Access 2007. One of the main advantages is that it put in a single location the tasks or the entry points used to request the visualization of menus, bars of tools, task’s panels or other components of the user interface.

24) When a data base is opened, the Options Tape appears in the upper edge of the Office Access 2007 main window.

Illustration 8: Getting started screen. Click here to view larger image.

25) The Options Tape is composed by command’s tabs (Start, Create, External Data and Data Base Tools). Each one has related commands grouped by functionality.

26) The commands in the Options Tape consider the active objects. For example, if a table is opened in the Datasheet view and you click in the Form command inside the Create Tab, Office Access 2007 creates the form based on this table.

27) Keyboard abbreviated methods can be used. All the abbreviated methods of the previous versions of Access remain the same ones. When a tab is selected, the available commands in that tab can be explored.

Run a command

28) Start Access.

29) Select a tab in the Options Tape. The follow table lists the most representative options available in each tab. They may change according to the selected object.

Options Tape Tab

Most common actions

Start

Most common actions

Copy and paste

Change font attributes

Change font alignment

Applies rich text format to a memo field

Work with records (insert, update, delete)

Records sort and filter

Records search

Create

Create an empty table

Create a table using a template

Create a SharePoint List

Create a new table in Design View

Create a new form based on a table or query

Create a pivot table or chart

Create a report based on a table or query

Create a query, macro, module or class module

External Data

Import or link external data

Export data

Get and update data through e-mail

Work with off-line SharePoint lists

Create data extraction jobs

Move database components towards SharePoint Site

Database Tools

Start Visual Basic Editor or run a macro

Create and view database relationships

Show database properties

Run database documentation tool

Move data towards SQL Server or another Access database

Run the linked database tool

Run the add-ins administrator

Create or edit a VBA module

Context cards

Office Access 2007 uses a new user interface’s element, the Contextual Commands Card that appears according to the actual task’s context.

Illustration 9: Press the ALT key to see the shortcut command letter. Click here to view larger image.

Galleries

The new Office Access 2007 user interface also includes a new control denominated gallery. The gallery control works jointly with the Options Tape and focuses attention on obtaining the wished results. Instead of showing the commands, the gallery control shows the results derived from these commands. The objective is to provide a visual form to explore and to see what Office Access 2007 can do, focusing attention on the results and not only in the own commands.

Illustration 10: Use Galleries to see layouts.

9/12/2007 11:47 PM

	Vishal Gupta
6454 posts
www.AskVG.com
Ultimate Member

Re: Microsoft Access 2007 Tutorials for beginners

Here comes Soumya with another nice tutorial. U rock dude. thnx for sharing it.

How to Use Smiley Code in Forum?
Promote MeraWindows at Your Blog / Site
Read Forum Guidelines

9/13/2007 6:27 PM

	Piyush Gupta
1834 posts
www.piyushworld.com
MW Addict

Re: Microsoft Access 2007 Tutorials for beginners

Very nice tutorial again

aapne to MS Office ki ek new helpfile hi yahan per load kar di

Read Forum Guidelines before posting

9/14/2007 12:05 AM

	soumya
2910 posts
feelwindows.blogspot.com/
Forum Guru

Re: Microsoft Access 2007 Tutorials for beginners

A data base is a data collection that belongs to the same context stored systematically for later use. In this sense, a library can be considered as a data base composed by books and indexed for its query.

Microsoft Access 2007 uses the relational model for the database management which is based on the logic of predicates where the data is represented by mathematical n-ary relations. Each relation is represented by the composition of fields stored in tables that represent these n-ary relations.

1) Start Access from the Start menu or from a shortcut.

2) The Getting Started screen will appear.

Illustration 1: Office Access 2007 Getting Started screen. Click here to view larger image.