Queries: What they are and how they work

How a query appears in design view.

Points to note: Source of the Query is a Table or another Query.

Field Name row 1, Table Table Row 2. Sort Row 3. Show Row 4. Criteria Row 5.

The second column has no Table name, as it is a derived field. We use the column In Use , and say if the value is 0 then include it. If it is ticked, it is -1 and not to be shown.

Thus in data view we get only the unticked entries.

Note that the color of the data view can be modified, and also the fonts can have colors.

Note the derived field "Account" has two fields, one is Bank Name and the other is Account Number. This is done by creating an expression, through an Expression Building Wizard.

Building an Expression.

When you right click with your mouse, with the pointer on the field name, you get a dialog box on which you should click on the word BUILD. You then get the above dialog box.

The Expression is Account:[BankName]&" " &[BankAccount Number]

You can write it by clicking on the field names in the list below and then clicking on paste on the wizard. Finally, you have to close it by clicking OK. Remember Account is the new field name, should be followed by a : colon. This should be written after the expression has been pasted.

The wizard also gives a name automatically, which you can refer in your program. This name is usually, Expr 1, Expr 2 etc.

Note that the Query should be saved and a name given, before the expressions can be built.

This is an example of joining two fields into a new one. You also see how only the records you need are displayed by using "Criteria"

Cash Purchases Query

In this query the last column Total is a derived field, by multiplying [Quantity] with [Amount] where the two are fields taken from the table Cash Purchases.

Total = : [Quantity]*[Amount]

Class and Students Query - note the join: One to Many. 1 is the field which is unique. 00 is the field where it can appear many times. How to enforce relationship click here and read Relationships. - How to ENFORCE RELATIONSHIPS in Advance Information.

Criteria to limit the list.

The field name is RecipeNumber and we want to select the number between 1 and 2 this will force the computer to produce all 1 and 2 recipe numbers. Since the recipe number is from the "Many" table, it will repeat.

The other common criteria is records within a certain range of dates. Between #01/01/2006# and #31/01/2006# put under the date field will give only records of January.

In the same way, if there is a query of Students, and in the Student Number field you type 10 you will get only the student with serial number 10.

Ways to bring together data from multiple tables or queries in a query

The power of queries lies in being able to bring together or perform an action on data from more than one table or query. For example, you might want to view a customer's information with the orders the customer placed. To see this information, you need data from the Customers and Orders tables.

When you add more than one table or query to a query, you need to make sure their field lists are joined to each other with a join line so that Microsoft Access knows how to connect the information.

If tables in a query aren't joined to one another, either directly or indirectly, Microsoft Access doesn't know which records are associated with which, so it displays every combination of records (called a "cross-product" or "Cartesian product") between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10). It also means the query might take a long time to run and ultimately might produce less meaningful results.

If you previously created relationships between tables in the Relationships window, Microsoft Access automatically displays join lines when you add related tables in query Design view. If referential integrity -
Rules that you follow to preserve the defined relationships between tables when you enter or delete records. If you enforce referential integrity, Microsoft Access prevents you from adding records to a related table when there is no associated record in the primary table, changing values in the primary table that would result in orphan records in a related table, and deleting records from the primary table when there are matching related records in a related table. - is enforced, Microsoft Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol to show which table is on the "many" side.

Even if you haven't created relationships, Microsoft Access automatically creates joins if you add two tables to a query and the tables each have a field with the same or compatible data type and if one of the join fields is a primary key. The "one" and "many" symbols are not displayed in this case, because referential integrity is not enforced.

Sometimes the tables you add to the query don't include any fields that can be joined. In this situation, you have to add one or more extra tables or queries to serve solely as a bridge between the tables whose data you want to use. For example, if you add the Customers and Order Details tables to a query, they won't have a join line between them because they don't have any fields that can be joined. But the Orders table is related to both tables, so you can include the Orders table in your query to provide a connection between the other two.

Once tables and queries are joined, and you've added fields from both tables or queries to the design grid in query Design view, the default join tells the query to check for matching values in the join fields. (This is called an inner join (A join in which records from two tables are combined and added to a query's results only if the values of the joined fields meet a specified condition. For example, when designing a query, the default join between tables is an inner join that selects records from both tables only if the values of the joined fields are equal.) - in database terminology.) When it finds matches, it combines those two records and displays them as one record in the query's results. If one table or query doesn't have a matching record in the other table or query, neither record appears in the query's results. If you want the query to select all the records from one table or query whether or not it has matching records in the other table or query, you can change the join type.

Join multiple tables and queries in a query

In query Design view, drag a field from the field list for one table or query to the equivalent field (a field of the same or compatible data type containing similar data) in the field list for the other table or query.
With this type of join, Microsoft Access selects records from both tables or queries only when the values in the joined fields are equal.

Notes

Join numeric fields only if the FieldSize property settings for both are Byte, Integer, or Long Integer.
You can change the type of join so that Microsoft Access selects all the records from one table or query whether or not it has matching records in the other table or query. For more information, click .

Change the type of join between tables and queries in a query

Open a query in Design view.

Double-click the join line between the field lists for the tables or queries.

In the Join Properties dialog box, click the join option you want, and then click OK.

Notes
From the Relationships window, you can change the default join type for tables.

All of the joins created using the Join Properties dialog box select records only if the values in the joined fields are equal. You can create joins that select records only if the value in one join field is greater than, less than, not equal to, greater than or equal to, or less than or equal to the value in the other join field; to do so, you must create an SQL statement in SQL view. see Query design view and see views. One of the is a procedure language called the SQL.