|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
SQL with Access By Richard Rost 
4 years ago
How to Use SQL With Microsoft Access Databases In this video, I'll teach you about the basics of SQL and how to use it in Microsoft Access. We'll see how to edit the SQL of an Access Query, how to modify the Record Source of a form, and the Row Source of a combo box. Ari from Hollywood, Florida (a Platinum Member) asks: I have a billion queries in my Access database. Is there any way to consolodate those so the list is a little more manageable? Most of them are just variations on the same theme, like different ways to sort customer lists. MembersMembers will learn how to manipulate SQL using VBA code. We will change a form's RecordSource property and a combo box's RowSource property directly using VBA. This will allow us to make dynamic events by using click events in our forms.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Suggested
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Access SQL basic concepts, vocabulary, syntax, SELECT statement, Microsoft Access SQL, Microsoft Access SQL reference, Can you use SQL in Microsoft Access, Is SQL same as Microsoft Access, Is Access good for SQL, How to Use SQL with Microsoft Access, SQL View, structured query language, why you should learn SQL, sql language vs. server
IntroIn this video, you will learn how to use SQL in Microsoft Access to simplify and enhance your databases. I will explain the basics of the SQL language, show you how Access queries are built on SQL, and demonstrate step-by-step how you can reduce the number of queries in your database by using SQL statements directly in forms, combo boxes, and list boxes. You will also see how understanding SQL can help you adjust data sources without relying on wizards and make your database management more efficient.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.In today's video, I'm going to teach you how to use SQL in your Microsoft Access databases, and I'm going to show you five reasons why, as an Access developer, you should take the time to learn SQL. Today's question comes from Ari in Hollywood, Florida, one of my Platinum members. Ari says, "I have a billion queries in my Access database." I'm pretty sure it's not a billion, but he's got a lot. "Is there any way to consolidate those so the list is a little more manageable? Most of them are just variations on the same theme, like different ways to sort customer lists." Well yes, of course Ari, there's definitely a way you can cut down on that list of queries. In order to do so, you're going to have to learn a little bit of something called SQL. What is SQL? I'll tell you in just a second. But first, a prerequisite. If you haven't yet watched my Access Beginner One Class, go watch that now. To learn SQL, I'm assuming you know how to build queries in Microsoft Access, and if you don't know how to build queries, go watch that. My Access Level One Class, it's four hours long, it's absolutely free. You'll find that on my YouTube channel and on my website, there's a link. You'll also find a link down below in the description. Go click on it and watch that if you don't know what queries are in Microsoft Access. All right, so what is SQL? SQL stands for Structured Query Language. It's basically the programming language that Access queries are based on. I'm sure you're familiar with this. This is the query by design where you build a query graphically. You put the tables in here. There's the customer table. You add the fields down below. You can sort them ascending or descending. You can add criteria. Behind every one of these queries, you'll find this: this is the SQL, the Structured Query Language, the programming language behind that query. In addition to the queries themselves, SQL is also behind the forms, the reports, the combo boxes, the list boxes, any object in your database that has to get data from a table, there's generally some SQL involved. Here, for example, I've got a combo box that selects the customer ID, which is the hidden column in there, of course, the last name, and the first name from the customer table, and it sorts it order by last name and first name. That's the SQL statement behind that combo box. Now do you have to learn SQL to be a good Access developer? No, you don't. However, once you do learn a little SQL, it makes you that much better as a developer, makes your databases more powerful, makes them easier to use. And just like I say about VBA, do you have to learn Visual Basic programming to be a good Access developer? No, you don't need it, but once you learn just a couple of commands, you can really do some cool stuff with Access. All right, so here are my five reasons why you, an Access developer, should learn SQL. Number one, knowing SQL will allow you to modify objects directly without relying on wizards. You can modify forms, the record source behind a form. You can modify the row source of a combo box or a list box and make changes without having to go back to the wizard and rebuild everything. I'm going to show you examples of how to do both of those things in today's video. Number two, you'll have a better understanding of why Access does what it does and why things are built the way they're built. You'll get a better understanding of how to build queries, of how to structure that language in the combo boxes and in the list boxes and in your forms. Number three, and this is Ari's problem. This was my problem for many, many years. Still is, actually. I got tons and tons of queries in my database because I didn't build my database over the past like 20 years. And rather than rebuild the whole thing, I just keep adding and adding and adding. I got millions of queries from way back when I didn't know how to write SQL. So you'll be able to eliminate a lot of those duplicated queries where it's just a slightly different change on a previous query if you know how to write SQL. Number four, your marketability as a developer will increase. SQL is always listed as one of the top skills that employers are looking for. So in addition to just knowing Microsoft Access, now you can say you also know SQL. Number five, if you ever do decide to upgrade to SQL Server later on or another SQL-based server platform, you'll already be one step ahead because you'll know the basic structure of the SQL language from working with it in Access. If you have a small business now, but you're growing and eventually you become a midsize or a large business, you can always take your Access front end and upgrade your back end, your tables and stuff to SQL Server. And going SQL makes that job a whole lot easier. Bonus number six, it's easy. SQL is easy to learn. It's a very simple syntax. It's all English. There are very few crazy function names you have to remember. All right, you'll see in just a second some examples of the syntax. It's easy to learn. Before we continue, it's important to note the distinction between the SQL language and SQL Server. Now the SQL language is what I've been talking about so far. It's an actual programming language where you can select data from tables and do other stuff, but it's a language that you will find inside of Microsoft Access and other products like SQL Server, like MySQL. SQL Server is a specific server-based programming package from Microsoft that handles your data. So the two are different. Sometimes people say to me, "Hey, do you have any classes in SQL?" Well, yeah, I have classes to teach you the SQL language in Access. I've got an Access SQL Server course that teaches you how to upgrade your Access database to SQL Server, and so on. So it's important to understand the difference between the two. If someone says, "I need to learn SQL," which one are you talking about specifically? The program SQL Server or just the language SQL? Yes, there are some minor differences between the two, which gets really crazy, but that's a topic for a different video. Now, when it comes to learning the SQL language, the most basic statement is a SELECT statement, and a SELECT statement has some basic parts: SELECT, FROM, WHERE, and ORDER BY. For example, a simple statement is SELECT fields or single field FROM and then a table or multiple tables or even other queries. But this is the basic syntax right there. For example, you could say SELECT CustomerID, FirstName, LastName FROM the CustomerT table. The field names are separated with commas, very easy. This can be on one line or on multiple lines, Access doesn't care. If you want all of the fields, you just say SELECT * FROM CustomerT like we do in our query graphical designer. We just bring down the star to bring all the fields into the query. If you want to add criteria, you use the WHERE clause. For example, SELECT CustomerID, FirstName, LastName FROM the Customer table WHERE LastName = 'Rost'. If you want to use multiple criteria, you can use AND or OR. For example, WHERE FirstName = 'Richard' AND LastName = 'Rost', or you can put an OR there. If you know keywords, you can use the LIKE keyword in there as well. There's tons of stuff you could do with this. Today I'm only basically scratching the surface. If you want to sort the records, you just add the ORDER BY clause. ORDER BY FirstName: which field do you want to sort by? You don't need to have the WHERE clause if you don't want it. If you want to see all the records, you can. So here, for example, I've got CustomerID, FirstName, LastName FROM the Customer table, no WHERE condition, so just give me all of them, sort by LastName then FirstName. If you want to sort in reverse order, use the DESCENDING keyword, DESC. That's it. That's the basics of a SELECT statement. Right there, what I just showed you right there is about 90 percent of SQL that you'll ever need, but just knowing that will make modifying your database a whole lot easier. Let me show you some examples. Here I am in my TechAccount free template. This is a free database. You can download it from my website if you want to. You'll find links down below. In here, we built a couple of simple queries. For example, the CustomerLFQ that simply takes the CustomerID and puts it together with concatenation, the LastName and the FirstName. Let's take a look at how it's designed in design view. You should know all this already. I'm going to close this property window. So we've got the CustomerID, and we've got another field here that we put together, LastName and FirstName. That's called LF. If I right-click right there, you'll see SQL View. You'll also see it right over here if you drop that down. There are about three ways to do everything in Access. There's the SQL behind that query. Let me zoom in, Shift+F2, so you can see it better. It's SELECT CustomerT.CustomerID, LastName & ', ' & FirstName AS LF FROM CustomerT; and that's it. That little bit of text right there turns into that with the graphical designer. Let's make another query. Save changes. Let's go to Create and then Query Design. Let's bring in the Customer table. Close that. Let's add the star. I got all the fields down here. We learned that in Access Beginner One. Let's just start to SQL View and see what we got. I'm going to zoom in. It's SELECT CustomerT.* FROM CustomerT; and generally you have to end an SQL statement with a semicolon, but in Access that's not required. It's not a bad habit to get into, but you don't really need it. But once you start getting into more advanced SQL, you're going to want it. Let's close this and we'll go back to Design View. Let's get rid of that star, and we're going to add some specific fields. I want to add CustomerID, FirstName, and LastName. Generally, when you're dealing with other objects like combo boxes and list boxes and even forms, you don't want to add the star. You don't want all those fields in your combo box. We're going to work with a couple of fields here. Let's take a look at the SQL for this. Right-click SQL and there it is. I'll zoom in again so you can see it better. SELECT CustomerID, FirstName, LastName FROM CustomerT. Now, Access adds the CustomerT.CustomerID and then CustomerT.FirstName. That's because if you have multiple tables in your SQL statement, Access has to know which table that field comes from. Now, we're not going to do that today — put multiple tables in here. So if you want to, you can get rid of that CustomerT. If you want to, I find it makes the SQL easier to read if you don't have all that in there. But just keep in mind, if you do in the future get into making queries that have multiple tables in them, you need that. I'll just hit OK and then I'll go back to Design View. Then I'll run it and there we go. There's just the fields we asked for. Want to add a sort? Let's sort by LastName. Drop the sort box down there, pick Ascending. Run it. There you go. Let's go back to SQL View and see how it changed. Now, take a look at that: Access adds back in that CustomerT. all the time. It's kind of fruitless to take it out of there, but you get the point. There's the ORDER BY clause right there: ORDER BY LastName. Want to sort by FirstName and LastName? Drop that down. Remember, if you want to sort LastName first, you have to click this guy and drag it to the left of FirstName. Access sorts left to right. And in your SQL statement, it'll also be left to right: LastName and then FirstName. Want to add criteria? LastName = 'Rost' right down there inside quotes. And again, SQL View: there's my WHERE condition. Once again, Access adds a lot of extra stuff in here that you don't really need. I'll zoom in. You can see where it's got CustomerT.LastName = 'Rost' with a whole ton of parentheses. You don't need most of this stuff. Access is just being thorough just in case you've got multiple fields in there, but that's really all you need. In fact, you can whittle this down if you want to. I'm going to get rid of all the unnecessary stuff here. There. That's the SQL statement in its simplest form: SELECT CustomerID, LastName, FirstName FROM CustomerT WHERE LastName = 'Rost' ORDER BY LastName, FirstName. Yes, Access does add a lot of superfluous stuff in there, but if you're not sure just leave it. Now again, why do I need all this stuff? I'm going to hit Cancel. Cancel that, save changes, no. So I've got my Customer List form here. Right now it's just set up by whatever the records are in there, there's no sort. If you look in Design View, open this guy up here, the Properties, you'll see the Record Source is just CustomerT. Now, I could set up a query to sort this guy differently, let's say LastName, FirstName, but then I have to make a query and save it and do all that stuff. Whereas I could just come in here in the Record Source property itself. I'm going to zoom in, Shift+F2, and now that I know SQL, I can replace this CustomerT with an SQL statement. I can say SELECT * FROM CustomerT ORDER BY LastName, FirstName. Now that I know how to write SQL, that goes right in there. Save it, close it, open it back up again and look at that. It's sorted. In the extended cut for the members, I'm going to show you how to make a little click event so you can click on the FirstName label up here, sort by that, or click on the LastName, sort by that. We'll do that in the extended cut. But you can see now how I've already eliminated the need for one query, and that's just one form. Let's do that again with a combo box. Each one of my customers can have orders. If you haven't watched my invoicing video, go watch that. It's absolutely free again. It's on my website. I'll put a link down below. That's how we build this. We build the invoice, which you can print out and stuff. This is all free. Right here, this combo box has its own row source. Forms and reports have record sources. List boxes and combo boxes have row sources. Let's take a look. Design View. Open this guy's properties up. There's the Row Source property. Look at that. There's already a SELECT statement in there. Now, this guy doesn't use an outside query. It actually is using the LastName, FirstName query that we built. You can put just a table-based query here if you want to, but if you want to make changes to this thing and you don't know SQL, you have to rebuild it using the wizard, using the combo box wizard. However, if you know a little SQL, you can change this without having to rebuild the whole thing every time you want to make a change. Let's see how. But first, a word from our sponsor. Who's our sponsor? Well, that's just me. If you want to learn more about SQL, I've got three whole seminars on the SQL language itself. Part One teaches you all about SELECT statements, all the different clauses, how to enhance it with WHERE conditions, ORDER BY clauses, all that different stuff. We integrate it into forms and reports. We do all kinds of cool things in Part One. Part Two is all about action queries, using SQL to actually change the data in your database: update queries, insert queries, append queries, all kinds of cool stuff. We'll also do union queries. Union query is something you can only do with SQL. You can't do it in the graphical design. That's where you take data from two tables and put them together so it looks like one table. Parts One and Two, lots of great information. Part Three is about modifying the structure of your tables using SQL. That's like adding fields and stuff like that. That's a lot more advanced. Most people don't go as far as Part Three. You don't really need that stuff, but One and Two, lots of great stuff in there. You'll find that on my website, findincd.com/sql. I'll put a link down below that you can click on. And that was a quick word from our sponsor. Me. All right. So how can I modify this thing in here? Let's take a peek at it. Let's Shift+F2 to zoom in. So we're going to CustomerLFQ and getting CustomerID and then the LF field from that query, order by whatever. We could probably eliminate that query if this is the only place we're using it by grabbing the SQL out of that query. Watch this. I can come over in here, CustomerLFQ design view. This is all that query is. Let's take a look here. Let me get rid of this. Let's take a look at the SQL. SELECT CustomerID, LastName & ', ' & FirstName AS LF FROM CustomerT. Let's just copy that. Close it. I'm into this guy here. We're going to delete all this, and we're going to paste that stuff in. Let's get rid of the CustomerT. I want CustomerID, then I want LastName & ', ' & FirstName. I can get rid of the AS LF. We don't need that anymore either. From CustomerT. Then we had an ORDER BY on there, right? ORDER BY LastName, FirstName. If you want to, you can get rid of these brackets too, because you are following my naming convention, you took my Access Beginner Level One class, and you know not to put spaces in your field names. That's the only reason why you would need brackets, so don't use brackets. I get people every day that send me problems because they didn't put the proper brackets around a field name that they have spaces in. Don't do that. So there's the SQL statement for that combo box, which does exactly the same thing that that query was doing over there. Now I've eliminated the need for two queries. Close that. Close it. Save it. Open it back up again, and there's the same thing with custom SQL behind it. In the extended cut for the members, I'm going to show you how we can click on this and change the way that this list appears. We can sort LastName, FirstName, or flip it around and do FirstName, LastName. That will do that for the members. That involves a little bit of programming. That's pretty much it. Now you can come in here and you can get rid of this guy. If you're sure you're not using it anywhere else, you can delete it. I'm pretty sure that's the only place we have it. And there you go. That's how you use SQL. That's the basics of a SELECT statement. There's tons more to learn, and again, I cover lots more in my SQL seminars. If you want to learn more, in the extended cut for the members we're going to do those two things that I mentioned. We're going to take the Customer List and make it so you can click on the label up here, FirstName or LastName, and change the sort of the customer list. Then we're going to make it so you can click on this little button and change how this combo box appears. We're going to change the row source and the record source of those two things using a little VBA and a little SQL. Here's the member database. FirstName click, LastName click. See that? It just changes the SQL in the form based on which label you click on. Then you want to open somebody up like this customer here. Go to their order form. Now this appears LastName, FirstName, but you can change it by clicking on this label, and now it's FirstName, LastName. Same data, just displayed and sorted differently. See? Sort LastName, FirstName. Sort FirstName, LastName. That's pretty cool, huh? Sometimes you don't know if you're looking for a customer, if you know the customer's first name. You might want to sort that list to find Jean-Luc, for example. See how that works? That all is covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Not just this one, all of them. There are about 240-some of them now, I think, approaching 250. Gold members can download these databases and you get access to the Code Vault on my website with all kinds of cool VBA code. That's the extended cut for the members. So what are you waiting for? Join today. How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What is SQL in the context of Microsoft Access?A. A server-based database management system B. A programming language used to structure and retrieve data in Access queries C. A system utility for backing up Access databases D. A hardware requirement for using Access Q2. Why might an Access developer want to learn SQL, according to the video? A. To avoid using any queries in Access B. To be able to directly modify objects without relying on wizards C. To hide data from other users D. To design new hardware Q3. Learning SQL allows you to: A. Replace Access with another database program B. Modify the record source and row source properties for forms and combo boxes directly C. Ensure database security D. Change the Access program interface Q4. Which of the following is NOT one of the five reasons given for learning SQL as an Access developer? A. Eliminating duplicate queries B. Enhancing job marketability C. Making Access run on a Mac D. Simplifying future upgrades to SQL Server Q5. What is the basic syntax for a SELECT statement in SQL as shown in the video? A. SELECT FROM, WHERE, ORDER BY B. SELECT fields FROM tables WHERE criteria ORDER BY field C. FIND fields IN tables ORDER criteria D. CHOOSE fields FROM database WHEN condition ORDER list Q6. What happens if you want all fields from a table in a SELECT statement? A. You write SELECT ALL FROM TableName; B. You write SELECT # FROM TableName; C. You write SELECT * FROM TableName; D. You write SELECT EVERY FROM TableName; Q7. How do you add criteria to your SQL SELECT statement? A. Using the FILTER clause B. Using the WHERE clause C. Using the HAVING clause only D. By choosing additional queries Q8. If you want to sort your SQL query results by LastName in descending order, which of the following statements should you use? A. ORDER BY LastName DOWN B. ORDER FROM LastName DESCENDING C. ORDER BY LastName DESC D. ORDER ASC LastName Q9. According to the video, what is the difference between SQL language and SQL Server? A. SQL Server is just another name for the SQL language B. SQL language is a programming language, while SQL Server is a database management system C. They are both types of Microsoft Office software D. SQL Server is a hardware product while SQL is software Q10. Why does Access sometimes add extra table names and parentheses in SQL statements it generates? A. To make the statements incorrect B. To confuse the user C. To ensure clarity when multiple tables are involved D. To add more functions Q11. If you know SQL, what advantage do you have when modifying combo boxes or list boxes in Access? A. You must always rebuild them using the wizard B. You can directly edit their Row Source without using the wizard C. You can only use one field in a combo box D. It automatically encrypts your data Q12. What does the wildcard character * represent in the SQL statement SELECT * FROM CustomerT? A. Select only numeric fields B. Select a single field with a star in its name C. Select all fields from the table D. Select records with a blank value Q13. Why does the instructor recommend not using spaces in field names? A. Spaces make the fields easier to read B. Spaces force you to use brackets in SQL, which can cause errors C. Spaces improve the performance of your queries D. Spaces are required by SQL syntax Q14. What is a UNION query, as referenced in the video? A. A query that deletes records from a table B. A query that merges data from two tables into one result set C. A query that changes the format of a table D. A query that splits a table into two Q15. What does the instructor say about ending SQL statements with semicolons in Access? A. They are never allowed B. They are absolutely required for every statement C. They are not required, but a good habit for advanced SQL D. They will cause Access to crash Q16. Which of the following is true about extending Access with SQL knowledge according to the video? A. It only helps if you program in C# B. It is unnecessary for any level of development C. Even basic SQL makes modifying your database easier and eliminates redundant queries D. It only works with SQL Server backends Q17. According to the video, what is the most basic SQL statement for retrieving data? A. UPDATE B. INSERT C. SELECT D. DELETE Q18. What feature does the instructor demonstrate changing using SQL to eliminate extra queries in Access forms? A. The color scheme of Access B. The Record Source property of a form or Row Source property of a combo box C. The primary key of a table D. The relationships window Q19. What is the benefit of being able to write and edit SQL for record sources and row sources directly? A. You can avoid using the query designer or rebuilding with wizards B. You will always need to use macros instead C. It prevents database corruption D. It increases the database file size Q20. What is a likely benefit of learning SQL for future business growth, as outlined in the video? A. SQL is incompatible with Access after upgrading to SQL Server B. Knowledge of SQL allows for easier backend upgrades as the business grows C. SQL prevents users from accessing data D. SQL makes your business less flexible Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-C; 11-B; 12-C; 13-B; 14-B; 15-C; 16-C; 17-C; 18-B; 19-A; 20-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone is all about using SQL in your Microsoft Access databases. I'll go over exactly what SQL is, and I will share five important reasons you should take the time to learn this language as an Access developer. This topic comes from a question submitted by Ari, who has a ton of queries in his Access database and wonders if there is a way to consolidate them, since most are only minor variations of the same thing, such as sorting customer lists in slightly different ways.First, if you don't know how to build queries in Access, make sure to check out my Access Beginner Level One class. It's a free, four-hour course available on my website and YouTube channel. Having that baseline knowledge is important before diving into SQL, because today's lesson assumes you know how to create queries using the graphical tools in Access. Now, to answer Ari's question, yes, there absolutely is a way to clean up and consolidate all those queries, but you'll need to learn some SQL. SQL stands for Structured Query Language, which is essentially the language that drives queries in Access. While you may be used to designing queries using Access's query design window, everything you're building gets turned into SQL behind the scenes. This includes not only queries but also forms, reports, combo boxes, and list boxes—pretty much any object in your database that retrieves data is using SQL under the hood. For example, suppose you have a combo box that lets you select a customer. That combo box uses an SQL statement to get the records it displays, selecting the customer ID, last name, and first name from the customer table, and sorting them in the order you specify. You do not have to know SQL to be an Access developer, but learning it can really improve your skills and make your database solutions cleaner and more flexible. It is similar to VBA in that sense: you can get by without it, but even learning a little can help you add more advanced features to your Access applications. Let me walk you through the five biggest advantages to learning SQL as an Access developer. First, SQL enables you to edit the record source and row source properties for forms, combo boxes, and list boxes directly, without depending on the wizards. You have more direct control, and you can make changes instantly without having to build or rebuild queries every time. I will show examples of this as we go along. Second, understanding SQL helps you grasp why Access structures things the way it does, and how the underlying queries in forms and other controls actually work. You'll gain a deeper understanding of how to design queries and control the data being shown throughout your database. Third, and this is particularly relevant to Ari's question, learning SQL helps you consolidate and reduce the number of saved queries in your database. Often, people create separate queries just for minor variations, like the sorting order or different filter criteria, when you could easily use a single query and modify its SQL code as needed. This cuts down on mess and redundancy significantly. Fourth, adding SQL to your skill set makes you more marketable as a developer. Employers often look for familiarity with SQL, since it's widely used in the industry and transferring that knowledge to or from other systems is highly relevant. Fifth, if you ever decide to move your data from Access to SQL Server or another SQL-based back-end, knowing SQL will make the transition much easier. As your business grows and you need a more robust database system, being comfortable with SQL will help you upgrade from Access tables to something like SQL Server with much less friction. And as a bonus, SQL is fairly easy to pick up. The syntax is straightforward, uses regular English words, and does not require memorizing a lot of odd function names. It's important to note the distinction between the SQL language and SQL Server, which is a Microsoft database product designed to store and manage data on a server. The SQL language itself is used in Access, SQL Server, and other database systems like MySQL. When someone says they want to learn SQL, it helps to clarify if they mean the language or the specific server software. There are some differences between how SQL is implemented across different systems, but that's a topic for another video. The most basic SQL command you'll use in Access is the SELECT statement, which is the core of any query that retrieves data. It has several main parts: SELECT specifies the fields you want, FROM indicates the table(s) you are getting data from, WHERE is used for filter conditions, and ORDER BY handles sorting. For instance, if you want to show customer IDs, first names, and last names out of your CustomerT table, you would write a simple SELECT statement that lists these fields and the table they come from. Putting an asterisk after SELECT, such as SELECT * FROM CustomerT, grabs all fields from the table. You can add filter conditions with WHERE, such as WHERE LastName = 'Rost', and combine conditions with AND or OR as needed. For sorting, ORDER BY lets you specify one or more fields, and you can use the keyword DESC to sort in descending order. This simple structure covers most of what you need to write or edit the SQL statements that drive your forms, reports, and controls in Access. Knowing this lets you both create your own queries from scratch and modify the ones that Access generates automatically. Let me give you a more practical look at this. In the free TechAccount template, I have a query called CustomerLFQ that generates a field combining the last name and first name. If you look at the SQL view for that query, you'll see it's a simple SELECT statement that concatenates those fields. Creating a brand new query that pulls all fields from CustomerT will result in the SQL SELECT CustomerT.* FROM CustomerT. Access adds the CustomerT prefix to field names when you have multiple tables involved, but you can often remove it to make your SQL code easier to read if your query references only one table. If you want to display only certain fields, say CustomerID, FirstName, and LastName, you can remove the asterisk and list the fields specifically. Sorting in the query can be adjusted by adding an ORDER BY clause, for example ORDER BY LastName. Whenever you make changes in design view, Access updates the underlying SQL, and you can see exactly what changed by toggling between the SQL and design views. Adding criteria is just a matter of typing the condition you want into the query design grid, which Access then translates into a WHERE clause in SQL. Sometimes Access inserts extra parentheses and table prefixes, but these are generally optional unless you are working with joins or more complex conditions. Once you know SQL, you can use more flexible approaches in your forms and controls. For example, on the Customer List form, if you want to sort the data by last name and first name, you can go directly into the form's Record Source property and type your own SQL statement, such as SELECT * FROM CustomerT ORDER BY LastName, FirstName. No need to create and save a separate query for every small variation. This reduces clutter and gives you more direct control. In the extended cut for members, I'll show how you can actually make the form respond to clicks on label headings, so you can sort dynamically by first or last name using VBA to swap the SQL statement out. A similar improvement can be made with combo boxes. For instance, if a combo box is based on a query like CustomerLFQ, but that's the only place you use it, you can copy the query's SQL code directly into the combo box's Row Source property. This removes the need to keep a separate saved query, and you can further edit or customize that SQL as needed, all without the wizard. Modifiers like using bracketed field names are only necessary if your field names have spaces. If you follow good naming conventions, you can keep your SQL cleaner and easier to maintain. Beyond these basics, there is a lot you can do with SQL in Access. If you are interested in a deeper dive, I've created three full video seminars covering SQL. Part One focuses on SELECT statements, different clauses and how to use them with forms and reports. Part Two explains action queries, allowing you to update, insert, or append data and introduces union queries, which combine data from multiple sources into one set. Part Three covers advanced operations like modifying the table structure. Most users will find the first two enough to manage almost any application. To recap, learning SQL lets you standardize and minimize the number of saved queries you need, gives you more flexibility in your forms and controls, makes you a better and more marketable developer, and prepares you for future upgrades. SQL is, at its heart, easy to learn, and it opens up a lot of capabilities within Access you might not have considered before. If you want to see all of these steps demonstrated as I work inside the Access application, or if you want to see how to build dynamic sorts using label clicks, you can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListWhat is SQL and its role in Microsoft AccessUsing SQL to consolidate and reduce queries SQL behind forms, reports, combo boxes, and list boxes Understanding SELECT statements in SQL Components of a SELECT statement: SELECT, FROM, WHERE, ORDER BY Using WHERE clause for criteria in SQL Using ORDER BY for sorting records in SQL AND and OR keywords for multiple criteria in SQL Using LIKE keyword for pattern matching in SQL Working with SQL View in Access queries Modifying Record Source property of a form with SQL Modifying Row Source property of a combo box with SQL Copying SQL from query to replace saved queries Difference between SQL language and SQL Server Access syntax for table and field names in SQL Eliminating excess saved queries using embedded SQL Benefits of learning SQL for Access developers Basic syntax for concatenating fields in SQL Simplifying SQL statements for readability Access automatically qualifying field names in SQL Directly editing SQL for dynamic data sources in Access |
||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Access SQL basic concepts, vocabulary, syntax, SELECT statement, Microsoft Access SQL, Microsoft Access SQL reference, Can you use SQL in Microsoft Access, Is SQL same as Microsoft Access, Is Access good for SQL, How to Use SQL with Micros Page Tag: SQL PermaLink How to Use SQL With Microsoft Access Databases |
