Data Entry and record manipulating

Certify and Increase Opportunity.
Be
Govt. Certified E-Governance Professional

 The structure of data in a table…

 …is reflected in the structure of a form.

Think of forms as windows through which people see and reach your database. Forms follow function. That is, their design fits the situation in which people will enter or view data. An effective form speeds the use of your database, because people don’t have to search for what they need. A visually attractive form makes working with the database more pleasant and more efficient.

 You choose a method.

 You choose a data source.

Access gives you three main ways to create a form: with the Form Wizard, with an AutoForm, or in Design view. Once you understand all three ways, you can choose the method or methods that will be best for your purposes.

The Form Wizard is best when you want to be guided step by step through the process of creating a form. The Form Wizard asks you questions and creates a form based on your answers.

You will need to tell the wizard:

• Which table or query the form data comes from.
• Which fields to use on the form.
• Which form layout to apply.
• Which visual style to apply.

To start working with the Form Wizard, you would click Forms under Objects in the Database window, and then click New on theDatabase toolbar. In the New Form dialog box, you would click Form Wizard to select this method.

AutoForms are best when you don’t need to make many choices. If your new form will contain all the fields from a table or query, and you don’t need much control over the form’s appearance, an AutoForm is simply perfect.

Unlike the Form Wizard, an AutoForm doesn’t ask you questions or offer you choices. Creating an AutoForm is a two-step process. Pick a type of AutoForm, pick a table or query, and Access does the rest.

There are several types of AutoForm, but the two shown here are all you need to know about now. AutoForm: Columnar shows records one at a time, with each field on a separate labeled line. AutoForm: Tabular shows all records at once, with each field shown for each record, and field labels at the top of the form.

To start working with an AutoForm, you would click Forms under Objects in the Database window, and then click New on theDatabase toolbar. In the New Form dialog box, you would click one of the AutoForm options to select it.

 The toolbox in Design view contains controls, such as text boxes and labels, to add to your form.

 The Design view gridlines and dots organize the area where you insert and arrange controls.

Design view is the best way to create a form when you want full control and complete freedom. In Design view, everything is up to you.

The challenge is that you’re on your own, without the prearrangements of the Form Wizard or an AutoForm. However, you can also create a form by either of those methods, and then change its details in Design view.

To work with a form in Design view, you choose items from the toolbox. These items, such as check boxes, pictures, and labels, are called controls. You can set their properties and format them to look as you want on the form. For details, see the course, “Take the controls.”

In Design view, you can move the controls around, like rearranging pictures on a wall. For example, you may want to group together the text boxes for address, city, state, and ZIP code—but on two lines, or three, or four? You can easily try them all and pick the best.

To see the form as it will appear when someone works with it, you can open it in Form view, and keep switching between Form and Design views until the form suits you completely.

To start working in Design view, you would click Forms under Objects in the Database window, and then click New on the Databasetoolbar. In the New Form dialog box, you would click Design View to select this method.

Reports

Reports provide many ways to organize, categorize, and even summarize your data. They help you show your data to best effect, and they offer forms of presenting data that may be new to you.

As with other parts of Access, the first question you need to consider is, what do you want the report to do?

Queries

Queries let you pull just the data you need out of a database so you can perform tasks and get questions answered.

With queries, you can retrieve, combine, reuse, and analyze your data. You can use queries to retrieve data from multiple tables, or as a source for forms and reports.

In this course, we’ll stick to the basics of choosing and retrieving data with select queries, which will give you a good foundation for working with other, more complex types of queries.

Click Next to continue reading this lesson on your own.

Access provides many different types of queries to meet many different needs for data.

We’ll briefly describe most of the query types, although in this course we’ll focus on the most commonly used query type, the select query.

A select query retrieves data from one or more tables and displays the recordset in a datasheet. You can also use a select query to group data, and to calculate sums, counts, averages, and other types of totals, although we won’t cover calculations in this course.

A parameter query displays a dialog box when it runs, prompting the user to enter information to use as criteria for the query. You can design a parameter query to prompt for more than one piece of information; for example, you can design it to prompt for two dates. Access will then retrieve all data with values between those two dates.

A cross-tab query arranges a recordset to make it more easily visible, using both row headings and column headings. Data can be seen in terms of two categories at once.

An action query creates a new table or changes an existing table by adding data to it, deleting data from it, or updating it. Because an action query is so powerful, actually changing table data, you should consider backing up your data before running an action query.

An SQL query is created by using a statement in Structured Query Language (SQL). SQL is an advanced way to query, update, and manage relational databases. Access can create the SQL statement for you when you create this type of query, or you can create your own SQL statement.

As with forms and reports, Access provides two basic ways to create queries: by using a wizard, and in Design view.

The wizard, also known as the Simple Query Wizard, gives you a head start in setting up your query’s structure by making some arrangements for you.

In Design view you have total control when creating a query. You drag the fields you want to a grid, and then you enter the criteria for selecting the data to be retrieved.

You can also create your query using the wizard and then refine the query in Design view to get just what you need.

The Simple Query Wizard begins by prompting you to select the database fields you need. You can select fields from tables. You can also select fields from preexisting queries.

For example, if you were retrieving a list of people from an Employees table, the wizard would prompt you to choose fields to include in your query, such as each employee’s name, manager, and extension.

The wizard also enables you to group and summarize data, which isn’t necessary for simple queries but can help you analyze more complex recordsets.

After you create your query, the wizard can run it, or the wizard can take you to Design view. In Design view you can specify criteria and refine your query. For example, you could specify in Design view that the retrieved list show only employees in the Marketing Department and that it be sorted by the employees’ last names.

 Design view shows the tables or the existing queries that you can base your query on…

 …and a grid where you can add criteria and modify your query.

In Design view, you begin by choosing the tables or existing queries that contain the fields you want to use.

Then, you select and drag those fields to a grid. The fields can come from just one table, or from multiple tables. In this course, we focus on working with one table.

After you have added your fields, you can specify criteria and other settings, such as whether to sort the results.

Note    You can change the fields used in a query after you have made your original selection. For details on making changes, see the Quick Reference Card.

Whether you create your query with the wizard or in Design view, testing it in Design view lets you easily tweak your query if you don’t get the results you want.