Mastering MS Access: How to Create Tables and Write Custom SQL Queries
Why I Still Use SQL in Microsoft Access
When I first started working with databases, I realized that Microsoft Access is much more powerful when we understand both the visual interface and SQL. The visual tools are useful for beginners, but SQL gives more control over how data is created, filtered, sorted, and analyzed.
In this tutorial, we will use a simple Sales Demo Database example. We will create a table named Demo, add fields such as product name, price, and stock level, then write SQL queries to find records based on conditions.
Simple idea: Access tables store the data. SQL queries help ask questions about that data.
Access Table Design vs SQL Query
Before writing queries, it is important to understand the difference between a table and a query.
Step 1: Plan the Table Structure
Before creating a table, plan the field names and data types. A good table structure makes queries easier later.
For this tutorial, we will create a simple product table with the following fields:
Pro tip: Always use a primary key. In Access, an AutoNumber field is commonly used as the primary key because it creates a unique ID automatically.
Step 2: Create a Table in Microsoft Access
To create a table manually, open the Access database and go to the Create tab.
Open the Create tab
Click the Table icon. Access will create a new table named Table1 automatically.
Access creates Table1 automatically
Right-click Table1 from the left navigation pane and open it in Design View. This allows us to edit the field names and data types.
Open the table in Design View
Save the table with a clear name. In this example, we use Demo.
Save the table name
Then enter the field names and data types.
Enter field names and data types
After saving the table, double-click the table name from the left pane to open it. We can now enter sample data.
Saved table in the navigation pane
Open the table to enter data
Sample records in the Demo table
Step 3: Open SQL View in Access
After creating the table and entering sample records, we can write a query.
To write a SQL query in Access:
Go to the Create tab.
Click Query Design.
Close the table selection window if it appears.
Switch to SQL View.
Write the SQL query.
Click Run to see the result.
Create a new query in Access
Step 4: Write a SELECT Query
A SELECT query is used to retrieve data from a table. For example, this query shows product names, prices, and stock levels from the Demo table:
SELECT Product_Name, Price, Stock_Level FROM Demo;
This is the basic pattern:
SELECT field_name FROM table_name;
Step 5: Find Low-Stock Products with WHERE
If we want to find products that are low on stock, we do not need to filter manually. We can use a WHERE clause.
SELECT Product_Name, Stock_Level FROM Demo WHERE Stock_Level <= 10 ORDER BY Stock_Level ASC;
This query means:
SELECT chooses the fields we want to display.
FROM Demo tells Access which table to read from.
WHERE Stock_Level <= 10 shows only low-stock products.
ORDER BY Stock_Level ASC sorts the lowest stock first.
Correction note: If the goal is to find low-stock items, use <= 10. Using > 10 means “greater than 10,” which is not low stock.
After writing the query, save it with a clear name such as qry_LowStockProducts.
Write SQL in SQL View
Click the Run button to see the query result.
Run the query and view the result
More Useful SQL Query Examples in Access
1. Show Products Above a Certain Price
SELECT Product_Name, Price FROM Demo WHERE Price > 1000;
2. Search Product Names with LIKE
In Access, the wildcard is usually *. This query finds products with names containing “phone.”
SELECT Product_Name, Price FROM Demo WHERE Product_Name LIKE "*phone*";
3. Use AND for Multiple Conditions
SELECT Product_Name, Price, Stock_Level FROM Demo WHERE Price > 1000 AND Stock_Level <= 10;
4. Sort Products by Price
SELECT Product_Name, Price FROM Demo ORDER BY Price DESC;
5. Count Total Products
SELECT Count(*) AS TotalProducts FROM Demo;
6. Calculate Total Stock Value
SELECT Product_Name, Price, Stock_Level, Price * Stock_Level AS StockValue FROM Demo;
Common SQL Mistakes in Microsoft Access
Access SQL is similar to standard SQL, but it has some special rules. Here are common mistakes beginners may face:
Access SQL Cheat Sheet for Beginners
How SQL Queries Connect with Forms and Reports
SQL queries become more useful when we connect them to forms and reports. For example:
A form can use a query to show only active products.
A report can use a query to print low-stock items.
A combo box can use a query to show a clean list of categories.
A dashboard can use queries to calculate totals and summaries.
This is why SQL is important even inside Microsoft Access. It helps we build a more useful database application, not just a simple table.
Learn More About Database Security
Database design is not only about tables and queries. Security is also important when we move from practice projects to real systems. we can learn more from the video below:
Final Thoughts
Learning to use SQL inside Microsoft Access is a major step for beginners. we can start with a simple table, add fields and data types, then write queries to filter, sort, calculate, and analyze the data.
In this tutorial, we created a product table, added fields such as Product_Name, Price, and Stock_Level, then wrote SQL queries to find low-stock products and sort results. These are the same basic concepts we will use later in MySQL, SQL Server, PostgreSQL, and other database systems.
Keywords: MS Access SQL tutorial, Microsoft Access queries, Access SQL View, create table in Access, Access SELECT query, Access WHERE clause, Access ORDER BY, database beginner tutorial, Access table design, SQL for beginners
Comments
Post a Comment