Table v Query
By Richard Rost
2 hours ago
What's the Difference Between a Table and a Query? In this lesson, we will explain the difference between tables and queries in Microsoft Access. You will learn how tables store the original data, while queries provide reusable views that filter, sort, calculate, hide fields, and combine information from one or more tables. We will also discuss why queries usually show live data, why duplicate tables for different lists create problems, and how forms and reports often use queries to prepare and present data. Martin from Davis, California (a Silver Member) asks: How do I know when I should use a table and when I should use a query? I already have my customer information in a table, so I don't understand why I need another thing that looks almost the same. I just want to know which one I should be using and why. Recommended Courses
Keywords TechHelp Access, Access table vs query, tables store data, queries filter data, select query, query criteria, query sort order, query calculations, join multiple tables, query live data, datasheet view, database normalization, forms and reports, active customer query
Subscribe to Table v Query
Get notifications when this page is updated
Intro In this lesson, we will explain the difference between tables and queries in Microsoft Access. You will learn how tables store the original data, while queries provide reusable views that filter, sort, calculate, hide fields, and combine information from one or more tables. We will also discuss why queries usually show live data, why duplicate tables for different lists create problems, and how forms and reports often use queries to prepare and present data.Transcript If tables and queries both look like rows and columns, why does Microsoft Access need both of them?
Welcome to another TechHelp video brought to you by Access Learning Zone.
I'm your instructor, Richard Rost.
Today, we're going to talk about the difference between a table and a query in Microsoft Access.
The short version is this: tables store your information, and queries ask questions about that information.
That's the simple way to remember it. Now let's unpack that a little bit.
Recently, I received an email from a silver member, but I get asked this all the time by beginners.
Martin from Davis, California, asked, "How do I know when I should use a table and when should I use a query? I already have my customer information in a table, so I don't understand why I need another thing that looks almost the same. I want to know which one I should be using and why."
Well, Martin, the correct answer is you should be using both of them.
A table is like a permanent home for your data. If you're tracking customers, the customer table is where those customers actually live. If you're tracking orders, the order table is where those orders live. Products live in a product table, and so on.
Each table should focus on one kind of information. Customers go in the customer table, orders in the order table, products in the product table.
And inside that table, you've got individual fields that are the individual pieces of information, like first name, last name, phone number, and so on. Records are the rows, so one row might be one customer, or a row in the product table is one product.
So if you remember nothing else about tables, remember this: tables store the real data. They're the source of truth.
You could think of a table as a permanent archive box for one kind of thing. And inside that box, let's say you've got index cards. Each card is like one record, one customer. And written on that index card, you've got first name, last name, phone number, and so on. Those are your fields.
But the important thing here is the box, the table holds the real information.
Now, if tables store data, why is that not enough?
Well, because most of the time, you don't want to see everything in the table. Imagine your customer table has 50,000 customers in it, and the boss walks in and says, "All right, show me all the customers from Florida sorted by last name."
Now, you could open the table, apply a filter, sort the last name field, hide a couple of columns to make it look just right. But then tomorrow, the boss says, "Oh, I want to see that list that you gave me yesterday with the customers from Florida."
Now you've got to go do all that work again. Or he might throw a curveball. He might say, "Show me all the customers with a credit limit over $1,000." Or next week, "Show me everybody who's not ordered in the last year."
You don't want to keep rearranging the same table every time someone asks a question about that data. So the table is storage, but it's not meant to be 50 different custom lists.
So, for our analogy with our boxes, the boxes are the tables. They contain the actual data. But let's say you've got different lists printed out.
So you could say, "Okay, here's everything in the boxes organized alphabetically." Here's another list I printed out that shows where all of the electronic items are stored. Here's another list that shows everything that's blue.
See, you can print out different lists that just explain what's in the boxes. And those are like queries.
A query is basically a saved question that you can ask your table. Show me all the customers from Florida. Show me all the orders from last month. Show me all the customers who are active. Show me everyone with a credit limit over $1,000.
Every query remembers the fields that you want, the sort order that you want, and the criteria that you want. And instead of rebuilding that list every time someone asks a question, you save it as a query.
Then later, you just open the query, and Access asks that table the same question again.
That's the big idea. The table stores the information, and the queries ask the questions.
The boxes still hold all your memorabilia, all your stuff, all your pictures and your baseball cards and all that stuff. But the lists on the clipboards that you printed out, those printouts, those lists have only what you wanted to find.
Maybe you've got a list alphabetical. You've got another one that's printed out by color, whatever. Tomorrow, you can use the same sheet again instead of searching through every box from scratch.
If you're looking for a baseball, you just look down your alphabetized list and say, "Okay, baseballs are in box 36."
And that's what makes queries so useful in Access. It's not another place to store information or store orders. It's a saved question that tells Access which customers you want to see or which orders you want to find based on how you want to see them.
And that's one of the biggest beginner misunderstandings. Most of the time, people think a query contains its own copy of the data. It doesn't.
A normal select query is just a view of the data that's stored in the table. The query results might look like a table, but the data still lives in the original table, not the query.
Think of it like looking through a window. The window lets you see part of the room. But the furniture is not stored in the window. The furniture is still stored in the room.
Same thing here. The query lets you see certain records in certain fields, but the real data is still in the table, which is good because you don't want five copies of your customer list floating around.
On the clipboard, you've got a carefully prepared viewing list, not another archive box. The list shows you a list of baseballs, postcard souvenirs, that match a particular request. But those items have not been moved onto the clipboard. They're still safely in their original boxes.
One set of data, one trusted home, but as many useful views as you need.
Now here's the part that surprises people, and this is actually where my metaphor breaks down. Since a query is often a live view of the data, if you change the data in the query, you may be changing the data in the table.
If you open a customer query and change someone's phone number, you didn't just change the query, you changed the phone number in the customer table. It's the same underlying record. It's just being displayed a different way.
Now, this is why I don't let end users poke around directly in tables or queries unless they know what they're doing. And even then, I sometimes don't.
For most regular users, you normally build forms, and forms give people a friendly screen to work with and give you more control over what they can see and edit.
But conceptually, for you, the new Access developer, this is important. Queries do not usually store a separate copy. They're looking at the real data.
And like I said, this is where my metaphor kind of breaks, because if the user changes something on the written printout sheet, they're just scratching over the printout sheet. That's not how queries work with Access.
So ignore the metaphor for this slide. Just remember that queries usually show you live data.
Now, queries are useful because they do more than just filter a table. Yes, they can save a list like customers from Florida, sorted by last name, blah, blah, blah.
But they can also hide fields you don't want people to see, like credit limits. They can perform calculations, like total price equals quantity times unit price, that kind of stuff. They can combine information from multiple tables.
For example, your customer name might be in the customer table. The order information is in the order table, and the query can bring those together. So you can see the customer name with their, let's say, latest order date.
And that's extremely powerful. But again, you're not copying data into a new table. You're asking Access to assemble the information that you need from the data that already exists.
For example, with our analogy, you might have boxes numbered. You might also have your shelves numbered. And you've got different tables for each of those. You've got a shelf table, and you've got a box table. You might even have a room table.
And so you can bring all that information together. Find me where the baseballs are. Okay, go to room three, shelf two, box 14.
So let's put them side by side. Tables store records. A query displays records based on a question.
A table is where the original data lives. A query is a reusable way to view that data.
A table usually represents one kind of thing: customers, products, orders, contacts, employees, that kind of stuff. A query can pull from one table or several tables and show only the fields or records that you want.
Tables and queries can look similar because Access often shows both of them in a datasheet view, which means rows and columns.
And that's where beginners get confused because they judge them by how they look, not by what they do. Table equals storage. Query equals question.
I mean, the word query literally means question.
Now, one of the biggest beginner mistakes is creating a new table every time you need a different list. Beginners often make CustomerFloridaT, CustomerNewYorkT, CustomerInactiveT.
Now they've got that same kind of customer data spread all over the place. Don't do that.
That's exactly what queries are for. Keep one real customer table, then make different queries to view that customer data in different ways.
One query can show Florida customers. Another can show New York customers. Another can show inactive customers.
The moment you start duplicating the same kind of data into multiple tables, you create a maintenance nightmare. If someone changes their phone number, which table do you update?
The answer should be one table, always one source of truth.
I see a lot of people coming from Excel do this because they'll have an active customers list and an inactive customers list. And those are represented by two different sheets in their Excel workbook.
And they'll just move a customer from one sheet to another one. In Access, you wouldn't do that. You would just have an active or inactive checkbox, and then your query can show you which one you want.
Now, where do forms and reports fall into all this?
Well, forms are what your users work with on the screen. Reports are what you print, email, or save as a PDF.
A lot of forms and reports are based on queries instead of directly on tables. Why? Because the query prepares the data exactly the way you want it.
For example, you might have a mailing label report that should only print active customers. You make a query that shows only active customers, then you base the report on that query.
Same with forms. You might have a form that shows only open orders or only unpaid invoices.
The table holds everything, the query narrows it down, and the form or the report presents it nicely.
So your big takeaway for today is: tables store, queries ask.
A table is where the data lives. A query is how you ask for the data you want.
If you need a different list, a different sort order, a calculation, or information from multiple tables, you probably want a query.
And if you want to learn exactly how to build tables and queries step by step, watch my Access Beginner 1 class. In it, I walk you through building a customer table, entering data, sorting and filtering, building queries, forms, and reports from scratch.
It's over four hours long. It teaches you all the basics, and it's absolutely free. You'll find it on my website and on my YouTube channel.
Now, if you found today's video helpful, make sure you like and subscribe and post a comment down below. Let me know how you use queries in your database or what still confuses you about tables versus queries.
Believe it or not, lots of beginners are confused by this. It's not just you. Trust me, I get this all the time.
Now that's going to be your TechHelp video for today, brought to you by Access Learning Zone. I hope you learned something.
Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the primary purpose of a table in Microsoft Access?
A. To store the actual data
B. To print formatted documents
C. To ask questions about data
D. To create charts
Q2. What is the primary purpose of a query in Microsoft Access?
A. To permanently archive records
B. To ask for and display specific data
C. To replace forms
D. To store backup copies of tables
Q3. Why might a query look similar to a table?
A. Both can be displayed as rows and columns in Datasheet view
B. Queries always contain copied table data
C. Tables are created from queries automatically
D. Queries cannot display calculations
Q4. A normal select query usually does what with the data it displays?
A. Deletes the original table records
B. Creates a separate permanent copy of every record
C. Shows a live view of data stored in tables
D. Moves records from one table to another
Q5. If you change a customer's phone number in an editable query, what usually happens?
A. Only the query display changes
B. The phone number in the underlying table changes
C. A new customer record is created
D. The query stops working
Q6. Which is the best design for tracking active and inactive customers?
A. Create separate tables for active and inactive customers
B. Move customers between tables when their status changes
C. Keep one customer table and use an Active field with queries
D. Create a new table for each customer status
Q7. Which situation is a good reason to create a query?
A. You need to permanently store a new customer
B. You need a list of Florida customers sorted by last name
C. You need to define the fields in a customer record
D. You need to assign a primary key to a table
Q8. What information can a saved query remember?
A. Fields, sort order, and criteria
B. Only the table name
C. Only the primary key value
D. Only the report layout
Q9. Which task can a query perform besides filtering records?
A. Combine information from multiple tables
B. Replace all tables in the database
C. Automatically print every record
D. Store a separate copy of each field
Q10. A query can calculate a value such as total price by using:
A. Quantity multiplied by unit price
B. A second copy of the order table
C. A new table for every invoice
D. A form's caption
Q11. Why is it usually a bad idea to create separate tables such as CustomerFlorida and CustomerNewYork?
A. Tables cannot contain customer records
B. It duplicates data and creates maintenance problems
C. Queries cannot use customer data
D. Access allows only one customer table
Q12. What is meant by "one source of truth" in a database?
A. Each kind of data should have one primary storage location
B. Every user should have a separate copy of the data
C. Every query should create a new table
D. Data should only be stored in reports
Q13. What are forms primarily used for in Access?
A. Giving users a controlled, friendly screen to view and edit data
B. Permanently storing records
C. Replacing all queries
D. Defining relationships between tables
Q14. What are reports primarily used for in Access?
A. Printing, emailing, or saving formatted output as PDF
B. Storing customer data
C. Setting primary keys
D. Replacing forms
Q15. Why might a report or form be based on a query instead of directly on a table?
A. The query can prepare only the needed records and fields
B. Tables cannot contain records
C. Queries always prevent users from editing data
D. Reports can only use queries with calculations
Q16. Which statement best summarizes the relationship between tables and queries?
A. Tables store data, and queries ask for useful views of that data
B. Tables and queries both store separate copies of the same data
C. Queries store data, and tables print reports
D. Forms store data, and tables ask questions
Answers: 1-A; 2-B; 3-A; 4-C; 5-B; 6-C; 7-B; 8-A; 9-A; 10-A; 11-B; 12-A; 13-A; 14-A; 15-A; 16-A
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.Summary Today's video from Access Learning Zone explains the difference between tables and queries in Microsoft Access.
The simplest way to remember the difference is this: tables store your information, and queries ask questions about that information.
This is one of the most common points of confusion for new Access users. Tables and queries can both appear as rows and columns in Datasheet View, so at first glance they may seem to do the same thing. However, they serve very different purposes in a database.
A table is the permanent home for your data. If you are tracking customers, the Customer table is where the customer records actually live. If you are tracking orders, those records belong in an Order table. Products belong in a Product table.
Each table should generally represent one type of thing. Customers go in the Customer table, products go in the Product table, employees go in the Employee table, and orders go in the Order table.
Within a table, fields are the individual pieces of information you track. A customer record might include fields such as FirstName, LastName, Address, Phone, Email, and CreditLimit. Records are the rows in the table. One customer record represents one customer. One product record represents one product.
The important point is that the table stores the real data. It is the source of truth.
I often compare a table to a storage box. Suppose you have a box containing index cards. Each card represents one customer, and the information written on the card includes the customer's name, phone number, and other details. The box itself is the table. The cards are the records. The pieces of information written on each card are the fields.
The data belongs in that box. That is where it is permanently stored.
So why do you need queries if the information is already in the table?
The reason is that you usually do not want to look at every record and every field in a table all the time. Imagine that your Customer table contains 50,000 customers. Your boss asks to see all customers from Florida, sorted by last name.
You could open the Customer table, filter it for Florida, sort by last name, hide columns that are not needed, and review the results. However, if your boss asks for that same list tomorrow, you would have to repeat all of those steps.
Then perhaps the next request is for customers with credit limits above $1,000. The following week, someone may ask for customers who have not placed an order in the past year.
You should not have to constantly rearrange and filter the same table every time someone needs a different list. That is what queries are for.
A query is a saved question that you ask your data.
For example, you might create queries that ask Access:
Show all customers from Florida.
Show all orders placed last month.
Show all active customers.
Show customers with a credit limit above $1,000.
Show customers who have not ordered within the last year.
A query remembers the fields you want to see, the sort order you want to use, and the criteria that determine which records should be included. Once you save the query, you can run it again whenever you need that same result.
Using the storage box analogy, the tables are your boxes containing the real items. Queries are like different lists you prepare based on what is in those boxes. One list might be alphabetized. Another might show only electronic items. Another might show only blue items.
The lists do not replace the boxes. They simply help you locate and organize the information in different ways.
A normal Select Query does not contain its own separate copy of your records. It displays data that remains stored in the underlying table or tables.
Think of a query like a window. A window lets you see into a room, but the furniture is not stored in the window. The furniture remains in the room. Likewise, a query lets you see selected data from a table, but the records remain in the table.
This is important because you do not want multiple copies of the same customer information scattered throughout your database. You want one trusted place where the real customer data is stored.
Queries can be live views of the data. In many cases, if you edit a value while viewing a query, you are editing the actual underlying record in the table.
For example, if you open a customer query and change a phone number, you have changed that customer's phone number in the Customer table. You are not changing a separate copy of the data. You are viewing the same record through a different lens.
For that reason, I generally do not allow regular end users to work directly with tables or queries unless they understand exactly what they are doing. Instead, I usually build forms for users. Forms provide a friendlier interface and allow me to control which fields users can view or edit.
As a developer, however, you need to understand that most queries are working directly with the live data stored in your tables.
Queries can do much more than filter records. They can hide fields that should not be displayed, such as credit limits or internal notes. They can perform calculations, such as calculating a total price from quantity multiplied by unit price. They can also combine information from multiple tables.
For example, customer names may be stored in the Customer table, while order details are stored in the Order table. A query can bring information from both tables together so that you can see customer names alongside order dates, order totals, or invoice information.
This is one of the most powerful uses of queries. You can assemble the exact information you need without copying the data into another table.
For example, suppose you have tables that track rooms, shelves, and boxes. One query could combine that information to tell you where a particular item is located. It might show that a baseball is in Room 3, Shelf 2, Box 14. The information remains stored in its appropriate tables, but the query brings it together in a useful result.
Here is the basic comparison:
Tables store records.
Queries display records based on a question.
A table is where the original data lives.
A query is a reusable way to view, filter, sort, calculate, or combine that data.
A table usually represents one type of thing, such as customers, products, orders, contacts, or employees.
A query can use one table or several tables and show only the records and fields that are relevant to a particular task.
Tables and queries can look similar because Access often displays both in rows and columns. The difference is not how they look. The difference is what they do.
Table means storage.
Query means question.
One of the biggest mistakes beginners make is creating a new table every time they need a different list. For example, someone may create separate tables named CustomerFloridaT, CustomerNewYorkT, and CustomerInactiveT.
That is not the best way to design a database.
If all of those records are customers, they should normally remain in one Customer table. Then you can create separate queries to show Florida customers, New York customers, inactive customers, active customers, or any other group you need.
Creating duplicate tables causes maintenance problems. If a customer changes their phone number, which table should you update? If the same customer exists in multiple tables, it becomes very easy for the information to become inconsistent.
The correct answer is to keep the real customer data in one table whenever possible. That table is the single source of truth.
This is a common adjustment for people coming from Excel. In a spreadsheet, someone might keep active customers on one worksheet and inactive customers on another worksheet. When a customer becomes inactive, they might move that row from one sheet to another.
In Access, you would normally keep the customer in the same Customer table and use a field such as an Active checkbox or Active status field. Then you can create one query that shows active customers and another query that shows inactive customers.
The data stays in one place, while the queries give you different views of it.
Forms and reports fit into this process as well.
Forms are generally what users work with on screen. They provide a controlled and user-friendly interface for entering, viewing, and editing data.
Reports are used for printing, emailing, or saving information as a PDF.
Many forms and reports are based on queries rather than directly on tables. The query prepares the data exactly as needed, and then the form or report presents it in a polished format.
For example, if you need a mailing label report for active customers only, you can create a query that returns only active customers. Then you base the mailing label report on that query.
Likewise, you might create a form that displays only open orders or only unpaid invoices. The underlying table stores all orders or invoices, the query narrows the records to the ones you need, and the form presents them in a useful way.
The main point to remember is simple:
Tables store the data.
Queries ask for the data you need.
If you need a different list, a different sort order, a calculation, filtered records, or information combined from multiple tables, you probably need a query.
If you want to learn how to build tables, enter data, sort and filter records, create queries, and work with forms and reports, I cover all of those topics in my Access Beginner 1 class.
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 List Tables as permanent data storage
Fields and records in Access tables
Queries as saved questions about data
Filtering and sorting data with queries
Queries as live views of table data
Editing table data through a query
Calculations and hidden fields in queries
Combining data from multiple tables
Avoiding duplicate data in multiple tables
Using queries as sources for forms and reportsArticle Microsoft Access tables and queries can both appear as rows and columns, but they serve very different purposes.
A table is where your data is actually stored. Think of it as the permanent home for one type of information. A Customer table stores customers. An Order table stores orders. A Product table stores products. Each row in a table is a record, such as one customer or one order. Each column is a field, such as FirstName, LastName, Phone, OrderDate, or Price.
The table is the source of truth. If you need to change a customer's phone number, the Customer table is where that information ultimately belongs. Good database design keeps each kind of information in its proper table so that the data is stored once and maintained in one reliable location.
A query does not normally store a separate copy of the data. A query is a saved request for information. It tells Access what records to show, what fields to include, how to sort the results, and what criteria to apply.
For example, suppose you have thousands of customers. You may need to see only customers from Florida, sorted by last name. You could open the Customer table and manually filter it, sort it, and rearrange the visible columns. However, if you need that same list again tomorrow, you would have to repeat the work.
Instead, you can create a query that says, in effect, "Show customers whose state is Florida, sorted by last name." Once saved, you can open that query whenever you need the list. The Customer table still contains all customers. The query simply provides a useful view of the customers that meet your conditions.
Queries can also answer more complicated questions. You might create a query to show customers with credit limits above a certain amount, orders placed last month, unpaid invoices, products that are low in stock, or customers who have not placed an order recently.
A query can display only the fields you need. If you are preparing a mailing list, you may want to see names and addresses but not credit limits or internal notes. A query can hide those unnecessary fields without removing them from the table.
Queries can perform calculations as well. For example, a query could calculate an order line total by multiplying quantity by unit price. The calculation can appear in the query results without needing to store that calculated value separately in a table.
Queries are especially useful when information is stored in multiple tables. Customer names might be stored in the Customer table, while order details are stored in the Order table. A query can combine those related tables so you can see a customer's name alongside their orders. This lets Access assemble the information you need without duplicating customer names inside every order record.
Although query results often look like a table, they are usually live views of the underlying data. If you open an editable query and change a customer's phone number, you are generally changing the phone number in the actual Customer table. You are not changing a separate copy stored in the query. For that reason, you should be careful about allowing users to edit data directly in tables or queries.
In most databases, users work with forms rather than opening tables and queries directly. A form provides a more controlled and user-friendly interface for viewing or editing data. A form may be based on a table, but it is often based on a query that has already selected the appropriate records and fields.
Reports also commonly use queries. For example, if you need to print mailing labels only for active customers, you can create a query that returns active customers and use that query as the source for the report. The table holds all customer records, the query selects the right records, and the report formats them for printing or saving as a PDF.
A common beginner mistake is creating a separate table for every list needed. For example, someone might create one table for Florida customers, another for New York customers, and another for inactive customers. This creates duplicate data and makes the database difficult to maintain.
If a customer changes their address or phone number, you would have to know which table contains their record and possibly update several copies. That leads to inconsistent data. Instead, keep one Customer table and create separate queries for Florida customers, New York customers, inactive customers, active customers, and any other useful views.
The same principle applies to active and inactive records. Rather than moving customers between separate ActiveCustomer and InactiveCustomer tables, store all customers in one Customer table and use a field such as Active to indicate their status. Then create queries to show active customers or inactive customers when needed.
The simplest way to remember the difference is this: tables store data, and queries ask questions about data. Tables provide the permanent structure of the database. Queries provide flexible, reusable ways to find, sort, calculate, combine, and display the information you need. |
