Searching data using query

  SEARCHING DATA USING QUERY

A query in Microsoft Access 2007 is a request to display database information in a window format. The window, also called a Datasheet View, shows any data from a table in a row-column format. A query can be created by using a wizard tool or by using the Design view in Access.

A query lets you retrieve certain information based on your criteria.

Design view offers the most common approach to query building. It provides a handy graphical tool that you can use to perfect any query.

To create a query, follow the following steps:

1. Choose Create → Other Query Design.

A new design window appears where you can craft your query. But before you get started. Access pops up the Show Table dialog box, where you can choose the tables that you want to work with. This is shown in the figure below:

2. Select the table that has the data you want, and then click Add (or just double-click the table).

3. Click Close.

The Show Table dialog disappears, giving you access to the Design view for the query

4. Select the fields you want to include in your query. To select a field, double-click it in the table box. A sample is shown in the following figure:

5. Arrange the fields from left to right in the order you want them to appear in the query results. When you run the query, the columns appear in the same order as they're listed in the column list in Design view.

6. Choose a sort order for a field if you wish to in the corresponding Sort Box. In the current example, the table is sorted in ascending order on the basis of First Name.

7. Click Query Tools → Design tab → Results group → Run.

Now that you've finished the query, you're ready run the query. You'll see the results presented in a datasheet, just like when you edit a table. This is shown in the following figure:

8. Press CTRL+S to save this query. A dialog box appears asking the name of the query.

9. Give a name of your choice and press OK.

Once you've created a query, you'll see it in your database's navigation pane. You can launch the query at any time by double-clicking it.

 CREATING A FORM

A form in Microsoft Access is an object that allows users to easily add and view data in a table. While you can always perform these tasks directly in the table, that method of doing things can get fairly cumbersome, especially if the table is quite large. It also may contain controls that provide the user with needed functionality enhancements. You can use forms to control access to data, such as which fields or rows of data are displayed.

 Using Form Wizard

The Form Wizard in Access 2007 creates a form from one or more tables or queries. It will ask you detailed questions about the fields, layout and record sources, and the desired format and create a form based on those responses.

The steps involved in creating a form using a form wizard are as follows:

1. Open the database which you want to connect with the form.

2. On the Create tab, in the Forms group, click More Forms and then select Form Wizard.

Following Form Wizard window pops up on your screen.

3. From the dropdown box for Tables/Queries, select the table or query that contains the fields that you want included in your new form. In our example, we will select Student table.

4. Select each item from the Available Fields column that you want to include in your form and move it to the Selected Fields column by clicking the button.

If you want to all the fields to your form, you can use the button.

5. When you've finished selecting all the fields you want to use, click Next to continue. The following wizard screen is displayed.

6. In the above figure, choose any one of the form layouts. If you are not sure which one to choose, you can select an option and get the idea from the preview of that particular layout. After selecting any one layout, click Next button.

7. Now, select a style for your form. In this example, we will select Module style. After the final selection, click Next button.

8. Next screen requires the title of the form. By default, Access assigns a title to the form. You can choose to keep the default title or given a title of your own.

9. Click Finish from the final step of the Form Wizard.

This will display the form in the right section of the window. A form navigation bar also appears in the bottom of the window. The final resultant form is displayed in the following figure:

Creating a Customized Form

Although the Form Wizard offers you a faster way of creating a basic new form in Access you can create a form manually having greater control over appearance and function of your final form. You can make the form as simple or complex as you desire.

In the following steps, we will see how to make a form in Design View in Access 2007.

1. After opening the desired database to which you would like to add a new form, select Form Design button from the Create tab. This will open a blank form in Design View as shown below.

2. Make sure that the Design tab under Form Design Tools is open on the Access ribbon.

Click on Property Sheet button. This opens the Property Sheet pane as shown in the figure below:

3. In the Property Sheet window, click on the Data tab. From the Record Source dropdown list, select the table or query that contains the fields you want to include in your form. This dropdown list contains the names of all the tables and queries which are a part of the database.

