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.
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"
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.
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 joinline 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.
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.
