access presentation 3 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Access Presentation 3 PowerPoint Presentation
Download Presentation
Access Presentation 3

Loading in 2 Seconds...

play fullscreen
1 / 32

Access Presentation 3 - PowerPoint PPT Presentation


  • 138 Views
  • Uploaded on

Access Presentation 3. By: Rasagnya Waghray Ali Murtuza. Overview. Create Queries Using Query Wizard Using Design view Example Criteria Wild cards Using relational operators in criteria Using compound criteria. Overview (cont). Omit duplication from query result

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Access Presentation 3' - oprah


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
access presentation 3

Access Presentation 3

By:

Rasagnya Waghray

Ali Murtuza

overview
Overview
  • Create Queries
    • Using Query Wizard
    • Using Design view
    • Example
  • Criteria
    • Wild cards
    • Using relational operators in criteria
    • Using compound criteria
overview cont
Overview (cont)
  • Omit duplication from query result
  • Main query types
    • Select Query
    • Update Query
    • Delete Query
creating query from wizard
Creating query from wizard
  • Queries are simply questions , the answer to which are in database. For creating a query you must have already defined your tables and entered data in your tables. The example database used in this manual is the student database we created in “Access presentation 2”.
  • For creating query from wizard, click on the “Create” tab then click on “query wizard”. Then in the window that pops up, choose “simple query wizard” option. Click “ok”.
slide5

Select the table you want to have query from and add the fields that you want to show in your query, click on next and “finish”.

  • If you need to make any changes to the query, right click on the query in navigation bar and click on “design view”
slide6

Query wizard

Select the table and the fields you want to add for the query

Step1: Choose simple query wizard

creating query in design view
Creating query in Design View
  • Another way to create a query is to click on the “create “ tab and click on “query design” button . Most of the time you will use design view to create queries.
  • A “show table” dialog box appears which shows all the table in the database. Select the tables that you want to query from and hit “add” button . After adding all the tables you want, click on the “close” button to close the show table dialog box.
slide8

Hit close after you selected your tables

Design Grid

Add the tables you want to use for your query

creating query in design view cont
Creating Query in Design view (cont)
  • When creating queries , you can request for specific records and accompanying fields to be shown based on certain criteria.
  • In Microsoft Access, there are six areas that can be utilized in creating a query.
    • Fields: Used to specify which fields should be a part of the query : for adding a field, you need to double clicking on the field name in each table.
    • Table: Used to specify the table that houses the fields that will be a part of the query.
slide10
Cont..
  • Sort: Used to specify how the records will be sorted when the query is run. It has two options: Ascending and Descending, you can sort the query result based on the related field.
  • Shows: Used to indicate whether a field involved in the query should be shown in the results or not, it is useful when you want to have a criteria /condition on a field but you do not want to show the field in the query result
  • Criteria: Is very important row and indicates the condition on the related field
  • Or: Indicated compound criteria
example
Example
  • As an example suppose we want to design a query that gives us the first name, last name, and GPA of the students whose majors are “Education”, and we want the results to be sorted by student’s first name. For doing that we need to do the following steps:
    • Double click on the “FN”, “LN”, “GPA” and “MAJ” fields on the student table to add these fields to the design grid.
    • In the “sort” row, under the FN field, choose ascending.
    • Uncheck the “show” checkbox under the MAJ field
    • In the “criteria “row under MAJ field, type “education”
    • Save the query and give it a name, e.g. “query1”.
    • Click on the “run query” button to see the query result.
slide13

Query result

You should switch to the design view of the query if you want to make changes to the query.

criteria
Criteria
  • As mentioned before , the criteria is a row where you specify your conditions on different fields.
  • Use comparison criteria: Unless you specify, access assumes that the criteria you enter involve equality(exact match). In the last query for example the criteria was actually “=Education”. If you want something other than exact match you must enter appropriate comparison. For example if you want to change the criteria of the previous example to see the students whose GPA is greater than 3.00, you write “>3.00” in the criteria row, under the GPA field.
using wildcards for criteria
Using Wildcards for criteria
  • Wildcards are symbols that represent any characters or combination of characters. It’s a pattern that specifies what will be matched. Wildcards use special characters to specify the types of information to match. The common wildcards are :
    • Asterisk (*), which represents any collection of characters. For example “El*” represents the letters EI followed by any collection of characters. Examples of * are:
cont of examples
Cont.. Of examples
  • Like “s*” : Matches all the values that start with “s”.(eg- “Smith”,but not “Davies”)
  • Like “*s” : Matches all the values that end with “s”.(eg- “Davies”, but not “Smith”)
  • Like “c*n” : Matches all the values that start with “c” and end with “n”.(eg- “Cohen”)
  • Like “c*a*n” : Matches all the values that start with “c”, have an “a” somewhere in the middle and end with an “n”.(eg- “Cohan”)
  • Like “c*an” : Matches all the values that start with “c” and end with “an”.(eg- “Cohan” but not “Cohain”)
example18
Example
  • As an example query that uses wildcard, suppose that we want to see the students whose last name ends in “n”. As soon as you write wild cards in criteria row, access adds “like” keyword automatically.
cont of wildcards
Cont.. Of Wildcards
  • Question mark(?): which represents any individual character, for example T?M , represents the letter T, followed by any single character followed by letter M. ( such as TOM or TIM).Examples:
  • Like “a??” : Matches “ape”, “aaa” but not “apple”
  • Like “*a?” : Matches “bread” and “lean” but not “apple” and not “fella”.
cont of wildcards22
Cont.. Of Wildcards
  • Number Sign(#): which matches a single numerical digit. Examples are:
  • Like “###abc” : (there are three number signs)

matches “123abc”

And “456abc”

But not “a123abc”

And not “abc123”.

compound criteria
Compound Criteria
  • We can have more than one criteria for a query. There are two types of compound criteria
    • And Criterion: Each individual criterion has to be true in order for the compound criterion to be true. To combine criteria with And, we should place them in the same row ( in criteria row).
    • OR Criterion:At least one of the individual criterion has to be true in order for the compound criterion to be true. To combine criteria with OR, we should place them in separate lines.
compound criterion example
Compound criterion Example
  • As an example of And criterion suppose the query is to show the First Name, Last Name, major and GPA of the students whose Last Name begins with “S” and/or their GPA is greater than 3.00.
slide27

And query result

Or query result

query types
Query Types
  • Until now we just mentioned the queries that filter the table, this type of query is called “select” query. Select query does not modify the content of table
  • There are two other types of query that modify the content of tables in database. These queries are:
    • Update queries
    • Delete queries
delete query
Delete Query
  • Deletes some records from a table that satisfy certain criteria.
  • For creating a delete query, click on the “delete button” in query type group. Then select the field that you want to have criteria on, and specify your criteria.
  • As an example, delete all students whose Major is “education”. When you run this query a window pops up and asks you to confirm deleting 2 records.
slide30

Click on Delete button

Specify the criteria

update query
Update Query
  • Updates the records that satisfy certain criteria.
  • For creating a delete query, click on the “update button” in query type group. Then select the fields that you want to update, specify the new values that these field should be updated to and specify the criteria.
  • As an example, update the records of all students whose major are “Education” and their GPA is greater than 3.0 , change their major to “edu”