4. After selecting a table, click on the Add Existing Fields button (7) in the Tools group. This opens up the list of all available fields from the table you choose.

5. Click on a field in the Field List panel that you want to add to the form. Hold down the mouse key and drag the field to the position on the form where you want it to be located.

This can be seen in the figure below:

After placing the field, you can resize both the field label and content boxes by clicking and dragging the boundary boxes around the field name.

At any time during this process, if you want to get an idea of how the final form will look, click on the View button() and select Form View.

To return to your form design in order to make additional modifications, click View again and choose Design View.

6. After completing the form, press CTRL+S to save the form. Enter a name for the form in the pop-up box that appears on the screen.

7. Click OK when done. The new form will be visible in the Forms list.

DESIGNING REPORTS IN ACCESS 2007

A report simply provides a printed version of your data arranged in some useful way Reports summarize and present data stored on tables. A report can be executed at any time and it will always reflect the updated data of the database. Reports are specialized database objects, much like tables and queries.

The easiest way to create a report is to use the Report Wizard, which guides you step by step through arranging and selecting which data to print on a report along with sorting your data at the same time. To use the Report Wizard, follow these steps:

1. Click the Create tab.

2. Click the Report Wizard icon in the Reports group. The Report Wizard dialog box appears.

3. Click in the Tables/Queries list box and choose the table or query that contains the data you want to print in a report.

4. Click a field in the Available Fields box and then click the button to move the field into the Selected Fields box. Repeat this step for each additional field you want to display in your report.

5. Click Next to proceed to next step.

Another dialog box appears, asking whether you want to group your data by a specific filed such as First Name or Last Name.

6. Click a field name displayed in the box and then click the button. We have grouped on the basis of Last Name as shown in the figure.

7. Click Next button.

Another dialog box appears, asking you to choose up to four fields to use for sorting your data in your report.

8. Click Next.

Another dialog box appears, asking you how to lay out your report, as shown in figure.

9. Click on your choice and click Next button.

Another dialog box appears, asking you to choose a style for your report.

10. After selecting the desired style, click Next to proceed.

A dialog box appears asking you for a descriptive name for your report.

11. Type a descriptive name for your report and then click Finish.

Access displays your report with the name as the title of the report. The final report is shown in the following figure. You can see that the records are grouped on the basis of Last Name field.

 EXPORTING DATA TO EXCEL 2007

Exporting data to Excel 2007 is the process of transferring data from another application or database into a Microsoft Excel 2007 worksheet. This allows the data to be viewed, edited, analyzed, and used for calculations in Excel.

Exported data is usually saved in Excel file formats such as .xlsx or .xls.

Need for Exporting Data to Excel

Exporting data to Excel is required because:

Excel provides powerful tools for data analysis

Data can be easily edited and formatted

Calculations can be performed using formulas and functions

Charts and reports can be created from the data

Sources of Data Exported to Excel

Data can be exported to Excel 2007 from:

Databases (MS Access, SQL, MySQL)

Accounting software

Text files (.txt, .csv)

Other spreadsheet applications

Steps for Exporting Data to Excel 2007

The general steps for exporting data are:

Open the source application or database

Select the data to be exported

Choose the Export option

Select Microsoft Excel 2007 as the destination format

Choose the file location

Click on Export or OK

File Formats Used While Exporting

Common file formats used for exporting data include:

.xlsx – Excel 2007 workbook

.xls – Excel 97–2003 workbook

.csv – Comma Separated Values

Advantages of Exporting Data to Excel

Easy data handling and analysis

Better presentation of data

Data can be sorted, filtered, and summarized

Data can be shared easily

https://www.youtube.com/@KrishnaDubaiMotivation

https://www.facebook.com/share/1H9PPi8tMX/

https://www.instagram.com/officialkrishnadubai?igsh=MXY1eDJiY3owOGtiYQ==

https://wa.me/message/ONUZUUV4Q2YGO1

For corporate Inquiries:

Call Us: +91 9262835223