Creating a Query - Query Wizard

Locked lesson.

Upgrade

About this lesson

Use the Query Wizard to build different types of query and perform aggregation.

Quick reference

Creating a Query - Query Wizard

Use the Query Wizard to create a query that contains aggregation. 

When to use

Create a query that aggregates values whenever you want to perform basic calculations on table data. 

Instructions

Create a Query - Query Wizard

  • From the Create tab, in the Queries group, select Query Wizard. 
  • Select Simple Query Wizard from the list. 
  • Click Next. 
  • Select ‘tblTicket’ from the drop-down list. 
  • Select the ‘TicketNumber’ and ‘CustomerName’ fields and add them to the Selected Fields area using the chevron. 
  • Select ‘tblTicketCompany’ from the drop-down list. 
  • Select the ‘Company’ field and add it to the Selected Fields area using the chevron. 
  • Click Next. 
  • Select Detail.
  • Click Next. 
  • Name the query ‘qryTicketCompanyQuery’.
  • Select Open and view the query. 

Create a Query with Aggregation

  • From the Create tab, in the Queries group, select Query Wizard. 
  • Select Simple Query Wizard from the list. 
  • Click Next. 
  • Select ‘tblTicket’ from the drop-down list. 
  • Select the ‘TicketNumber’, ‘CustomerName’, and ‘Charge’ fields and add them to the Selected Fields area using the chevron. 
  • Click Next. 
  • Select Summary.
  • Click Summary Options. 
  • Select Avg, Min, Max, and Count records in tblTicket. 
  • Click OK. 
  • Click Next. 
  • Name the query ‘qryTicketChargeSummaryQuery’
  • Select Open and view the query.
Login to download
  • 00:04 In the final lesson of this section,
  • 00:06 we're just going to run through how you can create a query using the Query Wizard.
  • 00:11 So let's jump straight up to Create and click on Query Wizard.
  • 00:16 Now the first thing you'll see when you open up this wizard is that we have four
  • 00:20 different types of query that we can create.
  • 00:24 Now, most of these are outside the scope of this basic course.
  • 00:28 So we're going to stick with simple Query Wizard.
  • 00:32 Let's click on OK.
  • 00:33 Now the first thing we need to specify are which fields we want to appear in
  • 00:37 our query.
  • 00:39 And we can choose from one or more tables.
  • 00:42 So I'm going to select our table Ticket.
  • 00:47 And I want to have the TicketNumber.
  • 00:50 Let's put that across to the right hand side.
  • 00:52 And the CustomerName from this table.
  • 00:56 And I also want to select from table TicketCompany, the CompanyName as well.
  • 01:02 Let's put that across.
  • 01:03 So you can really mix and match fields up across all of your tables.
  • 01:08 Let's click on Next.
  • 01:11 Now we can choose if we would like a Detail or a Summary query.
  • 01:15 So we're going to choose Detail, let's click on Next, and
  • 01:19 now I get to give my query a name.
  • 01:22 So we are going to call this qryTicketCompanyQuery.
  • 01:30 Do I want to open the query or do I want to modify the query design?
  • 01:35 Well, we're going to open the query.
  • 01:37 Let's click on Finish and there we go.
  • 01:40 There is the result of my query.
  • 01:42 So that is an alternative way that you can run a query.
  • 01:45 If you would prefer to use the wizard as opposed to dragging and
  • 01:48 dropping fields onto the grid.
  • 01:50 Now the final thing I want to show you in this lesson is aggregation.
  • 01:54 Now it might be when we are producing these queries we don't necessarily
  • 01:59 just want to list out ticket numbers or customer names or companies.
  • 02:04 Maybe we want to perform some aggregation.
  • 02:07 And we can do things like perform some calculations, average, mins,
  • 02:11 maxs, things like that when we're running our query.
  • 02:15 So let's take a look at an example of how that works.
  • 02:18 So let's close down this query.
  • 02:21 I'm going to select table Ticket in my navigation pane and
  • 02:25 then we're going to go to the Query Wizard.
  • 02:28 We're going to create a simple query again.
  • 02:31 And this time from table Ticket we're going to grab the TicketNumber,
  • 02:36 the CustomerName and also the Charge.
  • 02:39 So I'm adding in a numeric field here.
  • 02:43 Let's click on Next.
  • 02:45 Now instead of selecting Detail, I'm going to select Summary this time and
  • 02:50 then jump into Summary Options.
  • 02:53 And this is where I can choose if I want to perform some kind of aggregation.
  • 02:57 Now, of course, these are only going to work on numeric fields.
  • 03:00 So I've added a charge field which is a numeric field.
  • 03:05 So I could show a sum of the charges and average and a min and a max.
  • 03:09 And I can also do a count of the records interval ticket.
  • 03:13 Now if you're wondering why ticket number is appearing in here,
  • 03:16 that's because the datatype for the ticket number is set to number.
  • 03:20 So Access thinks that this could be a field that you want to aggregate.
  • 03:24 Now in this case, that doesn't really make sense to do that, so
  • 03:27 we're going to use the charge field.
  • 03:30 So I want to find out the average, the minimum and the maximum of the charge.
  • 03:36 And I also want to count the number of records interval ticket.
  • 03:41 Let's click on OK and next, and now we need to name our query.
  • 03:47 I want to open the query and let's click on Finish.
  • 03:50 And now check out what I have.
  • 03:52 Notice for each customer, I can now see the average of their charge,
  • 03:57 the minimum charge, the maximum charge and also a count.
  • 04:01 Now for me in the type of data that I'm using, because we only have one
  • 04:05 current ticket per customer, the average, min and max values are all the same.
  • 04:10 And also the count is the same as well.
  • 04:12 But if you're using a different data set that had multiple charges per customer,
  • 04:17 then you're going to see an average, a min and a max of those.
  • 04:20 But hopefully that gives you an idea as to the kind of
  • 04:24 functionality that you can add in when you use the Query Wizard

Lesson notes are only available for subscribers.

Gift this course