microsoft office access 2013 tutorial -basic 3 intro to queries

PowerPoint Presentation

Introduction to queries (Access basics, part 3)

then press F5 or click Slide Show > From Beginning to start the course. In the message bar, click Enable Editing,

If the videos in this course dont play, you may need to download QuickTime or just switch to PowerPoint 2013.

1

Introduction to queries (Access basics, part 3) Closed captions

Press F5 to start, Esc to stopSummaryFeedback Help The DesignerMulti-sourceOuter joinsUnrelated sources4:454:452:504:455:24Introduction1/5 videos

Now that weve built tables and relationships, its time to design and build some queries,and these are another essential piece of any database.Queries are literally questions you ask of your data,and in fact, asking the question you want answered can be a good place to start.For example, How many orders did Tailspin Toys place last month?That language in that question points you to the Orders table,and from there, you select the fields you want to see, build the query, and then run it.And thats the basic process or flow for most queries.You ask your question, and then you pick a data sourcethe tables or queries with the data that will answer that question.From there, you pick the fields you want to see in the result (which is also called a dataset, by the way),and add any criteria.In this example, the criteria filter the results to Tailspin Toys for the month of May,but criteria can do a lot more than that, and well look at them in the next video.And you can use queries for more than just answering questions.Theyre like a multi-tool or combination pliers; they can be one of the handiest tools in any database.For example, they can provide the data for forms and reports, and they can also make bulk changes to your data.Now, you can build several types of queries in any database, and to show you what you can do in Access,Ill select Create, then Query Design.Ill close the Show Table dialog because I want you to see these commands. These are the types of queries you can create.Youve already seen me build a Select query. Theyre the most common, and theyre how you get answers out of your data.A Make Table query lets you select records from an existing table and save them as a new table.That can be handy, say, if you import a big Excel file and you want to break some of that data into separate tables.Now, you run these next 3 queries against existing data.Append queries add data to existing tables, Update queries change existing data,and Delete queries do just that, they delete data.You use a Crosstab query when you need to display summary data such as averages or sums.For example, if you need to see sales by region, you use a Crosstab query.Heres what one looks like in the query designer, and the results look like this.You can see this one returns sales data per quarter.A Union query lets you combine multiple Select queries into a single result,and this is something you do when you need to see data from unrelated sources.Pass Through queries let you connect to a large database,such as Microsoft SQL Server, and process queries on that database.When you do that, Access becomes a front end, a set of tools that helps you run the larger database.And finally, Data Definition queries let you create or modify tables and indexes.The thing is, Access provides so many other ways to create tables and change indexes,youll probably never use this type of query.So for the rest of this course, Im going to concentrate on Select queries, because theyre the ones youll use the most.And to build one, Ill go to the Create tab and select the Query Wizard.If youre new to queries, this is a nice place to start. Choose Simple Query Wizard, and the Phone Numbers table.Ill select the Customer and Phone Number fields, choose Next,change the name if I want to, display the results, and select Finish.Theres the result, but you probably have a couple questions.For example, what kind of query did you just create,and how do you know whether youre calling someones office phone or their fax machine?You can answer those questions by adding some data to the query,and to do that, select Home, then View, then Design View. This starts the Query Designer.The designer shows you the data sourcein this case its a table,but it can also be a queryand the fields involved in the query.The ribbon will always tell you the type of query, and in this case you can see its a Select query.To add the Type field and make the phone list easier to use, you just drag and drop,and to see the results, you select Run here on the Design tab.Now, you can only do so much with the Query Wizard.For example, it doesnt let you add formulas, so for the rest of this course Ill use the designer.And next, Ill show you how to build a select query from scratch with the designer,and how to add some basic criteria to help you answer your questions.

5761234Course summary8Help


Press F5 to start, Esc to stopSummaryFeedback Help 2/5 videos

