
Learn to write SQL statements in Microsoft Access using the six select clauses and date, text functions. Join tables, manipulate data, and connect to SQL Server with pass-through queries.
Learn to navigate Udemy with the video player, adjust speed and volume, access subtitles, use the course content tab, and manage updates, Q&A, notes, ratings, and the certificate.
Create a demonstration database in Access by downloading the Northwind sample, saving it locally, and exploring the orders table to prepare for SQL queries.
Learn SQL for Microsoft Access boosts control and portability by coding SQL via GUI or SQL view, enabling complex queries like unions and easier fixes when GUI fails.
Create and run your first sql query in Access using select and from; learn how semicolons end statements and how to fetch all fields with select *.
Learn how the where clause filters records by conditions, using operators like greater than, less than, between, not equal, and brackets to resolve ambiguity; understand semicolon usage and readability.
Learn how to use group by to summarize data in SQL queries, apply aggregates like sum, and decide what to group by, with examples from payment type and purchase orders.
Explore how to use the having clause after group by to filter aggregated results in SQL for Access, using the sum of the shipping fee and orders.
Use the order by clause to guarantee sort order, specifying ascending or descending directions for fields like purchase order id and unit cost.
Explore aggregations in Microsoft Access by using group by for non-aggregate fields, and apply sum, count, min, max, and average with the expression builder to name results.
Learn to alias fields and tables in Microsoft Access SQL, using as and brackets for spaces, and understand why aliases apply only in specific clauses.
Filter text fields with the where clause in Access using literal strings, brackets, and not equal; use left, right, and like with wildcards to match starts, ends, or contains letters.
Explore how nulls behave in Microsoft Access SQL, learn to use is null and is not null to find or exclude unknown values, and distinguish null from empty strings.
Master filtering date fields in Microsoft Access with where clauses, hash-delimited dates, and between ranges. Learn to handle British and Japanese date formats and time components to ensure precise results.
Explore date/time functions in Microsoft Access, including year and month extraction, adding intervals, and converting numbers to dates, with examples of grouping and ordering by year and month.
Explore text and math functions in SQL for Microsoft Access, including left, right, mid, trim, ltrim, case conversion, rounding, and financial functions in select and where clauses.
Explore conditional functions in SQL for Microsoft Access, including if, switch, and choose, with examples of immediate versus deferred payments and null handling.
Explore how SQL statements power lookups and data displays across Access objects by linking orders to employees and customers, using lookup fields, forms, and reports.
Join the orders table with the employees extended table using an inner join on the employee ID to access the employee name and filter names that start with A.
Join the orders and customers tables on orders.customer_id = customers.id; count each company's orders with group by, and use aliases to simplify inner join queries, noting nonmatching rows are excluded.
Explore how inner, left, right, and outer joins combine customers and orders in Microsoft Access, compute zero orders with counts, and why cross joins are rarely used.
Learn inequality joins in Access SQL to count orders up to a date, using a date table and the greater than or equal to operator, beyond the GUI.
Learn to extend an Access SQL query to three tables by joining orders with customers and employees, using brackets to separate joins and aliases to disambiguate fields.
Explore when to use subforms and subreports in Microsoft Access to display orders with related customers and employees, instead of relying on complex joins in queries.
Explore missing data by identifying customers with no orders using left or right joins, group by with having, and where order ID is null, via Access query wizard unmatched queries.
Create a make table query by adding into and the new table name before from to save current query results as a real table, preserving data types.
Master inserting data into an existing table in Microsoft Access using an append query (insert into), preserving an audit trail and avoiding table replacement.
Learn to delete records in Access by turning a select query into a delete query, such as removing records from customers table where job title is null, with cascading deletes.
Learn to transform records with update queries in Access by replacing select with update, using set to change fields, applying where conditions, and handling joins and cascading updates.
Explore using distinct in the select clause to remove duplicates and compare it with group by, applying it to job titles and emails for unique results in Access SQL.
Learn how to use the top clause in Access SQL to show the top three job titles by count, using group by and order by for accurate results.
A query which contains all of the Months in the Order table (use DISTINCT) and the cumulative number of products sold per product.
Filter customers by the three least used job titles with a subquery in the where clause, or use a derived table; both approaches produce the same result in Access SQL.
Learn how correlated queries work in sql for microsoft access, including counting orders per customer in the select clause. Compare correlated and non correlated subqueries and when derived tables help.
Learn to create a cross-tab pivot in SQL for Access by transforming the month of order date into columns, grouping by employee, and counting orders as values.
Discover how to add and declare parameters in Access queries to enable interactive input from a form, using the three-part name forms!form input!txtOrderID and parameter types like integer.
Learn how union and union all combine rows from two queries in Access, align column counts and types, and handle duplicates to produce comprehensive results.
CREATE TABLE TestAllTypes
(
MyText TEXT(50),
MyMemo MEMO,
MyByte BYTE,
MyInteger INTEGER,
MyLong LONG,
MyAutoNumber COUNTER,
MySingle SINGLE,
MyDouble DOUBLE,
MyCurrency CURRENCY,
MyReplicaID GUID,
MyDateTime DATETIME,
MyYesNo YESNO,
MyOleObject LONGBINARY,
MyBinary BINARY(50)
)
Explore how to link Microsoft Access to an external data source like SQL Server, choosing between import and link, and importing the Person.Address table from AdventureWorks.
Create a pass-through query to send SQL Server code from Access through a DSN, allowing server-side processing with the target SQL dialect.
Learn to write sql in microsoft access using the six clauses of the select statement—select, from, where, group by, having, and order by— and apply joins, including inequality joins.
Do you want to learn how to write more advanced queries in Access. And do you want your queries to be portable into SQL Server and Access?
Please note: This course is not affiliated with, endorsed by, or sponsored by Microsoft.
What do people just like you think of this course?
"This is an excellent course, very well organized, structured, explained and paced. In addition, the course is content rich, provides a very good context and helpful tips! This is exactly what I was looking for. Thank you very much!" - Dmitry Tlusten
"I am a 51 year old complete beginner and just wanted to learn access to create my own database for a side business. I first took an MS Access course and then this SQL for Access course which allowed me to understand access better and to add some useful functionality to my database. Really enjoyed it, felt like a great introduction to SQL." - Mark Nugent
"If I were to name single biggest advantage of this course I would say that it is very logically structured. In my opinion essential to master any subject. Before the course I had some vague idea of SQL, but after it I feel quite solid." - Bartomiej Kurzeja
In this course learn how to:
Write SQL code, using the six SELECT clauses.
Use functions and formulas in your SELECT statements.
Create joins between tables, including more complex joins which cannot be evaluated using the graphical interface.
Write action queries and cross-queries.
Using derived tables in the FROM clause, and subqueries in the WHERE clause.
Creating correlated queries and cross-queries.
Using UNION and UNION ALL, and
Importing or Linking data and creating Pass-Through queries from other databases.
Hopefully, by the end of this course, you will also feel "quite solid" in Access SQL, and be able to use the extra functionality allowed only when using Access SQL to build even better databases.