Creating a Query - Query Design

Locked lesson.

Upgrade

About this lesson

Creating your first query to search through records in a database.

Quick reference

Creating a Query - Query Design

Create a simple query using Query Design to search for records based on specific criteria. 

When to use

We create a query whenever we want to perform a fast search for records based on criteria we specify. 

Instructions

Simple Query - Query Design

  • From the Create tab, in the Queries group, select Query Design. 
  • From the Add Tables pane, select ‘tblTicket’ and click Add Selected Table. 
  • Drag the ‘TicketNumber’ field from ‘tblTicket’ into the grid.
  • Change the Sort to Ascending. 
  • From the Query Design tab, in the Results group, click Run. 
  • Switch to SQL View and review the code. 
  • Close the Query. 
  • Name the Query ‘qryTest’. 

Creating a Query Using Multiple Fields

  • Reopen 'qryTest'.
  • Switch to Design View. 
  • Drag the ‘CustomerName’ field from ‘tblTicket’ into the grid.
  • From the Query Design tab, in the Results group, click Run. 
  • Review the SQL Code in SQL View 
  • Switch to Design View. 

Sorting Query Results

  • From the 'Customer Name field in the grid, click the drop-down arrow next to Sort. 
  • Select Ascending or Descending to sort by that field. 

Adding Criteria

  • Drag the ‘Priority’ field from ‘tblTicket’ into the grid.
  • Add the Criteria, ‘Moderate’. (Criteria needs to be in quote marks "")
  • From the Query Design tab, in the Results group, click Run.  

Adding Fields from Other Table to the Query

  • From the Add Tables pane, select ‘tblStatus’ and click Add Selected Table.
  • Drag the ‘Status’ field from ‘tblStatus’ into the grid.
  • In the Criteria field, type 'Active' (Criteria needs to be in quote marks "")
  • From the Query Design tab, in the Results group, click Run.  

AND/OR Conditions

When we add two or more pieces of criteria, the Query will run a search using the AND condition. We can change this to run an OR condition. I.e. instead of returning tickets with a priority of high AND a status of Active, we can return tickets with a priority of high OR a status of Active. 

  • Switch to SQL View. 
  • Replace the word AND in the code with OR. 
  • From the Query Design tab, in the Results group, click Run.  

Hints & tips

  • To run a query that includes all fields from a table, drag the asterisk (*) from the table to the grid.
  • If we do not specify a sort order, the default is ascending. 
