Working with SQL queries, what follows is merely a taste on the topic of SQL. It is not the intention to cover the subject fully, since you would need to read not one, but several books. competency will take a lot of practice many hours, possibly months. There are a lot of job positions available for SQL programmers, even if you only know Microsoft Access, and there are quite a number of other databases on the market opportunities are unlimited. Which on customer table, there were only two records. I've increased the size so it looks more like a live situation.

I'll double click on customer and see how many more I've had it and quite a few. So here are all of the records that I've entered. Now you may be asking how did I do this? Well, if your answer was probably by me In an existing customer Excel spreadsheet, you are correct. Now that we have generated tables imported data into the customer table and laid it into the order table, we can now begin working with a wide range of queries by using Microsoft SQL access. First, let's open the access query window.

And the way that we do that is by clicking on and what it will do first is remove that table from my view from my navigational view. And I'll click on query wizard. And here is a dialog box asking what sort of a query do we want? We want a simple, open or simple query wizard and asked me what fields do I want to view in this query. I will select ID followed by last name and using the forward arrow and then first name And then perhaps I will go with city. And that's sufficient for the first query.

Click Next. And what I will select here is a detail summary. Click Next. So what title we want to call this query, I'll change it to customer query one, and finish. And here is the result. So a listing from a customer table, have the ID, the last name, followed by the first name, followed by the city.

Save this as customer query one, but let's just ensure and click the Save icon. And now we'll start another query. So let's return back to the splash screen. And what we will do is select a customer order database again, remove this from our navigation pane. And What I'm going to use is the customer query one, but be a little more selective in my query. So I'll double click this.

And what we want is perhaps a selection. First of all, not all of the fields, but just on Kingston. So I am looking for a city where we can find Kingston and locate this. And there it is. As far as this course is concerned, we are now finished with a query wizard. And what I would like to do is to move on to Query Design.

I'll click Create again and select Query Design. Looking at the customer table for now only and we'll look at the order table or join those two together later. I'll add that to my navigational And close of the show table dialog. I will now go to home and view. What I propose doing here is looking at SQL statements in the flesh and seeing if we can manipulate some of these SQL statements ourselves. So I'll go into the SQL View.

And what pops up is really two statements, a select statement, and a from statement. Each one has a purpose, and I'll explain. The SELECT statement specifies what fields you want viewed in your query. So if I put in select last name, comma first name comma address, those fields then would be queried in the values for the customer table. As shown here, the from statement is followed by a table name, and in our case, a customer table. Well when we added a table In the show table, we select a customer.

So notice how that one popped up. There is one other statement I would like to include which is very common in SQL. And it's the where statement. The where statement specifies the criteria under which the SELECT FROM is going to operate. So if I selected a number of columns from a table, the work could specify any conditions under which this will operate. And what I will do is I will select all of the fields in the customer table and instead of writing out each one of the fields, and asterik will suffice to indicate that I want to select everything in the customer table all the fields.

I condition are built in is where city is too, and in this case, so we'll pick Hamilton, followed by an asteroid. have to be very careful because the indicator here is that Hamilton has to be in quotes. And if it's text base it does. And the final ending of select from where it has to be suffixed with a semicolon. I'm going to save that. It asked me for the for the query name and I will call it customer query to see okay, and it pops up in the object pane.

So I have the customer query two in the object pane, which is comprised of three statements select from where, and the specification is to select all, but restricted to only where the city is equal to Hamilton. So we've saved it as customer query to, I will remove this from the navigation pane and double click and see what the result is from this query. Great. So we have all the fields shown, but only two rows given based on the condition where the city is equal to Hamilton. We could do similar operations with the order table. But instead How about a query based on joining two tables together, the customer and the order table will repeat some of the steps that we did in the last query by going to the Create Query Design.

And this time instead of just one table, let's select two so we'll add the customer table. And we'll also now include the order table. close off this dialog box. We want these two tables to be joined on customer ID that will select from the customer table. I'm holding down the left mouse and holding it until I reach the customer ID in the order table and then release it. What appears is a relation line, which I will right click and join the two together.

The join Properties dialog box comes up and we want from this join properties all only those rows which are joined and common between the two fields, the two common fields ID and customer ID. Okay them. Down here in this navigation part, we want to specify the fields that we want to see in the queries I want to select a couple. In the customer table. I'm going to select the first name and the last name maybe a couple of columns The order table. So in the order table, I want the order ID.

And how about the order notes of order notes. I want to save them Ctrl S also will do, I will call it join query one. Okay, it closes off. And we know by double clicking, we now have the joint query and the object pane. If double, if we double click it, we should get the results of the query that we just created. And there it is.

Always possibility occurs that we need to modify not only the join query but any of the queries that we created. If we highlight to focus on this particular case, a joint query and hit the right key, we can go into the design view make any modifications that we did previously, so perhaps we want to add another field. Here. We want the order city. And the criteria is Hamilton. Let's see how that works out.

We'll save it. And we'll clear this pain off and double click another query that is most useful a parameter query. You select a previously saved query. And in my case, customer query one, enter into the design view and specify a parameter based on a criteria you want and the criteria will be for the user to enter a city and executing the particular query call query number one, based on this criteria that we just specified. So let's try that. First of all, save it.

Play this pain out. And I'll double click No customer query and see what happens. Ah. So, the parameter query now executes and it asks you for a city. State nice Niagara and executed. And it picks up rows where the city is equal to Niagara.

I do hope you've enjoyed this session on Microsoft Access SQL. And there was so much more you can do with it. Much literature has been written on the subject of sequel At least you now have an initial understanding and can explore further. I would like to encourage you to go further and dig deeper into the topic.