In this video, Ill use the Query Designer to create a Select query from scratch,and well follow the process I described in the previous video.Ill select a data source, fields from that data source, and as needed, well add criteria to filter the results.As a demo, lets answer the question from the previous video: How many orders did Tailspin Toys place last month?On the ribbon, choose Create, then Query Design. That starts the designer.You can use the Show Table dialog to add tables or queries as a data source,or you can just drag a source over from the Navigation pane.For this example thats the Orders table, and to answer our question, we need customer names and order dates.You can double-click to add a field to the design grid down here, or you can drag and drop.So if I run the query now, you can see it returns every order for every customer,and I can use the filtering tools here to narrow the results.But, if you add criteria to the query, you can get the same result every time.Thing is, adding criteria gets a little more complicated, so you need to remember a couple rules.And the first rule is: You need to know your data.To show you what I mean, Ill open the Orders table, select Fields on the ribbon, then the fields in our query.You can see Order Date is a Date/Time field, but Customers isnt a Text field, its a Number field.And that leads to the second rule: Your criteria has to match the data type of the field youre filtering.For instance, you can only enter date values in a Date/Time field, numbers in a Number field, and so on.So how do I know which number corresponds to Tailspin Toys?Ill start by going to Database Tools, Relationships,and I see Customers and Orders are related by the ID and Customer ID fields.So I open the Customers table, locate Tailspin Toys, and I see it has an ID value of 23.So back to the query, and Ill enter 23 in the Criteria row of the Customer ID field.Then Ill expand the Order date field,and add a pair of logical operators, BETWEEN and AND, and Ill put a starting date here, and an ending date here.And notice I surround the date values with pound signs.Access requires those because they identify the value as a date, and not text.So run the query and there you go. Just the orders placed by Tailspin last month.Now lets look at the query designer for a minute. Do you see these check boxes?If you clear them, you hide your field from the result, like so.The field is still in the query, you just dont see it in the results.Also, if you want to return a portion of the result,such as the top 5 values or maybe the bottom 25 percent of a data set, select a value from the Return list.Then here in the design grid, go to the Sort row and select Ascending or Descending.Ascending order returns the bottom items, and Descending returns the top items.So next, look at the Totals button?This is a fast way to add sums, averages and other calculations to a query, and here's an example.You can see we have three tables and a field from each,and if I run the query as it is now, we get a lot of repeated categories.So back to design view, click Totals, and notice that Access groups all the records in the query.Run the query again, and now it calculates the number of items sold in each category.Sort the results, and you can see which categories sell the most or the least.So next, Ill show you how to create a Select query that uses multiple data sources.That involves understanding a database component called a join, so keep going.

The DesignerMulti-sourceOuter joinsUnrelated sources4:454:452:504:455:24Introduction



Press F5 to start, Esc to stopSummaryFeedback Help

3/5 videos

Building a select query that uses more than one data source follows the same basic stepsas a query with a single data source.You start the query designer, select your tables or queries, and add your fields.But, theres a twist here: When you query multiple sources for data, you need to create a match between each source.For example, if you want to know which products are in a set of orders, you have to match the orders with their products.To create those matches, you use whats called a join.A join compares the data in two common fieldsone from each data sourceand it controls how the data in each field is matched.For example, the most common type of join only returns data from one field when it finds a matching value in the other and this is how you find out the number of products in each order, for example.And another type of join returns all the data from one field, but only matching data from the other.And in this example, the querys telling you which orders dont contain a given product.Now, when you created your table relationships, Access also created a join for each relationship.Access uses your relationships as patterns for joins,so most of the time, your joined fields will be your primary and foreign keys.But they dont have to be. You can join unrelated sources, and Ill show you how later in this course.But if you do have two related sources, and you add them to the Query designer, you see what looks like the relationship.Just remember that it isnt.Access is just telling you the type of relationship it used as the pattern for the join.And, as long as you have a join between your tables or queries, you can add fields from both sources.And notice, too, that I didnt add the joined fields to the design grid.You dont need to, because Access adds them behind the scenes.You can run the query and get a result.So lets go back and look at joins some more, because you need to remember a few facts.First, you can set or change the type of join by right-clickingor double-tapping the join line and selecting Join Properties.That starts the Join Properties dialog, and these settings control the type of join in your query,and these are three most common types of joins.This option creates an inner join.This is the type of join that returns matching data from both tables,and Access creates this type of join whenever you create a relationship.The second option creates a left outer join,which means that your query returns all the data from the table listed on the left,and only matching data from the other table.The third option creates a right outer join, and you can probably guess what happens.The query returns all the data from right-hand table, and only matches from the left.You typically use outer joins when you want to know what hasnt sold,or (more generically) when something hasnt happened.Now, I said earlier that these are the three most common types of joins.You can also use cross joins and unequal joins, but you wont use them very often, so I wont cover them here.If you want to know more, see the links in the Course Summary.And in addition to the types of joins, you also need to remember that relationships and joins arent the same thing.A relationship is a set of rules that controls referential integrity, how you add and delete data.Joins control how your query matches data.But the two can be kind of similar. For example, your relationships can have sides, one and many,and, your joins can also have sides, left and right.But, you only use joins in queries, and you can use them in ways that you cant in relationships.For example, you can delete joins without harming your database, something you cant do with a relationship.Also, you can add joins where relationships dont exist.As an example, you can join a field in a table with a field in a query as long as those fields have a matchingor compatible data types, and Ill show you how to do that later in this course.But first, we need to take another look at outer joins, so keep going.




