Change Query
By Richard Rost
3 years ago
Change the Data Sources in an Access Query
A quick tip for showing you an easy way to change the data sources (tables and queries) in a Microsoft Access query.
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, change query data source, add table, table, query
Subscribe to Change Query
Get notifications when this page is updated
Intro In this video, we will look at a simple technique for changing the data source in a Microsoft Access query without losing your configured fields or settings. I will demonstrate how you can swap out a table for a query to improve performance, especially when working with large datasets, and share a quick tip to make the transition seamless without having to rebuild your query from scratch.Transcript Welcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor Richard Rost. Today's Fast Tip is literally a Fast Tip. I'm going to show you a quick way that a lot of people don't know, surprisingly, how to easily change one of the data sources in a query to something else. I'll tell you why in just a second.
This is something that I was doing myself in my own database last night, and I thought that would make a pretty cool tip because a lot of people that I've talked to don't know this. If you've been using Access for 30 years, like I have, sure, you might know this.
I have a query that I use for various purposes that checks orders against customers. I really only need to check orders for the past 30 days or 60 days or whatever, but the query that I was using for the longest time I built years ago, was just pulling in the order table. So it was going through all of the orders, which is really slow, especially over a network.
I simulated it with this order-customer setup. All this is, is you have the customer table joined to the order table. I have some customer fields down here, some order fields, and a couple of calculations.
If you're working over a network, this order table might be pulling in tens of thousands of records that you don't need. In order for this query to process the information properly, it's got to pull in all of the customer records and all of the order records. Even over my network, which is really fast and has only two machines on it, well, three, but only two actually use the database, and it's slow.
To speed things up, instead of pulling in the order table, you can make yourself a query. I made an "Order 60 Day Q" here, and again, I only have some sample records in here. If you take a look at this, it brings in the order table and the order date. I'm only showing records from the past 60 days. So the date has to be greater than Date()-60, or 60 days ago.
What will happen is this query will evaluate first before the big query does. That right there is going to save a lot of time because it's only processing these records, which is faster.
What I want to do is substitute in this big query. I want to substitute "Order 60 Day Q" instead of the order table. But here's the problem: if I delete the order table, watch what happens down here. I just lost all those fields. That's a pain. I don't want to have to go back and figure out what fields I needed and redo all that again. There's got to be an easier way.
Here's where the tip comes in. Here's the trick. Let me close this and not save changes. Go back to what I had.
You seasoned Access people who've been using Access for a while are going to be like, yeah, that's obvious. But I've talked to a lot of beginners, especially, who don't know this trick.
What you're going to do is, even though you're going to be getting rid of the order table, leave it there for now. Go to Add Tables and bring in the query that you want, which in this case is the "Order 60 Day Q." Now, it's going to form an ad hoc relationship here, which is not what you need. That's okay. We can delete that.
Now, what we're going to do is, before we get rid of this table, you're going to change anything down here that says order table to "Order 60 Day Q." Copy that, tab, paste, tab, paste.
Now, make this join here: CustomerID to CustomerID. Make that same join over this way. Click, drag, and drop. If it's an inner join or an outer join, change that. Now, we can get rid of the order table, and we didn't lose anything down here. That's the trick.
It seems common sense, but I see people all the time who were like, oh, man. Now, save this. What's going to happen is, this query will evaluate first, it'll cut down a lot of the records, and then when the big query runs and joins it to the customer table, it's going to run a lot faster.
That's it. That's your Fast Tip for today. It's not all rocket science, folks. Sometimes these Fast Tips are going to be just that, just Fast Tips.
I hope you enjoyed it and I hope you learned something. And I hope you like the new microphone. I'll see you next time. Bye-bye.Quiz Q1. What is the main issue with the original query that the instructor describes?
A. It joins unrelated tables together
B. It includes too many customer fields
C. It pulls in the entire order table, causing slow performance
D. It deletes records by mistake
Q2. Why does using a query like "Order 60 Day Q" instead of the order table help improve performance?
A. It compresses all records into a single file
B. It only processes records from the past 60 days, reducing total records
C. It increases the frequency of data uploads
D. It disables all data validation
Q3. What is the first step to replacing a table with a query in your existing query without losing your fields?
A. Delete the table and add the query
B. Add the new query while keeping the existing table in place
C. Replace the table in SQL view directly
D. Export all data to Excel first
Q4. What should you do with the ad hoc relationship that appears after adding the new query to the design?
A. Leave it and create additional joins
B. Delete the unwanted relationship
C. Change it to a lookup relationship
D. Save the relationship for later use
Q5. Before removing the original table, what must you do to preserve your field selections in the query design grid?
A. Change all fields sourced from the table to the query
B. Save a screenshot of your grid
C. Export the query results to CSV
D. Run the query once and copy the output
Q6. After updating the fields and establishing correct joins, what is the final step before saving the query?
A. Re-add the order table for backup
B. Remove the original order table from the query design
C. Add all possible indexes
D. Test all queries for integrity
Q7. What key benefit does this trick provide to Access users?
A. Maintains field selections while swapping data sources
B. Converts queries to tables automatically
C. Disables data macros by default
D. Doubles the storage space
Q8. Who is most likely to be unfamiliar with this trick, according to the instructor?
A. Advanced SQL developers
B. Beginners or those new to Access
C. Network administrators
D. Long-time Access experts
Answers: 1-C; 2-B; 3-B; 4-B; 5-A; 6-B; 7-A; 8-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.Summary Today's video from Access Learning Zone covers a quick but useful technique in Microsoft Access for improving the performance of your queries, especially when you're working with large tables over a network. I've often found that while some experienced users may already be aware of this method, many people new to Access are not, and it can save you a lot of time and frustration.
The scenario I encountered actually came up in my own database recently, and I realized it would make a great tip to share. I had a query that was originally built to check orders against customers, but it was designed years ago and simply pulled in the entire order table. When working with lots of data, particularly over a network, this can be extremely slow because the query processes every order record, even if you only need a subset.
To make things run more efficiently, you can create a separate query that filters down the order table to just the records you need, such as only those from the past 60 days. For example, I set up a query called "Order 60 Day Q" that brings in orders with a date in the last 60 days by specifying that the order date must be greater than 60 days ago. This way, when you use this in your main query, it processes far fewer records, making everything faster.
Here is where a common challenge occurs. When you want to replace an existing table in your main query with another query (like substituting the entire order table with "Order 60 Day Q"), you might be tempted to just delete the old table. But if you do that, Access will remove all the related fields in your grid below, which means you could lose all the field selections and have to recreate them from scratch.
Instead, there's a simple solution. Start by leaving the table you want to replace in place. Add the new query or table you want to use alongside it in the query designer. Then, adjust the fields in the lower part of the screen: change each field reference from the old table to the new query. Once you have updated all your field references, you can recreate any necessary joins between your tables and queries. As a final step, delete the old table from your query designer. This way, you retain all your field selections and calculations without losing any work.
Once you've made the change, your new query will first filter out unnecessary records using the more limited data set, then join what remains to the other related tables, such as your customers. The end result is a faster, more efficient query, which is especially noticeable with large tables over a network.
That's the tip for today. Sometimes the simplest changes can make the biggest difference in your workflow, especially if you regularly work with large databases. I hope you found this information helpful and learned something new.
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 Changing a query's data source in Access
Using a filtered query to speed up performance
Creating a date-limited query for recent records
Adding a query as a data source in another query
Transferring fields from a table to a query without losing them
Recreating joins between tables and queries in the query designer
Ensuring field references update when changing sources
Improving query performance by minimizing records processed |
