Quick Queries #81
By Richard Rost
18 days ago
When to Use Temp Tables Instead of Union Queries In this video, we discuss best practices for combining data from multiple tables in Microsoft Access, highlighting the limitations of large union queries and recommending the use of temporary tables for easier maintenance. We address questions on query design, join behavior, handling zero-length strings versus spaces and nulls, strategies for importing data from Excel, and tips for maintaining database stability. There are also updates about adopting dark mode, new educational content on Reddit, and creator perspectives on YouTube ads and Access as a desktop or front end tool. Additional viewer questions about reports, security, and Access usage on tablets are also discussed. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, TechHelp Quick Queries, AccessLearningZone.com, union query errors, reserved words, GROUP BY union query, temp table strategy, staging table, query design tips, outer join vs inner join, zero-length string vs null, Excel import staging, data corruption, SQL Server backend, report multi-column layout, ACCDR security, migrating legacy database, network best practices, Power Platform integration
Intro In this video, we discuss best practices for combining data from multiple tables in Microsoft Access, highlighting the limitations of large union queries and recommending the use of temporary tables for easier maintenance. We address questions on query design, join behavior, handling zero-length strings versus spaces and nulls, strategies for importing data from Excel, and tips for maintaining database stability. There are also updates about adopting dark mode, new educational content on Reddit, and creator perspectives on YouTube ads and Access as a desktop or front end tool. Additional viewer questions about reports, security, and Access usage on tablets are also discussed.Transcript Ever stack a bunch of queries together and think you're being clever, only to have it turn into a maintenance nightmare? Welcome to another TechHelp Quick Queries brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today we're going to talk about a simple way to combine data in Microsoft Access that can quietly cause problems down the road. I'll show you why this shortcut seems smart at first, and when it starts making your database harder to manage and troubleshoot.
We'll also cover a handful of other questions from YouTube, my website forums, emails, and elsewhere, including form behavior quirks, networking best practices, query design tips, and a couple of common troubleshooting issues.
Alright, let's jump right in. Joining us today is Angela, one of my developer students. She's got 13 tables with the exact same fields and wanted one combined set of totals for a list box and eventually a report. She tried a union query and hit the classic reserved word and syntax errors. Once she got it working, the last sticking point was sorting the summarized totals by account fields instead of by name. And this is a pretty long thread. Got some help in here from Juan and Alex, myself even, Raymond of course. Then we get down to the bottom here, and here's the point of the video.
You can see here with the union query, she's doing some GROUP BYs in here. And this is one of those moments where Access is politely telling you, you can do this with a union query, but you're going to hate your life. Alex mentioned the first thing here. Watch the reserved words like name, type, ID, those will blow up a query. And the error message is never as helpful as it thinks it is.
And when you start doing GROUP BYs in a union query, that's when I say stop doing the union query. GROUP BY rules will apply, but if you want account type or account ID involved, it either has to be part of the GROUP BY or it has to be wrapped in an aggregate like sum, max, min, or something like that. Otherwise, you're going to get the "not part of an aggregate function" error.
And if you're combining 13 tables, then I would honestly say a temp table is the saner route here. And that's kind of what I recommended here, better off with the temp table. I would append everything into a single staging table, then your total queries and all that stuff becomes super simple. You're not doing SQL gymnastics every time you want to add table number 14.
Juan and Raymond were both circling that same idea, and they're right. I would consolidate first, then summarize and sort. You got each of your individual queries all set; now just create a temporary table. Then you could just append the results of each of those 13 queries into that temp table. Then you can do your grouping, then you can do your sorting. It makes it a whole lot simpler.
Union queries are fine for small sets. Let's say you got employees, vendors, and sales reps. When you built your database or you're getting data from a client, they're all basically the same thing. You want to merge those all into one list, simple stuff like that. But when you start getting crazy with calculations and grouping and all that, I would use a staging table. Just trust me, use a temp table.
In fact, I would recommend this video, Sluggish Form Part Two. I specifically do talk about utilizing temporary tables to speed things up like that. I will put a link down below.
Next up, we got Lee, one of my three-year students. He's got a sports card database where his card list is showing players even when no card data existed. So, the card data is missing, but he's got players in here. Everything worked, but he wanted to know if that behavior was correct and why changing the join types didn't seem to affect the results. It turned out the issue is where the join was being changed, not the join itself.
This is a great question, and honestly, it trips up a lot of people when they're first working with joins. He posted a copy here, and he's got a better picture of it down here. Here we go.
What you're seeing is an outer join, which means Access will show you all the records from one table, even if there's no match in the other. So here's your card table, and here's your player table. So if you've got a player that doesn't have any cards, you're still going to see the player because of this join type, that outer join right there. And that's not wrong. It's all about how you want to display it and what data you want to display.
If you want to see only players who have cards, make this an inner join, double click on that and set it to where you've got to have records in both tables, which is the default join, by the way. You can change all you want in the Relationships window, but this query, what you got set up in here, is going to override that. The Relationships window doesn't automatically change joins inside queries. Queries store their own join settings. So you can flip relationships all day long and nothing changes until you edit the join directly in the query builder. Once you switch that specific join to an inner join, then boom, only players with cards will show up.
The perfect example of why understanding where the joins live matters as much as understanding what they do. And I got videos on all this stuff. Inner joins, outer joins, Access Beginner 7 covers table relationships and join fundamentals, and Expert 14 covers query joins, including inner and outer joins, in more depth.
Next up, we got Ralph, one of my Silver members, and a good conceptual question. You want to know whether Access can distinguish between a zero-length string, meaning two quotes with nothing between them, or an actual space character, which is ASCII 32. In other words, are those treated as the same behind the scenes or not?
Great question, Ralph. And this is one of those little details that ends up mattering a lot more than people think. And of course, yes, absolutely, Access knows the difference. A zero-length string is literally a string that contains nothing. You're saying, I know the value and it's blank. A space, on the other hand, is one character. It's a space, ASCII 32. It's physically something in the field. It's just a space character, like someone hits the space bar.
And neither one of those things is the same as null, which means no value at all. So that's the big trio you always have to keep straight. Zero-length string equals blank on purpose. It's like when you ask someone for their address and they say, I don't have an address. Well, now you know they don't have an address, so it's blank, as opposed to you didn't ask them for their address, so you don't know if they have an address or not, and that's null.
The example that I give in my class is my grandfather. His name was Benny Spardoodie, not Benjamin, not Ben, it was Benny. That was his birth given name, and he had no middle name. So on his dog tags, he was in the Navy, it said none where his middle name should go. In other words, they know that he has no middle name and it's empty, it's an empty string. So it's not null. Null would mean, well, I didn't ask him for his middle name, I have no idea. Null means you don't know, and empty string means I know and it's blank.
Got lots of videos about this stuff. Here's my zero-length string video. Here's my null video. Here's a very fascinating video on null math. Go watch this one, this one's really cool.
Next up, we got Gold member Brian. He's adding an Excel import feature, but the spreadsheet is real names and values instead of foreign key ID numbers his table actually stored. He asked if the right approach is to import first, then translate the values into IDs, and then append into the real tables.
Yeah, you're thinking about it the right way, Brian. I would do it the same way. Import the spreadsheet into a temporary staging table first. Then run an update query that looks up the matching IDs from your related tables and fills in the foreign keys into foreign key fields, so you have to have both fields in that table. And then when you do an append into the real table, you're not trying to cram text values into an ID field, you've got the actual ID fields there. And then ones that you don't have a match for, you can easily match those up. Just give me a query that shows all the ones where the new ID is null, for example.
And of course Donald gave you some great links to some videos here, thank you Donald. But yeah, real world imports usually need some kind of a cleanup. Spreadsheets almost always come with junk rows, weird formatting, surprise blanks. People just type random notes anywhere in the spreadsheet because they can, because Excel doesn't have that many guardrails in it. So having that staging table gives you a safe place to clean, validate, spot problems before they touch your actual tables.
And I agree with what I told you there in the thread. Keep the original text values from Excel in separate fields for a while because future you will thank you when someone asks, hey, where did this record come from? Six months from now, you got the old data. If it turns out down the line you don't need that stuff, you can always clean it up later. Donald pointed you to the right one. I would go check this video out here.
Quick update, not really a question on this one, but I think last week, or one of the previous videos, I was testing out dark mode. I asked everyone to let me know what they thought. I got feedback from a lot of members and regular viewers, and the response was pretty clear. The overwhelming majority of my paid members and students said they preferred dark mode. And since they're the ones inside the courses and watching the videos every day, that's the audience that I really have to optimize for.
I did get a few comments on YouTube saying they prefer the light theme, and I totally get it. Everyone's eyes are different. But at the end of the day, I'm going to go with what works best for the people who are actively using the training long term. Dark mode seems to reduce eye strain for most of them, myself included, especially during longer sessions and late night coding, which is what I end up doing a lot. So dark mode is what I'm going to stick with for now.
I appreciate all the feedback though, keep it coming. Just maybe not all caps next time. Okay, all right, watch, lay off that caps lock key. But you can print out your complaints and fold them into sharp corners. I'm just kidding, I'm just kidding. I read all the comments and complaints, and yeah, I feel you. I have one good eye, and this is so much easier to look at. So that's what I'm going with too for me.
Next up, quick update for everyone. With the blessing of the moderators over at the r/msaccess subreddit, I started posting a new series of articles called Access Explained, where I'm sharing deeper dives into Access and SQL Server topics. The response so far has been pretty good. So far, I've posted about some of the basic things like primary keys, snapshot recordsets, and SQL Server integration, trying to break them down in a clear, practical way. I figured I'd start showcasing some of the best comments and questions here in the Quick Queries series. So if you're on Reddit, check out Access Explained. If not, don't worry, I'll bring all the good stuff to you.
One of the things that I did on Reddit was I asked people to post some of their issues, some problems that they have with Access. And this one user, Keylifeguard, said the biggest problem with Access is data corruption. This is one of those topics that does come up a lot. Yes, Access can get corrupted in multi-user setups, but most of the time it's not because Access is evil, it's because it's a file-based system. If someone's opening the back end over Wi-Fi, let's say, or a shaky VPN or through questionable network infrastructure, and that connection drops mid-write, that's when you're asking for trouble.
Tom, one of the commenters, is absolutely right to bring up the infrastructure. Good switches, solid cabling, stable wired connections, and no one opening the back end directly across the internet. If you're going to do that, then you got problems. Now, if you're doing everything right and you still got corruption, then that's usually the signal you've outgrown a file share. That's when SQL Server makes sense, because it acts as the gatekeeper for the data. Users connect to SQL Server; they're not opening a raw file over a shaky network connection, so the dropped connection doesn't leave your database half-written. That's not Access failing. That's usually architecture, and it's because your database isn't in a proper environment, or you've outgrown just an Access database. In either case, it's not Access's front end problem, it's how you're storing the data on the back end.
Next up, we got Aussie Diver, and I just gotta mention this guy down here with the data GIF. That's awesome, I love that. This one's not really a question, it's more of a great story. He's been using Access since Windows 3.1, and he built and maintained databases in defense, UXO clearance (that's unexploded ordnance). I love that stuff. And now at a major hospital tracking hyperbaric treatments. The current system started in Access 97 and has upgraded ever since, and now he's dealing with bloat, legacy code, and wondering if it's time to redesign from scratch.
First of all, that's an awesome career arc. That's not your average Access journey. But this is a common scenario. A database that started life in Access 97 and has survived every upgrade. You pick up new features, you lose old controls, like the date picker, and it's still running mission-critical processes decades later. That alone says something about the platform. But at some point, patching and upgrading turns into a mess.
I've been doing that with my own database. The database I currently have running my business, I started building it in 2002 when I started this company, and it's been just slowly growing ever since. I'm sure there's a lot of bloat in there, a lot of redundant code, a lot of stuff that I built 20 years ago that could be done a whole lot better today. But it's just really time-consuming to upgrade all that stuff. I've been doing it little by little, piece by piece. By the time I do some of the older stuff, it's time to redo the newer stuff that I learned to do better.
I usually tell people, if you're going to rebuild, do it intentionally. Map out what you actually use today, not what you used in 1998. Then, yeah, just map it all out, do the best you can, and rebuild. Rebuilding from experience is way better than building from scratch the first time. That's what I usually tell people in my courses too. The stuff I'm going to teach you now in Beginner Level One, don't start building your final company database now. Wait until you're at least into the Expert series because you're going to learn stuff over the next few lessons that's going to make you go "crap, now I've got to start all over again and rebuild it." That's just the learning process. That's how it is.
I can't stop watching data. Here's a thread that started with someone saying that my YouTube videos were instrumental in learning Access, which I truly appreciate. Then the conversation shifted into ads on YouTube, who controls them, and whether creators are responsible for all those unskippable spots.
First of all, hearing that the videos actually helped someone learn from scratch is exactly why I make them. As far as Reddit goes, I told the moderators that I'm not there to self-promote, I'm not going to be posting links to my own videos and stuff like that there. I'm there to genuinely help people, that's what I'm there for. If other people choose to share links to my channel, hey, I'm not going to tackle them through the screen and stop them, but that's not why I'm there. I appreciate you sharing a link to my channel, please do, but I'm not here to self-promote.
Now, as far as the ads go, someone said, "Good grief, four unskippable ads in less than a minute." Yes, I do choose to monetize my videos, that's how creators like me keep the lights on. But I don't control how many ads YouTube injects, when they show up, or whether or not they're skippable. That's YouTube's system. If you're seeing four unskippable ads in under a minute, that's not me manually stacking commercials like a 1990s cable channel.
Personally, I pay for YouTube Premium, which is, I don't know, five bucks, eight bucks a month, and you don't see any ads at all. I think that's well worth it because I watch lots of other tutorials on YouTube. Me, I'm more into Star Trek stuff and science and things of that nature. I don't watch a lot of computer stuff, but I love it that I can skip ads, so I suggest doing that.
At the end of the day though, YouTube is primarily a discovery platform for me. It helps people find me. The ad revenue that I get, honestly, is not that huge. I make more of my money from the back end sales. I get people to come to my website and become paid members or purchase my training courses. But if you watch the ads, yes, great, thank you. If you use Premium, great. If you use an ad blocker, that's your choice. Just understand that smaller creators do depend on that revenue more than you might think, so do that as you may.
I guess it goes back to the whole software piracy thing. I know back in the day a lot of people would download pirated software because they said, "Well, the company's not really losing money out of me anyways because I wouldn't spend $300 on this software product, so I might as well just use it for free because they're not losing potential revenue." I disagree with that, but that's what a lot of people think and that's why downloading songs on Napster was so popular. People were like, "Well, I wouldn't have bought this album anyways for $30 or whatever it was just for one song, so they're not losing any money out of me." Well, okay, if that's what you want to tell yourself, that's okay then, I guess.
Next up, we got Roadet, and down here Roadet is hoping that my Access Explained series will clearly distinguish between Access as a standalone file-based application, Access as a front end for systems like SQL Server, and the broader idea of SQL databases existing completely outside of Access. That's a great point, and yes, the distinction absolutely matters.
Access as a standalone application, meaning Access as a front end with an Access back end on a network share, is fantastic for small offices. Three, five, maybe ten users; if the workload is light and the network is solid, that setup works beautifully. I've set up hundreds if not thousands of companies with that kind of setup and it works great. Once you get beyond that or you need higher reliability and scalability, that's where Access really shines in its professional role as a front end to SQL Server, and that honestly is what I spend a lot of time doing.
At its core, Access is a rapid application development tool. It's an incredibly efficient way to design forms, reports, and business logic, and you can use that to connect to serious back end systems. Of course SQL databases stand on their own completely independent of Access. Access is just one client, but the beauty is you can prototype and build your solution quickly in Access and then move the data to SQL Server when you're ready. Then, after that, if you want to add web or mobile interfaces, you can do that later without throwing anything away. Your desktop users can still keep their Access front end, and then the other platforms can connect to the same data. That separation is definitely something I plan on emphasizing more as we move forward in the series. Thank you very much for the comment.
Alright, quick interruption. While I got your attention, if this video is helping you out, hit that like and subscribe and click the bell so you know when I release new stuff.
Alright, let's head over to YouTube and see what we got there.
First up, we got Brian. He's asking whether Access is going to be viable long term, especially with everything moving toward Azure and Power Platform, and whether I have any plans to cover training around that. Great question.
Short answer, yes, I get this all the time. Access is still very viable, especially as a front end tool on the desktop. Now, Microsoft is clearly investing heavy in Azure and the Power Platform, but that doesn't mean Access is going anywhere. In fact, Access works really well alongside SQL Server and Azure backends. I spend a lot of time teaching how to use Access as a front end to SQL Server. I just started a new course in that, in fact, and that skill directly translates into cloud-hosted databases too.
I do plan to cover a lot more of SQL Server integration and Azure over time, but I still think if you're building for the desktop for most small, mid-sized businesses on your local network on-premises, Access with SQL Server can't be beat. There's no better designer of forms and reports and VBA and all that as Access. You just can't beat it.
As far as Power Platform goes, yeah, I've got some stuff planned for that. Working on SQL Server right now, but that's on my roadmap.
Next up, we got JCWin, asking if I can do more videos on different report use cases. They're comfortable with forms and datasheets, don't print much, and work in county government. They're wondering what real advantage reports have beyond grouping and totals.
Good question. Reports aren't just for printing invoices and mailing labels. Think of reports as presentation tools. Forms are for working with data; reports are for presenting it cleanly. Even if you never print a single page, reports are still great for exporting to PDF, emailing summaries, creating dashboards, embedding subreports for grouped layouts in forms, and controlling exactly how data is formatted and displayed.
As far as different use cases, that's actually why I started the TechHelp series in the first place: to cover more situational topics. In my full course, I can't realistically cover everything, so I picked customers and orders and stuff like that because most people are familiar with retail, at least at some level. We've all purchased things from a store, so we understand how customers and orders work.
I can't realistically cover every industry scenario. I don't know how county government workflows are because I've never worked in county government, so I don't know. I've done some work for municipalities when I was a consultant, but everything's different. Every local area is different. Every municipality is different.
If you've got a concrete example of what you're trying to present, post it, and I can speak to the design side of it. At the end of the day, reports shine when you need structured, polished output instead of just rows on a screen. I cover a lot more about them in my Access Expert Level 19 course, so that would be a good start right there.
Next up, this user is asking if you can split a long field across two columns on a report, basically like a newspaper layout. So a long recipe continues in a second column instead of flowing onto a new page.
Normally, you can, if you've got mailing labels, for example, or you've got a whole bunch of little fields, you can create a second column. Just like the mailing label template wizard that comes with Access, it'll create two or three, or however many columns you need. But if you're talking about the same field, then no. You can't easily get that to go multi-column like you can, for example, with a Word document. Multi-column reports work at the record level, not at the field level.
So if you absolutely had to fake it, what you could do is a little text slicing in VBA. You would have to take your long text field and then break that up into multiple smaller fields, and then you could force those into different columns. I'd probably do it with a temporary table and then just break up the big long text field into paragraphs and put each paragraph in a separate record. This would actually make a pretty good TechHelp video, so I'm going to add this to my list. I don't think I've covered something like this before, but that's a great question. Keep checking the website.
Alright, next up, Tony Ache. What I think Tony Ache is trying to say is that they renamed their database to an ACCDR file to hide the tables, but the tables are still somehow accessible, and they're wondering if that's the right approach.
What the ACCDR is: that's runtime mode. When you rename a file to ACCDR, Access opens it without the normal design interface, but that doesn't actually do anything to secure the tables. Anyone who knows what they're doing can still get to the data and even your design as well. Hiding tables or renaming the file is not real security.
If you need to protect your data, you're looking at a proper front end/back end split and ideally moving to SQL Server, where you can control permissions. This isn't really good security.
ACCDE lets you encrypt it and it prevents them from getting into design view. They can't mess with your forms. They can't see your VBA code. An ACCDR just gives you the runtime version, but they can still get into your tables. With Access by itself, there's not a lot you can do to secure the data in the database. That's why you should never store things like credit card numbers or security information in there.
You can encrypt it, you can scramble it, you can use your own little algorithm. I actually have a whole seminar on that where I teach you how to just encrypt the data. Let's say you got one field in your database you want to hide, like social security numbers or something, you can scramble it with a little algorithm. But if you want proper security, you need to use SQL Server.
Okay, next up, we got: How can you use SQL? Yeah, okay. He or she is asking how to put an Access database on a tablet.
Access only natively runs on Windows. So if your tablet is a Windows device, like a Surface, running full Windows (not the mobile version they got), then you can install and run Access just like you would on a laptop. If you're talking about an iPad or an Android tablet (I have an Android tablet that I use), you can't run Access directly on those.
So in that case, you can Remote Desktop into a Windows machine. I do that a lot when I'm traveling. I Remote Desktop into my actual Windows PC, and then it just transfers the screens. So that's another way you could do it.
You can also move your data into SQL Server and build yourself a web or front-end app on some other platform. That's fine too.
One important thing, even if you do have Access on a Windows tablet: don't use an Access backend for that if you're connecting wirelessly. You don't want to run Access wirelessly. I talk about this all the time: running Access over Wi-Fi is asking for corruption--corruptions, in fact, multiple corruptions will happen. So if the tablets are going to be hitting the data over a wireless network, that's a strong case for using SQL Server as the backend.
Okay, okay. Don't forget to smash that like and subscribe.
We got Access Day coming up Friday, March 27th in Redmond, Washington. Check it out, there's the link. Make sure you stop by my website, check out what's new. Got new videos listed there, new courses. Got my SQL Server Beginner course. It's free, it's going on right now. While you're there, don't forget to get on my mailing list.
Don't forget to check out my Captain's Log, where I post thoughts about whatever I happen to be thinking that day. We got some Star Trek stuff. We got more Star Trek stuff. Some logic stuff. Once in a while I throw in a computer topic.
There's the merch store. Recently a couple of users were like, "How do I find the merch store?" Well, there's the link right there. But I made sure to add a link right there on the homepage too. It was on this little drop down thing, which is the stuff that I didn't think anybody cared about, we put on here. But everyone's like, "Where's the merch store?" So I put a link right there for the merch. Okay, it's easier to find now. You can get your t-shirts and your hats and all this stuff.
You know what? Honestly, I don't make a lot of money on this. It's just cool. I just love seeing like, "Oh, someone bought one of my hats." So I know there's people out there walking around with my logo on it. So that's just neat. I just like it. It's cool. Thank you. I appreciate it. Whenever I travel, whenever I fly, I always wear one of my AccessLearningZone shirts, or at least a Computer Learning Zone shirt, to the airport, hoping that eventually one of these days someone's going to recognize me and go, "Hey, you're that guy on YouTube." So far, no, no one's said anything, and I'm sad. So if you do see me or anybody wearing one of my shirts, make sure you say hi. Be nice.
So today, we learned when combining query results makes sense and when it's better to simplify your design. I think temporary tables are usually better than big, giant, long union queries. For big queries, break them into smaller, saved queries. Keep your logic easier to maintain.
We also looked at a few troubleshooting tips and some best practices to keep your database stable and easier to manage.
Post a comment down below and let me know how you like today's video, how you plan to use this stuff in your database, and drop any questions you'd like to see me cover in next week's Quick Queries.
So that's going to be your Quick Queries video for today. I'm brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time. Enjoy your weekend.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also be sure to subscribe to my channel (which is completely free) and make sure you click that bell icon and select "All" to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mention in the video can be found in the description text below the video. Just click on that "Show More" link right there. YouTube's pretty good about hiding that, but it's there, just look for it.
Now, if you have not yet tried my free Access Level One course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below that you can click on. And did I mention it's completely free?
If you like Level One, Level Two is just one dollar. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there's hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus you get access to my code vault, where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks, plus they get all of my beginner courses--all of them, from every subject--and you get one free advanced or developer class every month after finishing the expert series.
You can become a Diamond Sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video, brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today.
Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is generally recommended when combining data from many tables with similar structures in Access, especially if you plan to summarize and sort the results?
A. Use a single large union query combining all tables directly
B. Create a temporary staging table and append data from each table into it
C. Store everything in Excel for final processing
D. Use only make-table queries for each table separately
Q2. What is a likely issue you might encounter when using UNION queries that include GROUP BY clauses with fields not in an aggregate function?
A. An error stating too many records returned
B. The "not part of an aggregate function" error
C. Access will automatically handle non-aggregate fields
D. All records will be sorted alphabetically without warning
Q3. When would using a UNION query be appropriate according to the video?
A. When combining many tables with extensive calculations and groupings
B. For small merges like combining employee, vendor, and sales rep tables
C. When you need the highest possible performance
D. For merging tables with different field structures
Q4. Why is it important to be cautious about using reserved words like "name", "type", or "ID" as field names in Access queries?
A. They are always ignored in queries
B. Reserved words can cause unhelpful syntax errors
C. Access will automatically rename conflicts
D. Queries will run faster if you use reserved words
Q5. What happens when you use an outer join between a player table and a card table in a query?
A. Only players with card data will be shown
B. All players are shown, even those without card data
C. Only card data is shown, not player details
D. No results are returned unless both tables match
Q6. How can you control which records are displayed in a query with joins?
A. By editing the join type directly in the query builder
B. By changing relationships in the Relationships window only
C. By sorting the table alphabetically
D. By renaming the fields
Q7. What is the main difference between a zero-length string, a space, and null in Access?
A. They are all treated the same
B. Zero-length is blank on purpose, space is one character, null is unknown
C. Null contains a hidden space
D. Space and zero-length strings are identical
Q8. What is a best practice when importing Excel data with text fields that should be translated into ID numbers in your Access tables?
A. Import directly into the main table and fix errors later
B. Import into a staging table, match values using update queries, and append to the real table
C. Manually enter all data
D. Only import numeric fields to avoid this issue
Q9. What is the typical cause of data corruption in Access multi-user setups?
A. Access software bugs
B. Opening the backend database over unreliable network connections
C. Using temporary tables
D. Running nightly backups
Q10. What role does SQL Server play compared to an Access backend in multi-user environments, according to the video?
A. SQL Server is only for websites
B. SQL Server acts as a gatekeeper, managing data and preventing corruption caused by dropped connections
C. Access always performs better than SQL Server in these cases
D. Both work the same way in all environments
Q11. What does renaming an Access database to ACCDR do?
A. Encrypts the database and protects all data
B. Opens Access in runtime mode, removing design features but not securing tables
C. Prevents anyone from opening your database
D. Hides all the objects from users permanently
Q12. When should you consider rebuilding your Access database from scratch, based on the discussion?
A. After every minor version update
B. When bloat, redundant code, and legacy features make maintaining the old database more effort than rebuilding
C. When you lose a password
D. When you have less than 100 records
Q13. Which of the following is NOT a reason to use Access reports even if you do not print?
A. Exporting to PDF
B. Emailing summaries
C. Directly editing data in table view
D. Creating professional dashboards
Q14. If you want a field in an Access report to flow from one column to another like a newspaper layout, what is the recommended approach?
A. Use multi-column records directly; Access handles it automatically for long fields
B. Break up the text into separate records or fields using VBA or a temp table
C. Print to Word and finish the formatting there
D. There is no way to do this in any database
Q15. Which are REAL advantages of using Access as a front-end for SQL Server or Azure, as discussed in the video?
A. Access acts as a rapid design tool while SQL Server/Azure ensures reliability and scalability
B. Access can only be used for a single user environment
C. SQL Server requires a completely new front-end every time you upgrade
D. Access cannot connect to cloud data sources
Q16. What happens to table joins in a query if you change the relationship type in the Relationships window but do not edit the join in the query design?
A. The query's join type is overwritten by the Relationships window changes
B. The query retains its join settings regardless of changes in the Relationships window
C. Access always warns you about mismatched joins
D. Query results will be unpredictable
Q17. Why is it not enough to rely on ACCDR or hiding tables in Access for protecting sensitive data?
A. Hiding tables encrypts them
B. Experienced users can still access all tables and data
C. ACCDR fully blocks access to all forms and reports
D. Access by default prevents all data export
Q18. What should you avoid when connecting a Windows tablet to an Access database?
A. Using a wired network
B. Running the Access backend over Wi-Fi, due to corruption risk
C. Using SQL Server as the backend
D. Installing Microsoft Office updates
Q19. According to the video, what is a common characteristic of long-running Access databases that have been upgraded multiple times over the years?
A. They usually become slimmer and more efficient
B. They tend to accumulate bloat and redundant code
C. They require fewer updates
D. They lose all user data with each upgrade
Q20. What is the presenter's advice regarding learning Access and building your final business database?
A. Build your final database as soon as you finish Access Beginner Level One
B. Wait until you have more experience and knowledge before starting your final design
C. Refrain from taking any courses until you have database experience
D. Use only sample databases provided online
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-A; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-C; 14-B; 15-A; 16-B; 17-B; 18-B; 19-B; 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.Summary In today's Quick Queries video from AccessLearningZone.com, I'm taking you through a collection of Microsoft Access questions and practical scenarios that commonly come up among users and developers. My goal is to clear up some frequent misconceptions, highlight important best practices, and share strategies that can save you headaches in the long run.
I'm starting with an important topic: combining data from multiple tables using union queries. On paper, stacking up a bunch of queries to create a single summarized result set looks like a smart shortcut, especially when you have tables with identical fields. One of my students found out the hard way that as clever as this sounds, it quickly becomes a maintenance mess. She built a union query with several GROUP BY clauses but then ran into reserved word and syntax errors, and sorting the results by the right field became a major headache. If you find yourself needing to aggregate and sort data from a large number of tables, a temporary or staging table is almost always a better solution. By appending data from each source into a single temp table, you simplify your queries and make it much easier to maintain or expand your setup - no more "SQL gymnastics" every time you need to add a new table. Several experienced developers in our community agree: consolidate first, then summarize and sort.
A smaller union query is fine if you're just merging data from a few similar sources - like employees, vendors, and sales reps. But when your queries call for complex grouping and calculations, move to a staging table. I recommend checking out my Sluggish Form Part Two video for more details on this technique.
Next, I tackled a question from a student whose database listed players without related card data. This is a classic join scenario: Access will display every player if you've got an outer join set up, even when no matching card exists. If you want to see only players with cards, you need to use an inner join. Changing the relationships in the Relationships window won't affect a query's results - queries store their own join settings. So, make sure you're adjusting the joins directly inside your queries. For a deep dive on this topic, my Beginner Level 7 and Expert Level 14 lessons are great resources.
Another great conceptual question: Can Access tell the difference between a zero-length string ("") and a single space character? The answer is yes, and understanding it is critical. A zero-length string means "blank by choice": the field is deliberately empty. A space character (ASCII 32) is still a value, just not visible on the screen. These are also distinct from null, which means "no value" at all. It's important to understand the difference between "blank," "empty," and "unknown." I use real-life examples in my classes to make it clear, and I have dedicated videos about zero-length strings, null values, and null math if you want to explore those.
On the topic of importing data from Excel, another student asked if it's best to import text values first, translate them to proper foreign key IDs, and then append to live tables. This is the right approach. Import your spreadsheet into a temporary table. Then, use update queries to match up text values with their corresponding IDs from your lookup tables, storing both the imported text and the new ID in the staging table. That way you can easily spot and fix mismatches. Always keep your original text fields for reference until you're certain you don't need them. Excel imports frequently need lots of cleanup, so a staging table is enormously helpful in catching and correcting messy data before it affects your production tables.
A quick update about the channel: after testing dark mode, I've received overwhelming support from my paid members who find it easier on the eyes, so I'm going to stick with it moving forward. Thanks to everyone who provided feedback.
I've also started an "Access Explained" article series on Reddit with the moderators' blessing, focusing on foundational Access and SQL Server concepts. The feedback from the community has already highlighted some key challenges, such as database corruption. Access works well for small teams but can suffer corruption when the data file is stored on unreliable networks or accessed across VPNs or Wi-Fi. This isn't Access being "bad" - it's the result of file-based architecture. The solution is to use reliable infrastructure, or, when you outgrow Access as a backend, move to SQL Server. SQL Server acts as a gatekeeper, making multi-user access robust and stable.
One user shared the evolution of a mission-critical Access database that dates back to Access 97, used for tasks as important as hospital treatment tracking and UXO clearance. Databases like this often accumulate bloat and legacy code over the years. My advice is to rebuild intentionally: analyze what you truly use today, plan accordingly, and only then refactor or rebuild. Experience-based redesigns are always more efficient than starting from nothing.
Some viewers have wondered about advertisements on YouTube. I monetize my content to support channel operations, but YouTube itself determines the quantity and timing of ads. If you find the ads intrusive, YouTube Premium is a good way to avoid them entirely. Most of my teaching work is supported by memberships and training sales, so ad revenue is only a small part of the equation.
A relevant point brought up on Reddit is the distinction between Access as a full file-based system and Access as a front end to SQL Server or another SQL database. For small groups on a stable network, Access as a standalone app is great. For larger teams or those requiring scalability and reliability, Access excels as a front end for SQL Server. Access is a powerful rapid application development tool, and it can connect to just about any backend database. You can keep your Access front end for desktop users and expand into web or mobile apps later, all using the same backend.
Let's turn to a few quick questions from YouTube. First, is Access viable moving forward with the market shift toward Azure and Power Platform? Absolutely. Access works extremely well with SQL Server and complements modern cloud services. I teach many ways to use Access as a desktop front end to SQL Server and plan to add more Azure and Power Platform lessons in the future.
Another viewer asked about exploring additional use cases for Access reports beyond basic grouping and totals. Reports are valuable as output and presentation tools - not just for printing. They are crucial for producing polished PDFs for email, summary dashboards, or embedding grouped layouts in forms. If you have specific needs, send them in, and I'll be happy to address them. My Access Expert Level 19 course covers many of these reporting scenarios.
Someone else wondered about splitting a long text field into two columns on a report, like a newspaper layout. Access reports can display multiple columns, but only at the record level - not for the contents of a single field. To achieve a multi-column effect for a single long text, you'd need some custom VBA to break the text up, store it in separate records, and display those across columns in a temporary table.
A common confusion involves renaming an Access database to ACCDR format to "hide" tables. Doing so opens Access in runtime mode and hides the design interface, but it isn't real security. Anyone with the skill can access your tables. If you need security, splitting your front end and back end is a start, but you should look into SQL Server to control data access properly. Even ACCDE format, which blocks code and design editing, does not prevent data access. For securing especially sensitive fields, I have training on simple encryption methods, but SQL Server is the serious solution.
Another person asked about putting Access on a tablet. Access only runs on full Windows devices, such as Surface tablets or similar. For an iPad or Android tablet, you need to use Remote Desktop into a Windows PC or build a web or mobile front end, perhaps connecting to a SQL Server backend. Never run Access over wireless with an Access backend - it increases the risk of corruption, and that's where SQL Server comes in.
For upcoming events, Access Day is happening March 27th in Redmond, Washington, and I'm continually releasing new courses, including a free SQL Server beginner series. Be sure to get on my mailing list for updates. There's also a Captain's Log for my off-topic thoughts and a merch store - just a fun way for you to represent the channel if you'd like.
To sum up: I covered when it's wise to combine query results and when you're better off using temporary staging tables for large, complex operations. We also reviewed troubleshooting joins, understanding empty and null values, import best practices, handling Access on new devices, and recognizing when it's time to rebuild a legacy database.
Let me know what you thought and send in your own questions for the next Quick Queries. If you need more help with Access, including tutoring or development, check out my Access Developer Network.
You can find a complete step-by-step video tutorial covering everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Combining data from multiple tables using union queries
Issues with GROUP BY in union queries
Reserved word and syntax errors in queries
Using aggregate functions with GROUP BY
Advantages of using temporary tables for consolidation
Appending multiple queries into a staging table
Simplifying queries by consolidating before summarizing
Choosing between union queries and temp tables
Understanding inner vs outer joins in Access
How query join settings override relationship settings
Differences between zero-length strings, spaces, and nulls
Handling Excel imports with real values vs IDs
Using staging tables for data cleanup before appending
Keeping original import values for auditing
Causes of Access data corruption in multi-user setups
Network infrastructure considerations for database reliability
When to migrate to SQL Server for stability
Dealing with legacy Access databases and bloat
Approaching database redesigns strategically
Distinction between Access as standalone vs front end for SQL Server
Using Access for rapid application development
Access viability alongside Power Platform and Azure
Advantages of using reports for presenting data
Solutions for multi-column text fields in reports
Differences between ACCDR and ACCDE file formats
Limitations of hiding tables using ACCDR files
Access database options for tablet devices
Access and Wi-Fi/networking considerations
Moving data to SQL Server for wireless or cross-platform useArticle If you have ever tried stacking a bunch of queries together in Access to combine data, it may feel clever at first. But as you keep expanding and modifying your database, you may quickly find yourself facing a maintenance nightmare. There is a shortcut for combining data in Microsoft Access called a union query, which can be very tempting because of its simplicity. However, union queries, especially when you find yourself grouping, sorting, or combining results from more than a couple of tables with the same fields, can silently make your database more difficult to troubleshoot and manage.
Union queries work by taking the results of two or more select queries that share the same field types and number, and placing them on top of each other, essentially stacking the data into a single unified result. For example, if you have three tables named Employees, Vendors, and SalesReps, all with the same structure, and you want to see all people at once, a union query is a great solution.
However, problems start when you try more advanced tasks like grouping or totaling the data in a union query. Suppose a developer named Angela was tasked with combining the totals for thirteen tables with identical structures and showing the results in a single list box and report. She set up a union query, struggled with reserved word and syntax errors, but eventually got it working. The final challenge was sorting the grouped totals by account fields rather than by name. This is a common stumbling block: union queries can be finicky around reserved words like "name", "type", or "ID", and the error messages in Access are often less helpful than you wish.
With union queries, if you want to use fields like "account type" or "account ID" in your grouping or sorting, they must be included in your GROUP BY clause or wrapped in an aggregate function like SUM, MAX, or MIN; otherwise, you will run into the "not part of an aggregate function" error. When dealing with 13 tables, union queries become unsustainable. A better approach is to use a temporary staging table. You create a temporary table with your desired structure, then append the records from each of the thirteen tables into it. This way, you can easily group and sort the results, and when you need to add a fourteenth table, you just add one more append query, not mess with complex SQL logic.
Union queries are fine for small datasets where you are not doing complex summarization, calculations, or grouping. But for anything larger, especially with repeated use or many tables, a temporary table keeps things simple and maintainable. The process, in short: create a staging table, append the data from each table into it, then run your summary queries against the staging table.
Another common question in Access relates to joins, specifically, join types in queries. Consider a situation where you have a card database that always shows players, even when no card data exists. This usually happens because you're using an outer join in your query design: an outer join will display all records from one table (such as Players) and any matching data from the related table (such as Cards). Even if a player has no matching card data, they're still shown. If you only want to see players with cards, you need an inner join, which only returns records where both tables have matching records. Changing join types in the Relationships window doesn't affect your existing queries; you must adjust the join type directly within your query design. Understanding where and how joins are set is just as important as knowing what each join type does. If your query isn't behaving as you expect, double-check your join settings in the query itself.
Another question often asked is whether Access treats a zero-length string (""), a space character (" "), and a Null as the same thing. The answer is no, and this difference is important. A zero-length string is an explicit, blank value, showing that the field was asked for and answered with nothing. A single space is physically a blank character with ASCII value 32, and Null means the value is unknown or missing. For example, if someone's middle name is truly blank, you'd set a zero-length string; if you never asked, it remains Null. Access knows and respects these distinctions. When writing queries or VBA, you often use Is Null, = "", or compare to " " (single space) to test for these cases.
When importing Excel data into Access, the common situation is that the spreadsheet uses descriptive names while your Access tables store foreign key IDs. The best approach is to import your spreadsheet into a temporary staging table, then run update queries to match each name with its corresponding ID from your lookup tables before finally appending the cleaned data into your live tables. This lets you validate the import, resolve any unmatched values (such as missing customers or products), and keeps your main tables tidy. For safety and troubleshooting, keep the original text values from the spreadsheet in your staging table. You can always delete or archive these extra fields later once you're certain all the data has been accounted for and translated.
As for Microsoft Access and its long-term usage, many wonder about Access's future in a world of cloud platforms and Azure. Access remains a highly effective tool for desktop data management and rapid application development. While Microsoft does invest heavily in Azure and Power Platform, Access still works very well with SQL Server and cloud-hosted databases. Access is at its best as a front end for local databases, especially for small to medium offices. For larger or more distributed scenarios, pairing Access as a front end to SQL Server increases stability and scalability.
On the topic of report design, Access reports are not just for printing invoices or mailing labels. Reports are powerful presentation tools, perfect for summarizing, grouping, and controlling the exact look and formatting of your data, whether you print them, export to PDF, or email summaries. While multi-column reports in Access are record-based, not field-based, you can use report layout features like columns for mailing labels or similar records. However, if you want to split a long text field into multiple columns (like a two-column newspaper), Access can't do this natively at the field level. To split a long text across report columns, you would need to parse the text using VBA into logical chunks (like paragraphs), then arrange those as separate records in a temporary table, and base your multi-column report on that.
Regarding security with Access databases, changing a file from ACCDB to ACCDR puts Access into "runtime mode" but does not actually secure your tables or design. Anyone with full Access can still get to the database content. For real protection, you should split your database into a front end and back end and consider moving your backend to SQL Server. ACCDE does lock down forms and VBA code against design changes, but table data itself is still accessible if someone is determined. For true data protection, SQL Server is the recommended backend, where you can assign user-level security and permissions.
Access runs natively only on Windows devices. If your tablet is a Windows Surface or any tablet running standard Windows, you can install and run Access. You cannot, however, run Access natively on Android or iPad. For those platforms, your options are to remote into a Windows PC using Remote Desktop or to move your data backend to SQL Server or another cloud service, building web or cross-platform front ends as needed. If you are running Access on a Windows tablet or laptop, always use a wired or very stable connection to your backend data, not Wi-Fi. Running Access as a file share over Wi-Fi or through an unreliable network risks data corruption because Access is a file-based database at its core.
Data corruption is often raised as a significant concern for Access, especially in multi-user environments. Most corruption issues come from unstable network connections, such as users opening the backend file over Wi-Fi, VPN, or unreliable cabling. Access is not inherently flawed; it's just that the file can be left in an inconsistent state if a session is interrupted mid-write. Upgrading to SQL Server backend solves this, because SQL Server is a true database server that safely handles dropped connections and multi-user traffic. If your Access database is experiencing corruption despite a solid network, that is a good sign you have outgrown file-based Access as your backend and should consider SQL Server.
For those with old Access databases, maybe started in Access 97 and upgraded many times, it is common to have a lot of bloat and legacy code. If your database has survived many upgrades, it is usually a good time to stop patching and instead map your current workflow, analyze how your organization uses the application today, and rebuild it intentionally. Starting the rebuild with your real-world experience will result in a better, streamlined system than patching a legacy one.
On the financial side, some users are concerned with the number and type of ads on YouTube training content. Content creators like myself do not control the frequency or skip-ability of ads served by YouTube. The platform manages those entirely. The best way to avoid ads is with YouTube Premium. Supporting creators through courses, memberships, or occasionally tolerating ads all help keep the free content available.
Lastly, understanding the differences between Access as a standalone database (front end and back end both in Access), Access as a front end for something like SQL Server, and standalone SQL databases is critical. For small, local offices, Access as a standalone works well. As your needs grow, using Access as a front end and SQL Server as a backend combines ease of design with robust, multi-user data storage. Access is essentially a rapid application developer for forms, reports, and business logic, and connects well with advanced platforms when needed, letting you keep your desktop solutions in sync with newer cloud or mobile solutions.
To summarize, when combining data from similar tables, use union queries only for small, simple jobs. For anything more complex or as the number of tables grows, switch to using temporary tables for staging, cleaning, and summarizing data. Proper join usage and knowing the difference between nulls, zero-length strings, and spaces will save you many headaches. For importing data, use staging tables to translate external values to your internal IDs. Learn to split your application for better security and performance, and avoid running Access over unreliable networks.
If you are designing reports, know that Access is very powerful for presenting data, and if you have more specialized needs, such as splitting text across columns, some VBA can help you get there.
Be sure to continually assess your Access application's scope; when you outgrow the current architecture, plan your next steps methodically, and don't hesitate to rebuild with modern best practices in mind.
If you need more support with your Access database, want to connect with experienced developers, need more in-depth examples, or want to dig into topics like SQL Server integration, best Access practices, or advanced VBA tricks, there are plenty of resources online and communities to help you learn and grow. Keep exploring, experimenting, and building. Remember, careful planning and the right architecture will make your Access applications durable and much easier to maintain over the long term. |