Press F5 to start, Esc to stopSummaryFeedback Help

4/5 videos

Let's look at how to use left and right outer joins in a Select query.And as a quick review, outer joins have sides, left and right,and they return all the data from one table, and only matching data from the other.So now lets add just a little bit to this.The table that returns all data is called the base table,and the table that returns matches is called the secondary table.Also, the base and secondary tables vary, depending on the direction of the join.If you use a right outer join, the right-side table becomes the base table, and so on.And remember, you control left and right by selecting and then either right-clicking or double-tapping the join line,then Join Properties, then choosing the second or third options.So let's put this to work. Create tab, Query Design, and I'll add the Order Details and Products tables.You can see Access used a one-to-many relationship as the pattern for the join, and that means it's an inner join.I'll add the Product Name and Quantity fields, and if I run the query,you can see it only returns data for products that have been ordered.I know this because If the query was returning data for unordered products, wed see blank records here.So back to Design view, right-click or double-tap the join line and start the Join Properties dialog.Select the second option, and notice the join line.The arrow tells you it's an outer join, and it also points to the secondary table.Run the query, sort the data from smallest to largest, and now you know which products don't sell.So what happens if you change to a right outer join?I'll make the change, run the query, and we get the same results as an inner join, no blank records.If we look at the Join Properties dialog, we see why.We're returning all the data from the Order Details table, but only matches from the Products table.In other words, we're only seeing products that have a corresponding order,and that's the same result returned by an inner join.So think your joins through, and if you dont get the result you want, you can always change them.And next, I'll show you how to add joins and how to use indirect joins,and those are two ways to query unrelated tables and get some logical results.




Press F5 to start, Esc to stopSummaryFeedback Help 5/5 videos

