Introduction

A select query is a type of database object that shows information in Datasheet view. A query can get its data from one or more tables,
from existing queries, or from a combination of the two.
The tables or queries from which a query gets its data are referred to as its recordsource.

Whether you create simple select queries by using a wizard or by working in Design view, the steps are essentially the same. You choose the recordsource that you want to use and the fields that you want to include in the query — and, optionally, you specify criteria to refine the results.

After you have created a select query, you run it to see the results. Running a select query is simple — you just open it in Datasheet view. You can then reuse it whenever you need, for example, as a recordsource for a form, report, or another query.

NOTE For more information about using a query as a recordsource for a form or report, see the article Use a query as a recordsource for a form or report.

There are several types of query, each serving a different purpose. For example, a select query displays data. An action query changes the data in its datasource, or creates a new table. A parameter query prompts you to supply criteria when you run it. This topic only covers select queries.

NOTE This topic describes how to create select queries that return data from a single table. If you want to create queries that return data from two or more tables, see the article Create a query based on multiple tables.

Create a select query

A select question is employed to form subsets of information that you simply will use to answer specific queries. It also can be wont to offer knowledge to different info objects. Once you produce a choose question, you'll use it whenever you would like.

This topic explains a way to produce an easy choose question that searches the info in a very single table. you may conjointly learn the way to reinforce the question by process record criteria and adding calculated values.

Enter the sample data manually

On the Create tab, in the Tables group, click Table.

Office Access 2007 adds a new, blank table to your database.

NOTE You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.

Double-click the first cell in the header row and type the name of the field in the sample table.

By default, Access denotes blank fields in the header row with the text Add New Field, like so:

Use the arrow keys to move to the next blank header cell and then type the second field name (you can also press TAB or double-click the new cell). Repeat this step until you enter all field names.

Enter the data in the sample table.

As you enter the data, Access infers a data type for each field. Each field in a table has a specific data type, such as Number, Text, or Date/Time. Data types help ensure accurate data entry and help to prevent mistakes, such as using a telephone number in a calculation. You can specify the data type for each field in a table, and can change a field's data type under certain circumstances. For this sample table, you should let Access infer the data type.

When you finish entering the data, click Save

Keyboard shortcut Press CTRL+S.

The Save As dialog box appears.

In the Table Name box, enter Customers, and then click OK.

Copy the table to a spreadsheet program and then import it into Access

Start your spreadsheet program and create a new, blank file. If you use Excel, a new, blank workbook is created by default.

Copy the sample table provided in the previous section and paste it into the first cell of the first worksheet.

Using the technique provided by your spreadsheet program, name the worksheet Customers.

Save the spreadsheet file to a convenient location and go to the next steps.

Import the table into Access

In a new or existing database:

On the External Data tab, in the Import group, click Excel.

-or-

Click More, and then select a spreadsheet program from the list.

The Get External Data - Program Name Spreadsheet dialog box appears.
Click Browse, open the spreadsheet file that you created in the previous steps, and then click OK.

The Import Spreadsheet Wizard starts.
By default, the wizard selects the first worksheet in the workbook (Customers, if you followed the steps in the previous section), and data from the worksheet appears in the lower section of the wizard page. Click Next.
On the next page of the wizard, select First row contains column headings, and then click Next.
The next page of the wizard offers you an opportunity to change field names and data types or to omit fields from the import operation, by using the text boxes and lists under Field Options. You should not do this for this example. Click Next.
On the next page of the wizard, in the Field Options box, select Yes (No Duplicates) from the Indexed list, and select Long Integer from the Data Type list. Click Next to continue.
On the next page of the wizard, select the Choose my own primary key option, choose CustomerID from the list, and then click Next.
By default, Access applies the name of the worksheet to your new table. Make sure that the table is namedCustomers, and then click Finish.
On the last page of the wizard, you have the option of saving the import steps for later reuse. Because you are importing a sample table, it is not recommended that you save the import steps.

NOTE If you don't have a spreadsheet program, you can copy the sample data to a text editor, such as Notepad. For more information about importing text data, see the article Import or link to data in a text file.

Build the query

First, you will use a wizard to create the query, based on the sample table that you just created. Then, you will enhance the query in Design view. At each step, you can review the SQL statement that is automatically generated by the steps that you take.

