The link is in the description as well as always. If this course has exercise files, you'll find them in the video description below. So welcome to module two queries forms and reports. We have, um, different lessons in this module as we did in the previous one. So we're gonna get started by creating commonly used queries in a couple of different ways. You're gonna be introduced to the query wizard and also how to create a query in design view. After that we're gonna move on to customizing queries with criteria..
So you'll learn how to add criteria to queries, to limit the results, and also how to use wild cards in criteria. We'll then move on to using forms for data entry. You're gonna explore the form wizard, um, form design view, exploring all of the form layouts and actually using a form to perform data entry. Our last section in this module is creating reports for data output. So you'll learn how to base reports on queries, how to preview and print.
Them, understanding report design view, and also how to format reports. We're gonna use one, uh, dot P and G file. During this it's in the video description, it's called the learn it logo. If you wanna go ahead and grab that. So it was useful to have overlapping windows when we were cleaning up stuff so that our relationships would work. But now we wanna change it back to tab documents. So before we get into our queries, we're gonna go to file tab and then options, and it's on the current database and we're gonna just do the option.
Button for tabbed documents and okay. And we're gonna click okay again. So we have to close our database and then reopen it for that change to take effect. So I'm just going file close and then file. And there's my database sitting right there. So the next thing we're gonna do is let's open the customer's table by double clicking it in the navigation pan. So when we created this table, we made the employee field a number field because the employee ID field and the employee's table as an auto number field..
So this time we get a message saying you can't change the data type or field size of this field because it's part of one or more relationships. So you would have to delete the relationship. First, we're gonna click okay. On that message. And if you write, click the customer table tab, you're gonna go ahead and close it. You have to make sure the table is closed in order for you to impact a relationship that that table is in. So we're gonna say yes, if it prompts you to save your changes, we're gonna go to our database tools, relationships..
And the relationship we have to delete is between the customers and the employees table. So I'm gonna just select that join line and I can press delete on my keyboard and then enter to confirm the deletion. I'm gonna close my relationships window and reopen the customer's table in design view. And now I'm gonna do the drop down select look up wizard. Gonna choose next. We wanna bring in information from the employees table..
Next we wanna bring in the last name and we're gonna do next again. We're not gonna do a sort order. We have the list of last names showing there. We're gonna next again and finish. So again, because you're making it a look up field, it's gonna create a relationship. What it's not gonna do is enforce referential integrity. So we're gonna save the table. We're going to close the table, go back to our relationships window and notice.
You have your join line, but you don't have the one or the infinity symbol on it. So we're gonna select the join line, right? Click on it, edit relationship, enforce referential, integrity, and cascade update related fields and click. Okay. And then we can close our relationships window again. If we double click the customer's table, we'll see that the last name based on the employee ID numbers is now populating that column and.
We can close the customer's table. Now we're ready to create the first of two queries using the simple query wizard. So we're gonna use the create tab of the ribbon and in the queries group, we're gonna select query wizard. So you'll notice there are a few different kinds of wizards. We're gonna use the simple query wizard. You have a cross tab, query wizard, a fine duplicate query wizard and a fine unmatched query wizard. We're gonna click, okay. Because simple query is already selected and it wants to know what.
Posts Related:
Fields do you want in your query. So we're gonna select our table or query.
You can query a table or a query. So we want to select for our first one, we're gonna select the products table and we are gonna want to see the product ID, field, product name, and I'm just double clicking them. Uh, let's see, the next one is gonna be price. And then we want the category ID. Those are the fields we want in our query..Once we have our field selected, we're gonna choose next. And so do you wanna detail or a summary query for this one? We'll do a detail query. So we'll do next. And what title do you want for your query? So the title is gonna be what the name of the query is, and it's also gonna show on the query itself. So we wanna change the TBL because it's not a table. Now it's a query. So we wanna stick with our correct prefix. So we're gonna do Q R Y for query, and then I'm gonna get rid of the space..
And actually we're gonna just get rid of the word query at the end. So QR Y products, plural, and then we're gonna click on finish. So this is what your query looks like, right? It shows Q R Y products on the tab and also over here in your navigation bar and also in your navigation pain, you now have the query's object group, so simple query using the wizard, and we're gonna close that query and we're gonna do another. So we're gonna go back to query wizard and we're gonna use.
The simple query wizard again. So, okay. This time we want to use the customer's table and the fields that we want are company name, and we're gonna put the phone field next. Then we want address one city, state and zip. Oh. And then we want employee. So that's the fields we want on this particular query we're gonna do next. And what title do we want for it? We're gonna just change it from TBL to Q R Y again, and get rid of the word query..
So Q R Y customers. And because of that option button, it's gonna open the query as soon as you click, excuse me, as soon as you click on finish. So there's the results. And of course here you can do your column widths and seeing the data presented in the order that you want to see it. And because the employee idea is a look up field to the employee, last name that shows in the query as well. And we can close that query and say, yes..
So layout change includes, um, column width. So go ahead and say yes to that. Now we're ready to create a query from scratch from query design view. So let's do this first. Let's right. Click on your products query in a navigation pain, and let's go to design view. So if we had created this from design view, this is the view that we, we, we would have been working in. Let's go ahead and close that query..
And in your navigation pain, you're gonna write click on the products query and you're gonna delete it. And we're gonna say yes to the message to confirm the deletion. And we're gonna recreate that query from scratch without using the wizard. So what we're gonna do is we're gonna go to the create tab of the ribbon and this time in the queries group, we're gonna go directly to query design and it brings us a query one tab, right?.
It has a blank space and then a grid at the bottom that you can resize the, the height of the grid and on the right, you have that add tables pan. So we can figure out which tables we want. So in the add tables pane, I'd like you to go to the tables tab up top, cuz it was on the queries tab. When we came in here and we're basing this on the products table. So I'm gonna click on the products table and at the bottom add selected tables and I'm gonna expand the products table tile, just so I can see all.
Of the fields and the fields that we included in the query were product ID, product, name, price, and category ID. So in the, the table products, tile, I'm gonna double click product ID and notice that it fills the first column, giving it the field name and the table that it's, that field is coming from the next field that we want is product name. So I can just double click that. Next is price..
And then category ID is last.
So we just CRE recreated this query from scratch inquiry design view. Now we're gonna save the query. So I'm gonna right click on the query tab and choose save, and we're gonna call it Q Y products and click. Okay. So there's a couple of ways I can see the results of this.Query on the query design ribbon. The first button is view, and I could go to data sheet view if I wanted to, or I can run the query, so I'm gonna run it. And it takes it to data sheet view. So we got the same information. Again, we just built it from scratch and we can close the products query. Now we're gonna customize our customers query with criteria. So we're gonna open the customers query in design view..
And even though we created it in the wizard, you know, it it's created in every view, including SQL view. So in design view is where we're gonna add the criteria. So the first criteria is we wanna see customers that live in a specific zip code. Right. So if we look down at the grid portion, we have our zip column. And in that column, underneath the check mark, the next cell.
Down is the criteria cell. And this is where we're gonna put our criteria. So we're gonna type an equal sign in there. And then we're gonna put in the zip code, 97, 45, and we're gonna tab out of that field. And the reason why we wanna tab out of that field is because notice it put quotes around the zip code. So if we forget that it has to be enclosed in quotes, it will do it for us when we exit the field. So we're telling it now that this query is only gonna show customers.
In that particular zip code, let's run it and see what happens. And it worked TA da. So we have our two customers from Oregon city, Oregon showing in this query, let's go back to design view, and we're gonna delete that criteria that we put in for the zip. So just select it, including the equal sign and delete it. and now we want to see customers who are in any state that begins with the letter..
I, so for the state criteria cell underneath the check mark, we're going to use a wild card in this one. So this is kind of like an introduction to wild cards. So we're gonna do an eye, an asterisk and tab out of that criteria cell. So we typed an eye and an asterisk. The asterisks means any number of characters. And when we tapped out of the cell, it put the ion asterisk and double quotes and it proceeded it with the keyword..
Like, so access will give you some help when you're doing these things kind of like typing shortcuts. You just have to remember to exit the cell. And so now we're gonna run this and see what our results look like. And so we have six customers that live in a state that begins with the letter. I, we can go back into our query design. So typically. Uh, and we can go ahead and delete that criteria from the state field..
So typically what I would do is I would do these in multiple queries. So I have my customers query. I have customers from states, beginning with the letter. I, I have customers in specific zip codes. I would do those as all independent queries. If I needed to retain all of them, we're gonna close the customer's query. And yes, we do wanna save the changes. So one way I would do that is I could always copy the customer query and then just right click anywhere. And I did that by right. Clicking on it, right. Click anywhere and choose paste so I can take the copy of it and do as.
Many, you know, do criteria on the copy, make another copy, do different criteria and name them accordingly. If I have to keep them, we're gonna just cancel. You'll be learning more advanced querying techniques in modules three and four of this course for right now, we're getting ready to start using forms for data entry. And before we start creating forms, I wanna point out something that happens automatically in your tables..
Once you create table relationships. So let's open the customer's table, I'm gonna just double click it. And you'll notice right after each row heading, there's a plus sign that happens automatically when you create a table relationship. So this is the customer's table, right? One customer can have many orders. So the plus sign shows up in the one side of the relationship. And when you click on the first plus sign, you'll see every order for that customer..
So this is another efficiency tool. Instead of having to open up the orders table, you can use the plus sign happens automatically. And then in the order table, you can see a plus sign in front of order ID and it gives you the order details. So the order's table, each order can have many order details. So that's the one side of that particular relationship. So that can play into your form designs. Um, again, you're typically not going to want to be doing data entry directly in your tables..
Um, you would want to be doing them via forms, and we're gonna go ahead and close the customer's table. Now we're gonna start creating forms that we're gonna be able to use for data entry. So we're gonna do, we're gonna create two forms using the form wizard, and then we're gonna create a form from scratch using form design view. Again, we're gonna be working on the create tab of the ribbon and you have a forms group here on the create tab. And in that group, you have several choices..
We're gonna select form wizard. That's the first one that we're gonna use. So in the form wizard, it's asking which fields do you want on your form? You can base a form off of a table or a query. We're gonna do the dropdown. And for our first form, we're gonna use the customer's table. And we want all fields except the customer ID field. So the most efficient way to do that is to do the double. Arrow in the middle between available and selected fields and then scroll to.
The top of selected fields, click on customer ID and do the single back arrow. And now we're gonna do next. So remember, customer ID is auto number. We don't need it to be on the form because it's not gonna let a user input that number. So it, the next question is what layout would you like for your form? You have a little bit of a preview window here. We're gonna end up leaving it on column there, but you can do the option button for tabular data sheet and justified to see how that data looks..
And again, we're gonna leave it on columnar and do next. What title do you want for your form? So this will actually show on the form itself as well as in the navigation pain. So what I'm gonna do is I'm gonna get rid of the TBL for forms, the prefixes F R M, and we'll just leave it at customers and choose finish. So there is our. Form. Now we can change things on this form, like the form itself..
I don't want it to say FRM customers and I actually have a typo there, so I'm gonna fix it on the form and then I'm gonna actually fix it in the navigation pain. So in order to fix it, I'm gonna right click on the form tab. And I'm going to go to layout view. So if you remember layout view is a view that you can modify your form design and you get to see the underlying data. So I'm gonna go up and I'm gonna select that box at the top and.
Where it says FRM customers. And I actually am gonna get rid of my typo as well. And I'm gonna name it customer information form. Then I'm gonna click away from it, click back on that box and expand it. So the title now fits on one line and then I can right click on the tab, go back to form view, see what it looks like. And we're good with that form..
So I'm gonna close it. It's gonna prompt me to save my changes to the design. And then I'm gonna rename it by right. Clicking on it in navigation pain, just because I have that typo and I'm gonna get rid of that extra letter and click away from it. So we created a form. We're gonna create another form using the wizard. And this time I'm gonna give you a challenge. Uh, we wanna create a form based on the employees table, and we.
Want all fields except employee ID. So create that form from the form wizard. Now that I have my form create it, my employees form, I'm going to put it in layout view and I'm gonna change the title of the form. Get rid of the FRM and just call it employees form and then save it and close it. Our next step is to create a form using form design view..
So again, we're using the create tab of the ribbon in the forms group. We're gonna click on forms design. Now this is different than your query design view. So you get a grid here. And on the right side, you have a field list pain that pops up. If the field list pain is not there, I'll close mine on the form design tab of the ribbon to the right, in the tools group, you would click on add existing fields to open that pain..
So the form that we wanna create here is gonna be off of the orders table. So in your field list, we're gonna click the show, all tables link, and we're gonna click the plus sign in front of the orders table to expand it. So you can see its fields and we're gonna want all fields except the order ID field. So the easiest way, when you put fields on a form this way, it comes with the label, which shows the name of the field and then the actual field itself..
So if you're gonna be dragging fields onto your form grid, you don't wanna drag them so far to the left that the label is not visible. So an easier way of doing this is I'm gonna right click on the customer ID field, and I'm gonna choose, add field to view. So it will place it on the form. So both the label, which is on the left and the actual field shows up on the form. So since I did that, I did that from the orders table. It's now showing the customer's table and fields available and related tables..
So on and so forth. I'm gonna expand the orders table. Again, I'm gonna write click on order date, ad, field to view. Now it's expanded and it's up in this area field's available for this view. So I'm gonna scroll down, right. Click on ship date and add that field as well. So a simple form we created in design view at this point, I'd like.
To see what it would look like. So I'm gonna right click on the form tab and I'm going to go to form view. So now I can see what the actual form looks like, and that's great, but I kind of wanna title on the form. So I'm gonna right click on the tab again and go back to design view. Now notice in design view, you're seeing the detail section. That's the stuff that actually shows up on the detail section of the form. We're gonna right click right on that gray bar that says detail. And we're gonna show we're gonna select form header footer..
So now we have a form header section. And at the bottom, a form footer section, I'm gonna put my mouse between the form header and the detail section, click and hold and drag down a little bit. So I get more space in the form header section. Now, if you look up at your ribbon on the form design tab of the ribbon, you have quite a few different controls that you can use. So we're on the select control. That's why your mouse looks like a mouse pointer. You also have a text box, you have a label. We wanna use a label for our form header. So I'm gonna select the label control..
And now as I move my mouse into that header section, it looks like a plus sign with a letter a and I'm gonna just click and hold and draw a label about that wide. And I'm gonna just type customer order date information, and then I'm gonna click away from the box, select its edge and widen it. So it's all on one line. And now I'm gonna right click on the tab and go back to form view and see how that looks..
So now we have a, a title for our form at this point, we're ready to save this form. So I'm gonna write click to tab and save, and it's gonna be F RM customer order dates and click. Okay. And now I'm going to close that form. There's another way you can create a form that I'm gonna show you now..
So let's say we wanted a products form and on the products form, we want all of the fields from the products table. So what we're gonna do is we're gonna just open the products table and once it's opened, and by the way, you can close that field list on the right hand side, when it's open, we're gonna go to the file tab and we're gonna choose save. As you have two choices, save the database as, and give it a different name or save object as we're in a table object. And so we're gonna choose save object as then..
We're gonna click on the save as button and notice. We'll start with underneath as there's a dropdown. So we're in the products table. We can save it as a table. If you do the dropdown next to table, we can save it as a query, a form or a report we're gonna choose form. Then we're gonna change the name instead of copy of TBL products, it's gonna be F RM products and we're gonna click, okay. So now you have a form called the products form and we can, and it,.