At times, you'll need to query tables or other data sources that arent related, that dont share a join,and you have two options for doing that.You can use an intermediate table, a table that shares joins with the data sources you want to use,or, if you dont have any intermediate tables, you can add joins.So I'll start with the intermediate table, and as an example, in this database,employees have to enter a status for each order, but sometimes they forget.The question you want to answer, then, is which orders dont have a status, and which employees forgot to enter them?On the surface, it looks like we need to use three tables in the queryEmployees, Orders, and Order Details Statusbecause they correspond to the data points in our question:Which orders dont have a status, and which employees forgot to enter the status?So add them to the query, and you can see these two tables arent joined, but thats okay, right? I can add joins.So Ill join on the ID fields, run the query, and nothing.So now Ill do what I should have done in the first place and look at the relationships between the tables.When I do, you can see what looks like a chain of relationships hereEmployees to Orders,Orders to Order Details, and Order Details to Order Details Status.So Orders and Order Details Status arent related, but Order Details is related to them both.That makes Order Details my intermediate table.And in turn, that leads to a rule: As long as the intermediate tables are part of your data source,you can query these unrelated tables and get a valid result.You dont have to include the intermediate table in your results, but it has to be part of your querys data source.So Ill add the OrderDetails to the query, keep the fields the same, and run the query.Filter for blank values, and theres your answer.Now lets add a join to a query, and to do this, you need to keep some rules in mind:First, the tables or queries you want to join need to have fields with matching or compatible data types.And as an example of that, the Number and Currency data types are compatible, but Number and Text arent.Second, the join you create only resides in your query. Youre not creating a table relationship here.Third, the results should make sense.If the join returns data on orders and company cars, for example, you should probably start over.So as an example, lets say we need to know which customers ordered which products.Ill add Customers, Orders, and Order Details to a new query.Now Ill add the join. Ill drag Customer ID from Customers here to the matching field in the Orders table.Theres the join line, and heres another rule: Access places the field you drag on the left side of the new join.If that isnt what you want, select the join line, press Delete, and start over.Now that we got that taken care of, we can build the query.Ill add FirstName and LastName from Customers, then ProductID, OrderID, Quantity, and UnitPrice from OrderDetails.Run the query, and theres your data.If you're wondering, it would have been easier, and more logical,to create a table relationship between the Customers and Orders tables.If you don't remember how to do that, see the second course in this series,Creating relationships between your tables.And from here, you continue building your database by creating forms and reports.Forms can make data entry easier, faster, and a lot more accurate,and they also give your database a polished look and feel.Reports are how you present your information visually, where you use charts and graphs.For information on creating forms and reports, see the links in the Course Summary, and thank you for your time.Thanks for taking these courses.



HelpCourse summary

Press F5 to start, Esc to stop

SummaryFeedback Help

57612348

Course summaryIntroduction to queries (Access basics, part 3)

The DesignerMulti-sourceOuter joinsUnrelated sources4:454:452:504:455:24IntroductionThe basic processStart with a question, identify tables or queries with data that answers the question, create query, add data sources, fields, run. Use the Query WizardCreate > Query Wizard.Select a query type > OK.Select a data source (tables or queries), select your fields > Next. Enter a name, select an option to open or modify > Finish Use the query designerCreate > Query Design.Use the Show Table dialog to add tables or queries.Double-click/tap, or drag and drop fields to add to the design grid.Query multiple sources If you use related tables, theyre joined. Joined fields in unrelated sources must have matching or compatible data types; results should make sense.Left outer join: All the data in the left-hand table, only matches from the right. Right outer join is reverse. To add a join: Drag and drop.To create the query: Add joined sources, add fields, run.Intermediate tables If two unrelated tables are both related to the same table, add that 3rd table to the query, then query the unrelated sources.See alsoIntroduction to queriesCreate a select queryJoin data sources in a queryApply criteria to a queryCreate an Access formIntroduction to reports in AccessIntroduction to expressionsMore training coursesOffice Compatibility Pack

Check out more coursesHelpCourse summary



57612348

Rating and commentsThank you for viewing this course!

How did we do? Please tell us what you think


HelpCourse summary



57612348

Help

If you download a course and the videos dont playClick Enable Editing if you see that button. If that doesnt work, you may have PowerPoint 2007 or earlier. If you do, you need to get the PowerPoint Viewer. If you have PowerPoint 2010, you need the QuickTime player, or you can upgrade to PowerPoint 2013.Using PowerPoints video controlsPoint at the bottom edge of any video to start, stop, pause, or rewind. You drag to rewind. Going placesYou can go to any part of a course by clicking the thumbnails (light or shaded) below the video. You can also click the forward and back arrows, or press Page Up or Page Down. Stopping a courseIf youre viewing online, click your browsers Back button. If youre viewing offline, press Esc. If youre watching a video, press Esc once to stop the video, again to stop the course.


microsoft office access 2013 tutorial -basic 3 intro to queries

Data & Analytics