On the Create tab, in the Other group, click Query Wizard.
In the New Query dialog box, click Simple Query Wizard, and then click OK.
Under Tables/Queries, click the table that has the data that you want to use. In this case, click Table: Customers. Note that a query can also use another query as a recordsource.
Under Available Fields, double-click the Contact, Address, Phone, and City fields. This adds them to theSelected Fields list. When you have added all four fields, click Next.
Name the query London Contacts, and then click Finish.

Access displays all of the contact records in Datasheet view. The results show all of the records, but show only the four fields that you specified in the query wizard.

Step-by-Step: Select Query

What you will learn:

to design a select query manually
to add all fields from a table at once to grid
to clear the design grid
to add fields to design grid
to move columns in a query
to use criteria in a select query
to combine criteria in a select query
to find Null or Not Null values
to use Like operator
to name and save a query

Start with: , Project database open.

First you will create and modify a basic Select query. Then you will experiment with different types and combinations of criteria.

Query Design View

With the Projects database open, in the Database Window, select the Staff table.
Click on the New Objects button and select Query .
The New Query dialog appears.
Select Design View.
The Query Design View opens with the Staff table already showing in the top part of the window. The query grid at the bottom is blank.

Include All Fields in Query

You add fields to the query by dragging them from the table at the top and dropping them onto the grid. You can drag more than one at a time.

Move your mouse pointer over the * at the top of the list of fields in the Staff table.
Drag the * down to the first column and drop.
The image at the right is an animation. Refresh this window to see it run again.

The * asterisk represents all of the fields in the table.
Click the Run button to execute this query. You will see all of the fields and all of the records in the Staff table.

Of course this query is not very useful! You might as well have used the table itself.
Switch back to the Query Design View by clicking the View button .
From the menu select Edit | Clear Grid .
The grid is now blank again. This command is especially useful when there too many fields to select them all easily. Remove a column: You can also select the columns you don't want and press the Delete key.

Add Fields to Query Design Grid

Recall that sorting rearranges the records while using criteria hides some records.

When selecting fields to drag to the design grid, you can use the normal selection methods.

Single field: Drag any single field.
Adjacent fields: Hold SHIFT down to select all items between the two you click on.
Non-adjacent fields: Hold CTRL down to select items individually which are not next to each other.

Single field:
Click on the field EmployeeID and drag it down to the design grid and drop it on the first column.
Multiple fields:
1. Click on the field FirstName.
  Scroll, if necessary, until the field LastName shows.
2. Hold the SHIFT key down and click on the field LastName.
  Three fields are selected: FirstName, MiddleName, and LastName.
3. Drag the selection down to the design grid and drop on the second column.
  All three fields appear, in the same order as in the table list.

Sort: Multiple Fields

You will usually need to kind many fields in your question. The order of the columns is very important. Sorting and criteria ar applied beginning with the left-most column and moving to the correct.

Click in the Sort row in the second column, FirstName.
Click on the arrow that appears to open the list of choices and select Ascending.
Repeat for the fields MiddleName and LastName.
Switch to Datasheet view by clicking the View button .
Be careful of using the Run button instead of the View button to switch to datasheet view. When your query is an action query, you will want to view the datasheetbefore actually performing the action!

