MS-Access

1) Create Table-A relational database like Access usually has several related tables. In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns).

Create a table in MS Access (एम एस एक्सेस में टेबल का निर्माण करना)

MS Access मे डाटा को स्टोर करने के लिये टेबिल का निर्माण करना होता है। टेबिल डाटाबेस फाईल के अंदर हेाती है। एक डाटाबेस फाईल के अंदर एक से अधिक टेबिल हो सकती है। टेबिल का निर्माण रो एवं काॅलम से मिलकर हेाता है। फील्ड मे डाटा टाईप को सेट किया जाता है।
First step-
Go to file menu→ new→ click on blank database→ insert file name→ click on create button.

1.create a table in design view:

Create Form :- Create a form from an existing table or query in access. To create a form from a table or query in your database, in the Navigation Pane, click the table or query that contains the data for your form, and on the Create tab, click Form. Access creates a format displays it in Layout view.

Create a blank form in Access:-

1) To create a form with no controls or p reformatted elements: On the Create tab, click Blank Form. Access opens a blank form in Layout view, and displays the Field List pane.

2.) In the Field List pane, click the plus sign (+) next to the table or tables that contain the fields that you want to see on the form.

3.) To add a field to the form, double-click it or drag it onto the form. To add several fields at once, hold down CTRL and click several fields, and then drag them onto the form at the same

time.

Why use forms?

In real life, a form is piece of paper you fill out so someone can collect and keep track of specific information about you. Only one record—your record—is captured with any given paper form.

Access 2007 forms work in a similar way.

In previous lessons, you saw that you can populate a database by entering records into the tables themselves. If the database has hundreds of records and many fields to populate for any given record, a table can be overwhelming to a user entering data. An Access form lets you enter data one record at a time, without having to see the entire table.

An Access 2007 form also lets you know exactly what information to enter and can even tell you what that information should look like. Adding certain control components to a form—like a drop-down menu—can dramatically increase the integrity of the data that is held in a database.

A database owner wants to control the levels of access other database users have to the data; the fewer the amount of people who are interacting with the data, the lower the chances are of the data becoming compromised. Forms are one more way a database owner can limit the actions of other users. Form properties can be set so users can only enter records or just view records.

Creating a form

Access 2007 has several automatic tools for creating forms. These tools are located in the Forms group on the Create tab in the Ribbon, as seen below:

Access 2007 forms tools include:

· The Form command makes a basic form, showing a single record at a time.

· The Split Form command creates a form showing one record on top and includes the Datasheet view of the entire source table on the bottom.

· The Multiple Items command creates a form that shows all records at once, which looks similar to the source table in Datasheet view.

· The Form Wizard is hidden under the More Forms command. It walks you through the process of creating more customized forms.

To create a form using the Form command:

The basic Form command is the one we suggest because it allows you to see just one record at a time. It also includes all of the fields in your source table for you, and you can modify the layout of the basic form to hide fields or add controls:

· Begin by highlighting the table you want to use as a source table.

· With the source table highlighted, select the Form command from the Forms command group in the Createtab on the Ribbon.

· The new form is created and opens in the object pane.

The newly created form has the same name as the source table by default. You can give the form a new name by saving the form. You will be prompted to give the form a name.

Using forms to enter data

Populating a database is easy once you have a basic form in place. Record navigation works the same way for forms as it does for tables. The navigation bar is located in the bottom-left of the object pane. The navigation buttonswork the same way as they do for tables. The picture below shows the navigation buttons for a form.

To add a record using a form:

· Navigate to a new record, either by using the New Record navigation button or by using the New command in the Records group on the Ribbon.

· Add the new data.

· Save the record.

Your data must be entered using an acceptable format. The acceptable formats were established when the field properties were set.

Save by using either the Save command on the Ribbon or by progressing to another record using the New (Blank) Record navigation button. Moving to a new record saves the most recently entered record. However, it may be necessary to refresh the table in Datasheet view to see the newest record.

To edit records using a form:

Just like in a table, database users can edit records from a form using the Find and Replace command. This command works exactly the same way in a form as it does in a table.

Creating a drop-down list

Using a drop-down list on a form can increase the integrity of the data in the database because drop-down lists force form users to select one of the preset options in the list to populate the field. These types of form controls are relatively easy to set up using the Combo Box.

To create a drop-down list using a Combo Box control:

· With the form opened in Design view, select the Combo Box command in the Controls group on the Designtab in the Ribbon.