Login to download
  • 00:00 In this section of the course, we're going to be taking a look at queries.
  • 00:08 And queries are effectively searches that
  • 00:12 you can perform on fields in one or more tables.
  • 00:16 So why would we want to create a query?
  • 00:19 Well, over time your database is going to grow.
  • 00:23 And in the example of this database,
  • 00:26 it could be that we end up with hundreds or even thousands of tickets.
  • 00:31 And as your database grows, it gets harder to search for specific things.
  • 00:36 The time is going to take longer, the larger the database is.
  • 00:40 So what we can do is create queries which will help us interrogate our data and
  • 00:45 pull out of it exactly what we need in the most efficient way possible.
  • 00:50 So in this first lesson, I'm going to show you how you can build a simple
  • 00:55 query using query design, so let's jump up to the create tab.
  • 00:59 And we have a queries group, we have query wizard and query design.
  • 01:05 Now, we're going to look at the query wizard in a couple of lessons time.
  • 01:08 Let's first start out by taking a look at the query design area.
  • 01:12 Now I'm going to close down the property sheets, we don't actually need that and
  • 01:17 what we have here is the query window.
  • 01:19 Now currently we don't have any tables added to our query window,
  • 01:23 we're going to add tables in a moment, and then underneath,
  • 01:27 we have what we call the QBE the query by example pane.
  • 01:31 And this is more commonly referred to as the grid.
  • 01:34 Now essentially, what we can do here is we can add one or
  • 01:38 more tables to the upper half of this screen, and then we can drag and
  • 01:42 drop fields down to the grid, to effectively create search criteria.
  • 01:47 Now notice that when I clicked on query design,
  • 01:49 it automatically popped open the add tables pane on the right hand side.
  • 01:53 If you go into query design and it looks like that, all you need to do is go up
  • 01:58 to the query design ribbon and click on the add tables button.
  • 02:02 Now we're going to run a query on table ticket and
  • 02:05 it's going to be a very simple query.
  • 02:08 So let's select the table and add selected tables,
  • 02:12 lets drag this down so we can see all of the fields.
  • 02:15 Now first off, we're just going to create a very basic query.
  • 02:19 And all I want this query to do is return a list of all of the ticket
  • 02:24 numbers in my database.
  • 02:26 So I'm going to grab the ticket number field from the table, drag and
  • 02:30 drop it down onto the field area in the grid.
  • 02:34 So you can see here field is ticket number, table is table ticket.
  • 02:38 Let's run the query, if we jump up to the query design ribbon in
  • 02:43 the results group we have a big old run button.
  • 02:46 So let's click it to run that query, and
  • 02:49 there we go it's just producing a list of all of the tickets in my database.
  • 02:54 Remember this is the tickets, not the related tickets.
  • 02:58 And I could run a simple query like this on anything, if I wanted
  • 03:01 a list of all of the companies or all of the customers I could do that as well.
  • 03:05 Now we're currently looking at this query in datasheet view.
  • 03:11 Now notice that once we've run a query we get an additional view that we can switch
  • 03:16 into and that is SQL view.
  • 03:17 And this really shows you the underlying code that produces those query results.
  • 03:23 Now don't worry too much if you don't know a great deal about code this is fairly
  • 03:27 simple to understand and
  • 03:29 it's just saying select the ticket number from table ticket.
  • 03:32 Now we're going to close this query, we're going to save it and
  • 03:36 we're just going to save it as query test.
  • 03:38 So again, sticking to my naming convention, a QYY test, and click on ok.
  • 03:44 Notice that we have a new queries group over in the navigation pane,
  • 03:48 that's going to house all of the queries that we create.
  • 03:51 Now let's reopen our query I'm going to jump back into
  • 03:56 design view and we're going to add in another field.
  • 04:01 So maybe this time I want to see the ticket number and the customer name.
  • 04:05 So, let's drop this down into the next column,
  • 04:10 let's run that query and that's what we get.
  • 04:14 If we jump in and take a look at the SQL code, you can see now it's
  • 04:19 selecting ticket number and customer name from table ticket.
  • 04:23 So really straightforward to understand.
  • 04:26 Now, if we go back into design view, if I wanted to run a query that includes
  • 04:32 all of the fields in the table, I would simply drag this asterixis to the column.
  • 04:38 And then we're going to get a result that shows all of the fields.
  • 04:41 Now I actually don't want to do that, so I'm going to delete that out.
  • 04:45 Now when we're looking at this grid we can see that the first row defines the field
  • 04:51 the second row defines the table and the third row defines the sort order.
  • 04:55 So if I wanted to sort the query results in a specific way, I could click
  • 05:00 the drop down and maybe I want to sort by the customer name in ascending order.
  • 05:06 And it's worth noting that if you specify nothing here,
  • 05:09 it's going to do an ascending soul.
  • 05:11 Let's run the query again and
  • 05:13 now you can see that those are sorted alphabetically A to Z.
  • 05:18 If we take a look at the SQL code notice we now have a third statement added in,
  • 05:24 so now it's selecting ticket number and customer name from table ticket,
  • 05:29 and it's ordering it by the customer name.
  • 05:33 Now what I could do here,
  • 05:34 is if I decided that I wanted to order this in descending order instead,
  • 05:39 what I could do is click on the end here, press the space and type in DESC.
  • 05:44 Run the query again and now it's switched those around.
  • 05:48 Let's go back in to our query, now notice that we have a show row underneath sort.
  • 05:55 So that is allowing us to define if we want to actually
  • 05:59 show the results in the query.
  • 06:02 And I do in this case, and then we have a criteria row.
  • 06:06 Now for this, I'm actually going to add in another field.
  • 06:10 So we're going to add priority into this third field, and what I'm
  • 06:14 going to say here is that I only want to show tickets with a certain priority.
  • 06:19 And that priority is going to be high and that needs to go in quote marks.
  • 06:25 Let's run the query.
  • 06:27 And now take a look at the results that I'm getting.
  • 06:30 Let's right click and take a look at the SQL view.
  • 06:33 We have another statement added in and that is aware statement.
  • 06:38 So it's going to select ticket number, customer name and
  • 06:41 priority from table ticket, where the ticket priority equals high and
  • 06:45 then it's going to order them in descending order.
  • 06:48 Let's jump back into design view again,
  • 06:51 now I can also add fields from other tables.
  • 06:54 So I'm going to add the status table and
  • 06:57 now I can use these fields in my query as well.
  • 07:00 So let's drag status down into this field just here and
  • 07:04 I'm going to add a status, let's add some criteria of active.
  • 07:10 Let's run this query and there we go I get my results right click and
  • 07:14 take a look at the SQL, you can see here it says where the priority is high and
  • 07:20 the status is active.
  • 07:22 Now what about if I wanted this as an OR condition instead of an AND, so
  • 07:26 I want to produce results where the priority is high or the status is active.
  • 07:32 Now what I could do here is change this to an OR statement.
  • 07:35 So I'm going to replace where it says AND with OR, and now it's going
  • 07:40 to produce results where the priority is high or the status is active.
  • 07:45 So let's click on run, and now I get a much longer list.
  • 07:50 And if we take a look in design view,
  • 07:52 notice what has happened here with us doing that.
  • 07:56 We now have active in the OR row and high in the Criteria row.

Lesson notes are only available for subscribers.

Gift this course