Hmmm. The first names are in alphabetical order. What we really want is to sort on the LastName and then on FirstName and then on MiddleName. Happily, this is easy to fix!
Switch back to Query Design View by clicking the Views button .
(Isn't it handy that this button changes as you change views?)
Move your mouse pointer over the top of the LastName column until it turns into the Select Column shape and then click.
The whole column is selected.
Position your mouse pointer over the top of the LastName column until it changes to the Select shape .
Drag to the left.
The mouse pointer changes to the dragging shape .
A black bar between two columns shows you where dropping will place the column that you are dragging.
Drop to the left of the FirstName column.
The LastName column moves.
Switch to datasheet view. Aha! Now you have the sort order that you need. Of course since none of the first or middle names are the same, it is hard to be sure!

Set Criteria

To select just some of the records, you need to enter one thing within the Criteria row within the question style read. solely records that match those rules are going to be shown within the datasheet.
To make things a little a lot of attention-grabbing, let's add a field to the question.

Switch back to the Query Design View.
Scroll the table fields list at the top of the window until you see the field Country/Region. Drag the field and drop it on the second column.
Unexpectedly, this field does not replace the second column.
The new field appears between the Employee ID and Last Name columns.
In the Criteria row, type Argentina in the second column and press TAB to exit the cell.
Access adds double quote marks around your text automatically after you exit the cell.
You did not have to type an = before the value. Access assumes that it is there in the Criteria row unless you type something else, like < or >, for example.
Run the query. Only records with Argentina in the second column are showing.

Multiple Criteria Rows

You can use as many various criteria in your question as you wish and in any combination.
The records in an exceedingly query's results datasheet should match the entries in one whole row within the Criteria section. Each row, however, could be a separate set of criteria.

Switch back to the Query Design View.
In the first Criteria row in the first column, type <5.
In the second Criteria row in the first column, type >5.
You now have two different criteria. The query will display records for which:
- EmployeeID < 5 AND Country/Region = "Argentina"
  OR
- EmployeeID > 5
Run the query.
Eight of the nine records fit one or the other of the two criteria sets. Records 6, 7, and 8 do not have Argentina as their Country/Region, but they do have EmployeeID numbers > 5. The choice of criteria did not reveal anything interesting, but it did show what can happen when you choose badly!Notice that the records are not sorted by which of the criteria rows they satisfied but on the name fields, as before.

Using OR for Criteria for a Field

You can combine criteria for a single field with OR, such as:
>15 OR <5
"Chavez" OR "Fuller"

Switch back to Query Design View.
Delete the criteria in column 1.
In column 2, edit the criteria to read "Argentina" Or "Australia" .
Run the query.
Six records match either Argentina or Australia.

Alternate method: You would get the same results by putting Argentina and Australia on separate Criteria rows. However, if there are also criteria for other columns, you would have to repeat those entries on the second row.

Using NOT

To eliminate records that match a value, the NOT operator is handy.

Switch back to Query Design View.
Replace the entry in the Criteria row for column 2 with Not "usa".
Run the query. You get the same results as before! The only values available in the Country/Region field right now are Argentina, Australia, and USA. So Not "usa" is the same thing as "Argentina" Or "Australia"

Note: You did not have to match the capitalization of the value USA.

Finding Null or Non-Null Values

It is common to look tables for blank values. These could also be Null or zero-length strings. within the Table style read, you'll be able to opt for whether or not or to not permit those sneaky zero-length strings. The default is N, thus none of the fields within the employees table area unit allowed to carry a zero-length string.

Switch back to Query Design View.
Delete the entry in the Country/Region criteria row.
In the Criteria row for MiddleName, type Is Null.
Run the query.
Only two records are missing a middle name.
Switch back to Query Design View and change the criteria to Is Not Null.
Run the query.
This query now shows the 7 records which do have a value for MiddleName.

Finding Null or zero-length string: You can combine two criteria if you do not know whether or not the field allows zero-length strings, like Is Null Or "".

Using Like

Sometimes you want to see records that match a pattern rather than matching a particular value exactly. For example, you might want to see all names that start with L.

With the Like operator you can create a pattern using wildcard characters. This is a very flexible and powerful method.

Switch back to Query Design View.
Delete the existing criteria.
In the Criteria row in the LastName column, type Like "g*".
The asterisk * is a wildcard character which stands for "any number of characters". So this criterion will match any text value that has the letter g as the firstcharacter. Without the *, the query would match only text that had g as the only character.
Run the query.
Two last names start with the letter g.
Switch back to Query Design View.
Edit the criterion to read Like "??????".
The question mark ? is a wildcard character that stands for 1 character. So this criterion asks for values with exactly 6 characters.
Run the query.
Two last names have exactly 6 characters.

Save and Name a Query

Later it will be useful to you to be able to tell a query from a table from just its name. Queries and tables are shown alphabetically in the lists from which you choose a source for your forms and reports.

Naming objects:

Many database programmers start the name of a database object with an abbreviation for the kind of object it is, resulting in awful names like tblStaff for a table or qryStaffNames for a query or rptStaff for a report. Such names can be useful when writing the small programs (macros and procedures) that the programmer uses to control how the database behaves. However, recently programmers are getting away from that style of naming. Programmers generally also avoid using spaces in names to make it easier on them to write their programs.

Switch back to Query Design View.
Remove all of the criteria.
From the menu select File | Save As .
The Save As dialog appears. Notice that you are not saving the database file, just the query. You cannot use Save As on the database as a whole! Quite different from other programs.
Name your query QSort Staff Names.
Click on OK to save the query and close the dialog.
Close the query by clicking its Close button .

Computer full information

Sunday, June 28, 2015

Learning select query in access