· Drag and drop the combo box sizing tool to create the combo box where you want it to be on the form.

· The Combo Box Wizard appears.

· Choose the desired option from the wizard, and click Next.

· Because the middle option was selected in the example above, the wizard progresses to the next step, which asks for the values to be typed into a small table.

· Next, the wizard asks what to do with the entered values. Access can either remember the values for later use, or it can populate a field with the entered values. Use the drop-down list to select which field Access should use to store the values.

· Once the desired option has been selected, click Next.

· Finally, the wizard gives the combo box a generic name, which can be meaningful to you later if there's ever a need to adjust the properties of this or another combo box. If you want, give the combo box a name, then click Finish.

Whatever name is entered will appear as a label on the form. This label can be deleted if you want.

Switch to Form view to see how the combo box works. The Category drop-down list appears on the Books form, as seen in the image below.

Challenge!

If you haven't already done so, save the sample Ready2Read database to your computer.

· Scroll through the customer records using the Customers form.

· Create a basic Books form using the Form command.

· Use the new Books form to enter and save the following data:

o Title: The Secret Streets of Savannah
Author: Amy Little
Category: Travel
Price: $34.99

o Title: Cars and Trucks
Author: Jonathon Bradley
Category: Kids
Price: $14.99

· Using the Combo Box command, create a drop-down list on the Books form for the following categories:

o Fiction

o Nonfiction

o Kids

o History

o Technology

o Home & Garden

o Travel

o Food

· Add another record to the database using the Books form with the drop-down category selector.

· Use the Find and Replace command to change the price of all books that are $14.99 to $16.99.

Previous: Entering and Editing Data in Tables

3) Create Query:-Select the Create tab in the toolbar at the top of the screen. Then click on the Query Design button under the Other group. Next, highlight the tables that you wish to use in the query. In this example, we've selected the Employees table and clicked on the Add button.

MS Access 2007: Create a query

This MSAccess tutorial explains how to create a query in Access 2007 (with screenshots and step-by-step instructions).

See solution in other versions of Access:

Access 2003

Question: In Microsoft Access 2007, how do I create a query?

Answer:Select the Create tab in the toolbar at the top of the screen. Then click on the Query Design button under the Other group.

Next, highlight the tables that you wish to use in the query. In this example, we've selected the Employees table and clicked on the Add button. When you are done selecting the tables, click on the Close button.

Add the fields to the query. You can do this by double-clicking on the field name. In this example, we've added the LastName, FirstName, and Address fields.

Then click on the Save button at the top left of the window (this is the button with the picture of the disk).

Create a Simple Query

Step 1: Open the database containing the table(s) you want to query in Microsoft Access.

Step 2: On the Create tab of the Access ribbon, click the Query Wizard button.

Step 3: Select Simple Query Wizard. Click OK to continue.

Step 4: From the Tables/Queries drop down list, select the table or query that contains the fields you wish to use in the query.

The Save As window should appear. Enter the name that you'd like to assign to the query and click on the OK button. In this example, we've saved the query as Query1.

You should now see the query appear in the left window.

Step 5: From the list of Available Fields, select each field that you want included in your query and add it to the Selected Fields list by clicking the “>" button. As a shortcut, if you want to add all fields, click the “>>" button. Click Next to continue.

Step 6: In the next screen of the Simple Query Wizard, choose whether you want to run a Detail or Summary query. A Detail Query will return a listing of every record, whereas a Summary Query will, as its name implies, return a summarized version of the results.

If you choose to perform a Summary query, you’ll also have to decide how you want the information summarized. To do this, click the Summary Options button and check which items should be summarized. Also, if you want a count of how many items are included in each category of your summary, put a check in the box next to Count Records. Click OK to exit the Summary Options window.

Click Next to continue. Skip to Step 8 if you chose to run a Detail Query.

Step 7: If you’re running a Summary Query, the next step in the wizard may ask you to choose how you want to group the records in your query. Make your selection and click Next to continue.

Note: This step may not appear in every Summary Query. Whether it does or not depends on what fields you have chosen to be displayed when performing the query.

Step 8: Choose a name for your query and click Finish.

The new query can now be viewed in Access.

For more tips and tutorials, take a look at the other items in Bright Hub's collection of Microsoft Access user guides. Learn how to create a new form as well as how to add fields to existing forms, or get advice on general database design strategies. New articles are added to the collection on a regular basis, so don't forget to bookmark the page and check back often.