Quick Queries #97
By Richard Rost
18 hours ago
Normalization Without a Computer Science Degree
In today's Quick Queries video, we start with a practical explanation of database normalization without academic jargon, focusing on avoiding duplicated data and building proper relationships in Microsoft Access. We will discuss recent updates to Access, working with SQL Server, troubleshooting issues like report data not displaying, macros versus VBA, tempvars in Access and SQL Server, and the differences between Access databases and web applications. Additional topics include advice on building databases for specific industries, general troubleshooting steps, best practices for storing computed data, and helpful tips for learning VBA.
Sam from Arlington, Texas (a Gold Member) asks: I watched a bunch of normalization videos on YouTube and they all start talking about First Normal Form, Fourth Normal Form, Eight-Zillionth Normal Form... and somewhere around then your eyes glaze over. Do I actually need to know all this stuff to build a good Access database
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, database normalization, normal forms, eliminate duplicate data, table relationships, SQL Server migration, pass-through queries, computed columns, Access macros to VBA, troubleshooting reports, web app vs Access, form design, beta channel features
Intro In today's Quick Queries video, we start with a practical explanation of database normalization without academic jargon, focusing on avoiding duplicated data and building proper relationships in Microsoft Access. We will discuss recent updates to Access, working with SQL Server, troubleshooting issues like report data not displaying, macros versus VBA, tempvars in Access and SQL Server, and the differences between Access databases and web applications. Additional topics include advice on building databases for specific industries, general troubleshooting steps, best practices for storing computed data, and helpful tips for learning VBA.Transcript Ever hear people talk about first normal form, second normal form, 85th normal form, all that stuff? And you think you need a computer science degree just to build a Microsoft Access database? Well, you do not.
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Today we are going to talk about database normalization without all the academic jargon. I will show you a simple, practical way to think about normalization so you can build better databases without memorizing a bunch of textbook rules.
We also have questions from YouTube, my website forums, emails, all kinds of stuff, including tempvars with SQL Server, Access versus web applications, building industry-specific databases, troubleshooting reports that depend on open forms, and lots more.
All right, let's jump in.
Before we get started, I just want to say sorry I missed last week's Quick Queries. I try to get these out every Friday so you have the weekend to watch them, but last week life just got in the way. Things were really busy and I just could not get it recorded. That almost never happens without at least one or two emails asking, "Hey, is everything okay?" Honestly, I love that. It reminds me that so many of you have made these videos part of your regular routine, and I really appreciate that.
So, this was one of those rare weeks where the universe had other plans for me, but we are back this week. We have a bunch of great questions, so let's jump right in.
Leading off today, we have Sam from Arlington, Texas, one of my gold members. Sam says, "I watched a bunch of normalization videos on YouTube and they all start talking about first normal form, fourth normal form, eight zillionth normal form. Somewhere around then your eyes glaze over. Do I actually need to know all this stuff to build a good Access database?"
Honestly, for about 95 percent of Access developers, no.
Here's what you really need to remember.
First, every table should represent one thing. What is a thing? Customers, orders, products. Do not mix different kinds of information together. Do not make one table that has multiple different types of things. Store a customer and all the data related to a customer - first name, last name, date of birth, all that stuff.
Second, do not duplicate information. If you find yourself copying the customer's name, phone number, email address, or whatever onto every appointment, work order, or invoice, stop. Store it once in the customer table and relate everything back to that customer.
The same goes for repeating data. If a customer can have five email addresses, ten phone numbers, or twenty orders, those belong in related tables, not extra columns. I do not want to see phone number one, phone number two, phone number three, phone number four, and so on.
My limit for that, and this is a personal limit, is three things. If you need more than three, for something like three phone numbers - home phone, work phone, cell phone - okay, great. If you need more than three, put it in its own table. Trust me.
I see people coming from Excel do this all the time. They have a customer sheet and an order sheet, and they copy all the customer data from the customer sheet into the order sheet for every single order, when they should set up a relationship. That brings up the third thing: relate everything together. If a customer can have many orders and an order can have many products, that is where relationships come in. That is really the heart of normalization.
Everything beyond that - first normal form, eighty-fifth normal form, six million - I know there are not that many of them, but yes, they are real, and they are useful in certain environments. Most Access developers will never need to think about them.
If you build clean tables, eliminate duplicated data, and use proper relationships, then you are already doing what matters.
Now yes, there are always exceptions based on your use case. For example, if you care where an order was shipped, you do want to copy the customer's address from the customer table into the order table so you have a history. Sometimes you do want to denormalize databases. That is a whole different topic.
If you want to learn more, I have a whole video on normalizing data specifically as it pertains to Access users. Those other videos are not wrong. They are just teaching database theory for computer science students. I am teaching practical Access development for business people. So it is just two different schools of thought.
I hope that answers your question.
Next, over on my forums on my website, this is more of just something that one of my gold members, John, noticed. He said that when you copy an object now, instead of "Copy of Table Customer," now it is "Table Customer - Copy." Subtle change. It is a tiny change, but I actually like it. It matches the way Windows copies files, so it is a little more consistent. It is only in the Microsoft 365 subscription version, not the perpetual releases. Remember, you only get bug fixes and security updates with those versions. That is why I like the subscription.
It is a small quality-of-life improvement, but I will take those whenever we can get them.
While we are on improvements, I just posted in my captain's log that they have added wide form support to Access in the beta version now. Access has always had that 22-inch maximum form size, which is really annoying because I have a pretty big monitor. Not that big, but it is a decent size. I have been wanting to make wider forms for years.
The feature is currently available in the beta channel. If you are using the beta channel, just do a Windows update and you will get it. If you are not using the beta channel and want to wait until it is in the regular current channel, it will be released in the current channel preview - they are saying July 21, 2026. It should make its way into the regular current channel after that, so everybody gets it. I have not had a chance to play with it yet. I am looking forward to it when I get a few free minutes. You know how my day always goes.
But I love this stuff, and I have to do it so I can teach you. I will be playing with it soon.
For everyone who is curious, this is what my actual setup looks like. I have a pretty wide monitor. There is my laptop. But it is not as big as the AI made it out to be.
If you are curious how to get in the beta channel, that is where you can get all the new features they are testing. Watch this video to explain how to set it up.
Next up, we have someone in my visitor forum, Hugh, which once again goes to prove that I do not just answer questions from members. Hugh wrote in asking if, once you move your tables to SQL Server, you have to stop using Access queries and replace everything with SQL Server views.
No. That is one of the nice things about linked tables. Access does not care whether the table is from a backend ACCDB file or a linked SQL Server table. Your Access queries - your existing Access queries - will continue working just fine, 99.9 percent of the time.
That is why I recommend migrating one table at a time, testing everything out, and making sure. Once everything is stable, then start looking at converting the heavy-hitting queries into SQL Server views or at least pass-through queries so that SQL Server can do more of the work. But that is optimization. That is not something that you have to do on day one.
There is a lot more in this thread. If you want to read it, I will post a link down below.
Migration does not have to be all or nothing. When I moved my personal database that I used for my business up to SQL Server initially years ago, I did it one table at a time. I started with the less important tables, like just my contact history. Then maybe the messages table, and then maybe the order details table. Slowly, one at a time, I moved them up there. After I got them all online and everything was working nice and smoothly, then I started saying, "Okay, instead of pulling all this data down and chugging all this stuff out, what can I turn into views at that point?"
Things like the orders for the active current customer, that kind of stuff, turn them into pass-through queries. Get it working first, then make it faster later. That is my philosophy at least. It does not have to all be done in one day, too. I think my migration took like six months. Once you are done, you are going to love it. It is a much smoother back end, especially if you have a bunch of users on it.
Now for this one, Gold Member Roy was having an email problem. Stick with me for just a minute. Even if you do not care about email, check this out. He started getting a CDO email error after a Windows update and was convinced Microsoft had broken something.
We walked through Gmail app passwords. There is a pretty involved conversation here. I will put a link down below if you want to read it. We went through Gmail app passwords, SMTP settings, authentication - all the usual suspects. Roy even generated a brand-new app password, which is something you sometimes have to do.
In the end, he shut everything down, restarted Access, and everything worked fine.
I know this sounds silly, and this is the lesson here. This is exactly why the first item on my Access troubleshooter is - well, the first item is backup first, which should always be your default first thing to do. But the first item on the actual list is restart Access. Shut the database down all the way, and start it back up again.
The next step is restart all Access databases. Make sure MSAccess.exe is not even running anymore. I might have three or four databases open at any given time. Make sure they are all shut down, kill the process if you have to.
Restart Office. If you have Word and Excel running too, shut them down. They all have shared DLL files; any one of those can be misbehaving. Then reboot the PC, and I mean actually do a restart of Windows. Do not just sleep mode it.
Do a restart. Do not just do a shutdown and then turn it back on again, because sometimes that puts the system in sleep mode. Make sure you select restart Windows.
Then continue on through the list of troubleshooting stuff and check your passwords and your code and all that. There is a lot of stuff on here. I will put a link to this down below. We have been building this for years.
It turned out he just needed the ON OFF switch.
Back when I used to do tech support, I had a customer on the phone who said, "The manual says press the ON OFF switch. I cannot find that." It is on/off switch. It is like the CD-ROM tray. People used to think it was the cup holder, and the mouse - one person thought it was a foot pedal. I have stories. I could go on. I am going to write a book about them someday.
While I have your attention, hit the like button for me. It helps me and the channel. It tells YouTube that people like my videos and it shows them to more people. I would appreciate it.
If you are not subscribed, what are you waiting for? Hit that subscribe button too.
Next up, we have David who asked whether SQL Server computed columns are a good idea, or whether that kind of formatting belongs in a view.
My general philosophy is that tables should store business data, not presentation. If you are building someone's full name, mailing address, or some nicely formatted display value, I would usually put that in a view or in an Access query. Instead, keep the tables with clean data in them. This gives you flexibility later to change the formatting without changing the table itself.
For example, phone numbers. I always tell you to store just the digits. Do not store the dashes and parentheses and all that stuff.
Now, computed columns absolutely have their place in SQL Server. There are performance reasons, indexing reasons, and some calculations really do belong there. But for presentation stuff like names and addresses, such as putting first name and last name together, I would lean toward a view or a query.
I am going to start including a little more SQL Server stuff in the Quick Queries because SQL Server has queries too, so it is all part of the same family. But do not worry, I am not going to make the whole thing about SQL Server. Access is still my baby. I still love Access more than anything else.
Next up, someone named Mark posted this in my visitor forum a couple days ago. He asked if there is a way to open a video file on YouTube using Access and force it to respect his ad blocker.
You can open the video in your browser and there are no ads. But if you copy the same URL from Access, it ignores the ad blocker. Is there a way around the solution or the problem?
As a YouTube creator whose livelihood depends on those ads, I would not teach anyone how to bypass them. I am sure there is a way to do it, but even if I did know, I would not share it. To me, it is no different than pirating software, movies, music, or other digital content. Creators deserve to be compensated for the work they produce.
I know some people justify it by saying, "The movie studios and musicians, they make millions of dollars." That is not like all the different small independent creators, like me, on YouTube. So many of us rely on that ad revenue to help pay the bills.
My advice is if you do not want to watch ads, I recommend YouTube Premium. It gives you an ad-free experience and creators still get compensated for their work. That seems like the best solution for everyone involved. I pay for YouTube Premium because I watch lots of YouTube, especially late at night. I love science documentaries and astronomy stuff, and some Star Trek stuff, and Rush stuff. I watch a lot of YouTube, even when I am not creating.
It is about eight bucks a month for YouTube Premium. I know a lot of people justified music piracy and stuff back in the Napster days because it was either stuff they would not have bought anyway or they already did buy it and just wanted a digital copy for their phone or iPod or whatever. That is kind of how I felt with music. Rush, for example - everyone knows I am a huge Rush fan. I had already owned every Rush album on cassette and CD, sometimes multiple times over. I had been to every concert since Roll the Bones. So I justified it, "Well, I have already legally paid to listen to this music. Downloading a copy off Napster and putting it on my digital device - I am not really stealing anything," but it is still not right to do.
That is why I like what we have today, like YouTube Music or Amazon Music, where you can just pay one price every month and listen to whatever you want. That works for everybody. Creators get paid, you can listen to whatever you want, and everybody is happy.
As far as your specific problem, technically what is happening: when you open YouTube inside an Access browser control, you are not actually using your normal Chrome or Edge browser. You are using an embedded Edge WebView control, or the older IE control if you still have that. That does not automatically share your browser extensions or ad blockers.
Even if you do use an ad blocker, that is not going to apply inside Access. As far as I know, there is no supported way to install those extensions into the embedded browser. Even if there was, I am not going to tell you. There you go. Fair enough.
Next up, Carl, one of my platinum members, was building the letter system from my Access Expert 6, and everything looked right, but the body of the letter just would not appear on the report. We went through a whole bunch of stuff and he posted a bunch of screenshots and all that.
It turned out to be two really common issues. Either the report is trying to pull data from a form that is not actually open, or the form is still dirty. In other words, you have typed into the record, but Access has not saved it yet.
That is one of the things I teach in my invoicing video. For example, you start a new order, put in the order date, a description, and then you hit print invoice - it is not able to generate the invoice because the record is not saved yet. If you get a little dirty pencil up here, this record does not exist in the table yet. That is why in this video I teach, in the invoice button, you have to put either Me.Dirty = False or Me.Refresh so this data gets committed to the table, then the invoice can generate.
Carl figured it out. Brainfart. We have all had those. Sometimes all it takes is another set of eyes, or in my case, another eye looking at the problem to figure it out.
That is what the forums are for.
Just to mention from Matt, one of my lifetime platinum members - he said, for people getting into macros, it is probably worth mentioning that macros can be converted to VBA if they choose. Macros are worth learning even if VBA is the ultimate goal.
Yeah, macros are a great stepping stone if you do plan on getting into VBA. I find once people learn a little bit of VBA, it is addicting. You are like, "Oh, I can do all that with just three lines of code! Let's learn some more." But you could start with macros as a stepping stone. There is a tool in Access to convert your macros right over to VBA.
Where is that, you might ask? Yes, I am in dark mode because it is nighttime. If you take any macro, like my Open Main Menu - all this is a button I put up here that just opens the main menu. If that is not open, I can click this and it runs that macro that opens up the main menu.
Go into Design View and say you are sick of using a macro now - which, in order to put it up here, you have to make it a macro, but this is just for demonstration purposes. There is an option right here that says Convert Macros to Visual Basic.
Do you want to add error handling? Do you want to include comments? Okay, sure. Convert. All done.
Now, let's see what happens. Now it will open up the VBA editor. If you have other code, you are not going to see your macro in here. Where did it go? Open up the Project Explorer and you will see right there this converted macro Open Main Menu. It will also show up in your navigation pane. Double click there and there it is.
It puts nice little pretty comment boxes in here and it does all the same thing with DoCmd.OpenForm, etc. You might think that all this error handling is a bit overkill, but if this thing generates an error and this is a database you are giving to someone else, this error handling will make it so it does not give you an error. It will tell you what the error is instead of crashing your database. It is just a nice little handy tool.
I used to use this a lot back before I knew how to really write VBA. Thanks for the tip, Matt. I could probably make a whole TechHelp video about that. There are lots of ins and outs to it, and some things that macros do not really do well, but that usually involves more advanced macros.
Like I said in my macro video, I tend to only use macros for things that you can only do with macros, like putting something up on the quick launch toolbar, or that startup macro that checks to see if you are in a trusted location, or really simple stuff for beginners. If it involves really complicated macro language, I do not even bother. I just put it right in VBA, but I know not everybody else does that. Maybe there is another video in that.
Of course, if you want to learn more about macros, there is the video.
Over on YouTube, we have majeedsalami7800 who says, "Thanks, Richard, could you explain if you can pass parameters like tempvars to SQL Server?"
You really cannot do that directly. Tempvars live inside your Access application, so SQL Server has no idea they exist. They are just variables sitting in memory in your Access front end.
What you can do is use the tempvar as a value for a parameter. For example, if you are building a pass-through query or calling a stored procedure, you could take the value out of the tempvar and send it to SQL Server as a parameter, but you are not really passing the tempvar itself. You are just passing the value that is stored in the tempvar.
So, if you had TempVars!CustomerID and it was equal to 42, then SQL Server is never going to see TempVars!CustomerID, it will just receive the 42.
I do not have a TechHelp video on pass-through queries yet, but I do cover them in my Access SQL Server Online Seminar. You can use a parameter. A pass-through query is basically a query you build in Access, even using VBA so you can make it whatever you want, and then that query is executed on the server.
There are lots of great ways you can use those. I will be covering them in my upcoming SQL Regular course. I will probably do a TechHelp video on them eventually, but if you want more information, here is where you go.
Next up, we have Vivek Sony saying he has created some projects in Access, but people prefer web-based applications over MS Access forms. Who are these people? That is completely subjective.
I like the way Access databases look, personally. I think a lot of websites are - they say that web pages look more presentable while MS Access forms have tiny text boxes and a lot of empty space. That is all up to you. That is how you build your database. You do not have to build tiny text boxes and lots of empty space, especially if you build them like I build them.
I like the overlapping windows. I do not like the big full tabbed document interface that is the default nowadays. That is why I teach in all my videos; I teach the windowed interface. This is how I teach them - overlapping windows. So it is not one big area. If you see what you are saying with lots of empty space, you do not need to have lots of empty space. You can fill it however you want.
I push back on the idea that web apps automatically look better. That is a design issue, not a technology issue. You can build really great-looking Access applications and you can build some really ugly web apps too. I have seen them.
The bigger question is not appearance. It is what problem are you trying to solve? If you are building an internal business application for Windows users, especially on a local area network or connected to SQL Server, Access is still the fastest and most productive development tool out there. Nothing beats it for rapid application development, hands down.
If your users need browser access or they are using Macs, tablets, or phones, then a web front end makes sense. But that does not mean you throw away your Access database. Keep Access for your office staff, your power users. Move your data into SQL Server and then build a web front end that talks to the same data.
It is not Access versus the web. In a lot of businesses, the best solution is Access and the web working together, using each tool for what it does best. That is the comparison.
The comparison is Access for your PC and web for your mobile users if you need quick phone access or tablet access. It is not one or the other.
Even if you have people traveling around the world, you could still put your data in Access. In the last seminar I showed - Access SQL Server Online Seminar - take your Access tables, put them in SQL Server on the web. I use WinHost, but you could use Azure or whatever back end you want. Then connect your Access database to it for your people in-house, or people on their laptops while they are traveling, if they have their Windows laptops.
If Joe from accounting needs to be able to pull stuff up on his phone, build a web interface for it. There are a million different tools out there for that now. I will probably do some videos on some of them one of these days. I am just so busy with the Access stuff.
Next up, we have Pancajissini (I do not know how to pronounce it). He wants me to build a pathology laboratory banana something.
First off, I noticed you basically posted the same question multiple times over the past few days. You do not have to do that. I am the only one here that reads and responds to the YouTube comments. I have a ton of great moderators on my website and they help with stuff there, but here on YouTube, it is just me. I will eventually get to it. I am working backward through time, so there are some comments from years ago that I have not gotten to yet, but I will get to it eventually. Posting the same questions multiple times does not move you to the front of the line.
As for building pathology laboratory software, I do not know the first thing about pathology laboratories. I certainly do not know enough about that business to build the database for it.
Back when I was a consultant, the Microsoft Access part was the easy part. The tables, the queries, the forms, the VBA, the SQL. That is the stuff I already knew. The hard part was learning the customer's business. I would spend days sitting with employees watching how they work, learning their paperwork, their process, their old DOS-based systems, their Excel spreadsheets they were using. I would have to learn their whole business, which I have never done for a pathology laboratory.
The closest I have come is I built a database for an insurance company once, and I learned more in one week about insurance than I ever wanted to know. I am still scarred from it. But it was a great learning experience. I can build an insurance database if I needed to today, but I am not going to!
That is why in my videos and in my classes, I teach examples that everyone can relate to, like customers, orders, and products. Everyone has bought something from a store, whether it is a lemonade stand or Walmart. Those examples make sense no matter what industry you are in.
Sometimes people post stuff in the forums on my website and they are using terms that I have no idea what they are talking about. I ask them to relate the problem to customers, products, or orders - something that I can understand so I do not have to learn their entire business just to answer their question. Once I understand the model, then it is easy to answer the question.
My advice to you is, once you understand the fundamentals of database design, you can build software for almost any business. You know your data, and you know your business better than I do. For everyone, whether it is a pathology lab, doctor's office, restaurant, auto repair shop, enterprise, duty roster, whatever, the concepts are all the same. Learn tables, relationships, queries, forms, and reports first. Then you apply those skills to whatever business you are building for, and that is how you do it.
Sorry I cannot help more, but I have a full course on my website that will teach you how to build databases. You have to add the pathology part.
It is kind of like genealogy. I did not know much about genealogy, but I was interested in it. So I sat down and learned how it works, and then once I understood it, I was able to build a database to track genealogy. I have to take the time to learn something first before I can teach you how to do it.
Next up, we have Two Spirit Guide following along with my check register video. He says he tried to use the formula credit minus debit, and the amount field was not subtracting correctly. They entered the opening balance as a credit and expenses as debits, but the numbers did not seem to come out right.
Based on what you are describing, I do not think the formula in the video is the problem. The formula should simply be credit minus debit, which gives you positive deposits and negative expenses. If it is adding the two values together instead of subtracting them, then something else is probably going on.
Double check that your query expression still says credit minus debit, and that you are actually looking at the calculated amount field, not the original credit or debit fields. If you accidentally change the expression or bound the text box to the wrong field, then you will get exactly the kind of behavior that you are describing.
This is one of those situations where this video is five years old and has 30,000 views. If there was something wrong with this video, I would have 30,000 people screaming at me that there is something wrong with this video. So this one has stood the test of time.
Double check everything. Usually, it is something misspelled, or who knows, all kinds of things can happen. The easiest thing to do - start over, start building it again, make sure you follow along, check your spelling, check your fields, all that good stuff. I cannot count how many times people have asked for help or posted something in the forums, and that is why I always say post screenshots so I can see exactly what you are doing, because people will type in what they think they are doing and I look at the screenshot and it is not what they typed in. I do not trust what people type in. I trust what is in the screenshot, but you cannot put screenshots on YouTube. I wish you could, but you cannot.
If there was something wrong with the video, if the stuff in the video did not work, the comment section would have staged a mutiny by now. So I am pretty sure the video is correct. Double check everything. It happens. The number one thing when people have problems is they spelled something wrong. I am guilty of that too. I do it all the time. That is why I try to copy and paste when possible.
Next up, Be Jones says one of his favorite ways to learn VBA is to record a macro, then read the code that gets generated. That is a fantastic way to learn VBA. I used to do that all the time myself.
I will be honest, I still do that sometimes with Word or Excel, where I do not write a lot of VBA in Word and Excel every day, like I do in Access. They have some weird ways of naming stuff. Anyway, those applications - Word, Excel, PowerPoint - have a macro recorder. Access does not have a macro recorder. Access macros are a completely different thing.
But what you can do is what I showed you a few minutes ago. Build an Access macro then use the Convert Macros to VB feature that I showed you. It is not quite as nice as a true macro recorder, but it is still a good way to see how Access translates macro logic into VBA. It is not always the most efficient, but it usually works. Great tip. Thanks.
That is about it for this week's questions.
Before you go, do not forget to stop by my website. Check out what is new. I am always adding new videos, updates, templates, random bits of Access goodness. Alex added a GitHub repo video, so that is cool stuff.
Be sure to check out my captain's log. If you are wondering when I will be releasing something, or why I have not released a Quick Queries video, check my captain's log. That is my blog. You can see here, I had some problems with the website, so I posted this. I ended up breaking it. It was supposed to be a quick upgrade, but it turned into a monster nightmare. One line of code. If I am behind schedule, I will post it in my blog. Stuff like the wide form support, things like that, so check it out if you are curious what is going on behind the scenes.
Do not forget to pick up your hat, your t-shirt, your mug. I sound like the guy at the baseball games - get your popcorn, get your peanuts, get whatever. Pick up a copy of my Access book on Amazon.
I am actually thinking of putting together a combined book of all of my Access stuff in one ebook. If you buy it, you get all the videos too. I can put that on Amazon. What do you think? Should I do that or not? It will be a decent amount of work, but you never know - might get my stuff out in front of a few more eyeballs.
Today we cleaned up the mystery behind database normalization, talked about using Access with SQL Server and even the web, looked at a few common troubleshooting gotchas, and covered when Access is still the tool that is right for the job - which is most of the time.
Post a comment down below. Let me know how you like today's video and how you plan to use some of these ideas in your own database. Do not forget to post any questions you would like to see me cover in next week's Quick Queries, which I will try to get out on Friday.
That is it for today's Quick Queries video, brought to you by Access Learning Zone. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main principle of database normalization according to the video?
A. Store related data in separate tables and eliminate duplication
B. Store all information in one large table for easy access
C. Use as many columns as possible for flexibility
D. Avoid using relationships between tables
Q2. What is an example of a "thing" that a table should represent?
A. Customers, orders, products
B. Customers and vendors in one table
C. Product sales data and employee payroll in the same table
D. Random lists of items and addresses
Q3. What should you do if a customer can have multiple phone numbers?
A. Create a related table for phone numbers
B. Add extra columns for each possible phone number
C. Limit customers to only one phone number
D. Store phone numbers as comma-separated values in one field
Q4. What is the recommended maximum number of similar fields (like phone numbers) in a table before you should create a related table?
A. Three
B. One
C. Five
D. Ten
Q5. Which of the following best describes denormalizing a database?
A. Storing a customer's address in an order table to preserve history
B. Breaking data into as many tables as possible
C. Ensuring all data is only stored once
D. Using only queries, not tables
Q6. What tool in Access can be used to convert a macro to VBA code?
A. Convert Macros to Visual Basic
B. Macro Recorder
C. Macro Analyzer
D. Visual Basic Converter
Q7. What happens to your existing Access queries if you move your tables to SQL Server and use linked tables?
A. Access queries will continue to work most of the time
B. All queries will stop working until rewritten as Server Views
C. You must re-create every query as a macro
D. Queries can only run locally, not on linked tables
Q8. What should be the first step in Access troubleshooting when something goes wrong?
A. Restart Access
B. Reinstall the database
C. Delete the table causing the issue
D. Run a virus scan
Q9. Why might using computed columns in SQL Server tables not always be recommended for things like formatting names?
A. It mixes presentation logic with data storage
B. SQL Server does not support computed columns
C. It makes tables impossible to back up
D. Formatting names only works in Access macros
Q10. Why might YouTube videos opened in an Access browser control not respect your normal browser's ad blocker?
A. The embedded browser does not share extensions with your main browser
B. The Access browser control automatically filters all ads
C. YouTube blocks videos in embedded browsers
D. Microsoft Access disables ad blockers by default
Q11. How can you ensure that data on an Access form is saved before generating a report?
A. Use Me.Dirty = False or Me.Refresh before running the report
B. Always close the form and reopen it before printing
C. Delete the record and add it again
D. Use an Access macro to refresh only
Q12. What is true about macros and VBA for a beginner Access developer?
A. Macros can be a helpful stepping stone before learning VBA
B. VBA should always be used, macros are obsolete
C. Macros cannot be converted to code
D. VBA cannot do what macros do
Q13. What is the limitation of TempVars when working with SQL Server?
A. They exist only in the Access front end and cannot be seen by SQL Server
B. They are automatically synchronized with SQL Server variables
C. They can be directly referenced in SQL Server queries
D. TempVars are stored on the SQL Server side
Q14. When might a web-based application be preferable to Access forms?
A. When users need cross-platform or remote access via browser or mobile
B. When the Access forms look better by default
C. When developing only for Windows desktop users
D. When only one user needs access to the data
Q15. What should you do if you want to apply your Access database skills to a specialized industry, like a pathology laboratory?
A. Learn the fundamentals of database design, then apply them to the specific business
B. Find a pre-built template for every industry
C. Hire a developer who knows every industry
D. Only use pre-made Access database samples
Q16. What is a common cause of issues with calculated fields not working as expected in Access, such as in a check register example?
A. The fields or expressions may be misspelled or mapped incorrectly
B. Access does not support calculated fields
C. Credit and debit cannot be stored in the same table
D. Only macros can be used for calculations
Q17. What is a good way to learn VBA in Word or Excel according to the video?
A. Record a macro and read the generated code
B. Only use the macro recorder and never look at the code
C. Manually write VBA code without any reference
D. VBA cannot be learned from macros
Q18. According to Richard, when would you optimize queries by turning them into SQL Server views or pass-through queries?
A. After the migration is stable and not on day one
B. Before testing anything
C. Only if they stop working in Access
D. Simultaneously with moving tables to SQL Server
Q19. Which of the following is NOT a core principle of database normalization as explained in the video?
A. Each table should represent one thing
B. Avoid duplicated information across tables
C. Add fields for every possible data value in advance
D. Relate tables to handle one-to-many relationships
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-C
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 Access Learning Zone, I want to put database normalization into practical, plain language for people who might be overwhelmed by all the academic rules and terminology. You do not need a computer science degree to build a useful Access database, nor do you need to memorize all the details about first, second, or higher normal forms.
Here is how I think about normalization when building an Access database. First, each table should represent a single subject: customers, orders, products, and so on. Do not combine unrelated data in the same table. Store all the fields that belong to one subject together in its own table.
Second, avoid storing duplicate information. If you find yourself retyping customer details onto every order, stop and rethink your design. Keep customer data in one spot and use relationships to connect each order back to the customer it belongs to. This same principle applies to items like phone numbers or email addresses. If customers can have an unlimited number of these, do not create lots of fields like phone1, phone2, phone3, and so on. If you need three or fewer (home, work, mobile), that can be fine, but once you need more, put them in a related table.
Many people who come from Excel are used to copying information from sheet to sheet, but database design works better when you rely on relationships. For instance, connect customer and order tables using relationships rather than duplicating fields.
Most Access developers only need these basics: clean tables that avoid duplication and use relationships to connect data. The deeper levels of normalization matter for specialized cases, but you are unlikely to need them for most Access projects.
Of course, there are exceptions. For example, if you care about recording the address where an order was shipped, it is appropriate to copy the customer's address onto the order at the time it is placed, to preserve the snapshot. Sometimes denormalized data structures are necessary.
There is a whole video on normalization principles specifically for Access users, which avoids academic jargon and focuses on practical advice.
Moving on, there is a small recent change in Access that you might have noticed if you copy objects. Instead of seeing "Copy of Table Customer," you will now see "Table Customer - Copy." This matches the way Windows names copied files. It is just a minor quality-of-life improvement available in the subscription version of Access.
Microsoft also added support for wide forms in the beta version of Access. In the past, forms had a 22-inch width limit, which can be restrictive on larger monitors. If you are on the beta channel, this feature is available now. The plan is to release it to the main channel preview around July 2026. It is a welcome improvement, and I look forward to exploring it further.
If you are curious about how to join the beta channel to access new features, I have a video that explains the process.
Now, a common question is whether you need to change all your queries when you move your Access tables to SQL Server. The answer is no, not initially. Linked tables allow your Access queries to keep working just as before. You do not have to rewrite everything as SQL Server views right away. I recommend migrating your tables one by one, testing each step, and only later optimizing by converting resource-intensive queries into views or pass-through queries for better performance. This gradual approach makes migration manageable.
On the troubleshooting side, there was a situation where someone was having issues sending emails after a Windows update and thought something was broken. After checking all the settings and restarting everything, simply restarting Access fixed the issue. It sounds basic, but restarting the application and even Windows itself often resolves many problems. Always back up your work first, then work through the troubleshooting checklist, which might include restarting Access, closing all Office applications, and rebooting your PC.
Now, regarding SQL Server computed columns, the question came up whether it is a good idea to store presentation logic like full names or mailing addresses as computed columns in the table itself. My general rule is to store just the raw business data in tables and handle formatting in queries or views. This keeps your tables flexible for future changes. Computed columns are valuable for certain calculations or indexing scenarios, but not usually for presentation formatting.
Someone also asked if it is possible to force YouTube videos to respect an ad blocker when opened via Access. As a creator who relies on ad revenue, I would never help someone bypass ads. If you want an ad-free YouTube experience, the proper solution is to subscribe to YouTube Premium, which still compensates creators. The real technical reason ad blockers do not carry over is that Access uses an embedded browser control, which does not share browser extensions from Chrome or Edge.
Now onto a common reporting issue: if your report depends on a form being open, but the form is not, or the data has not been saved yet, the report may not show correct information. Always make sure data is committed before generating reports. For example, after entering a new record, force Access to save the record by using Me.Dirty = False or by refreshing the form.
If you are starting with macros and plan to learn VBA, you can have Access convert your macros into VBA code. This can be helpful for beginners who want to see how things are done in code. While macros are great for simple tasks or quick automation, learning VBA will unlock a lot more power and flexibility.
Another question involved passing TempVars to SQL Server. TempVars only exist in Access, so SQL Server cannot see them. However, you can extract the value from a TempVar and send it as a parameter to a pass-through query or a stored procedure on the server, but the variable itself is not sent, just its value.
There was also a question about whether web-based applications look better than Access forms. I do not agree that web apps are automatically more presentable. Access forms can look great, and layout is up to the developer. The bigger issue is which tool is appropriate for your users. Access is exceptionally productive for Windows business applications. If you need remote or mobile access, then a web front end may be appropriate, but there is no need to abandon Access if you do not have to. Often, the best solution is to use Access for in-house work and pair it with a web interface for remote users, all connecting to the same SQL Server database.
Regarding requests to build industry-specific databases, for example, for a pathology lab, the process is always the same. First, learn how databases work: tables, relationships, queries, forms, and reports. Only after mastering those fundamentals should you apply them to your specific type of business. My tutorials use examples everyone can relate to, like customers, products, and orders. If you understand your business well, you can adapt those principles to any industry.
Another question involved a check register setup where someone was having problems with a formula not subtracting correctly. If your calculated field does not produce the expected results, double check your formula and ensure you are looking at the output field, not the raw input fields. Most of the time, errors like this come down to a small typo or a misnamed field.
One tip for learning VBA is to record a macro in applications like Word or Excel and then study the generated code. Although Access does not have a true macro recorder, you can convert Access macros to VBA to see how Access structures the code. It is not always the most efficient code, but it does help you understand the process.
Finally, remember to visit my website for the latest Access tutorials, updates, templates, and helpful articles. My blog, the captain's log, keeps you updated on what I am working on, upcoming features, or any schedule changes. You will also find information about my books and links to all my learning materials.
Today, we clarified the basics of database normalization in a practical context, discussed Access and SQL Server integration, went over some common troubleshooting advice, and talked about when Access is the best solution for your needs.
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 Understanding database normalization in plain English
Simple rules for practical normalization
Building single-purpose tables
Avoiding duplicated data across tables
When to create related tables for repeating data
Relating tables with proper relationships
When to denormalize (copy address for order history)
Difference between theory and practice in normalization
Copying database objects and naming conventions in Access
Access wide form support update
Migrating Access tables to SQL Server step by step
Linked tables vs views in Access with SQL Server
Troubleshooting Access errors by restarting
Storing business vs presentation data in tables
SQL Server computed columns vs views
Embedded browser controls in Access and ad blockers
Common reasons report data doesn't appear (dirty form, closed form)
Saving records in forms before printing reports
Converting macros to VBA in Access
Learning VBA from converted macros
Using TempVars in Access with SQL Server queries
Comparing Access forms vs web forms for design
Designing applications for both Access and the web
Building industry-specific databases using fundamentals
Troubleshooting check register calculations
Validating query expressions and field bindings
Learning VBA by converting and studying macrosArticle Have you ever heard people talk about first normal form, second normal form, or some other complicated-sounding database principle, and wondered if you really need a computer science degree just to build a simple Microsoft Access database? The good news is, you absolutely do not. While database normalization is an important concept, you do not need to memorize a bunch of academic rules to create solid, effective databases for most business applications.
Let us look at normalization in a practical way. To start, remember that every table in your database should represent one thing. For example, you might have a table for customers, one for products, and one for orders. Do not mix different types of information together in the same table. For instance, customer information like first name, last name, and date of birth should all be in the customers table, not spread across multiple tables or mixed in with unrelated data.
Another critical point is to avoid duplicating information. If you find yourself copying a customer's details (like name, phone number, or email address) into every appointment, order, or invoice, that is a sign you need to step back and rethink your design. Store this information once, in the customers table, and then link related information back to that customer. This not only saves space, it also makes it easier to update customer information in one place.
Watch out for repeating data too. Say you want to store multiple phone numbers for one customer: do not create columns for phone number one, phone number two, and so on. If you need more than a couple of entries (like home, work, and mobile), it is better to set up a related phone numbers table. This way, you can store as many numbers as you need without cluttering your customers table with unnecessary fields. A good rule of thumb is that if you need more than three of something, put it in a related table.
This is a common issue for people transitioning from Excel, where data might be copied from a customer sheet into every row of an order sheet. In a database, instead of duplicating data, you set up relationships. For example, a customer can have many orders, but the order just references the customer by their unique ID. Relationships allow related tables to connect, which is the heart of normalization.
You may hear about various "normal forms," but for almost all Access developers, you do not need to focus on these textbook categories. If you create clean tables that do not duplicate information and use proper relationships, your database will work well. There may be exceptions, such as copying the shipping address to the order table so you have a historical record of where each order was sent, even if the customer changes their address later. Sometimes denormalization, or intentionally breaking the rules, is useful, but that is a more advanced topic and depends on your specific needs.
When building or updating objects in Access, you might notice small changes in how the software behaves, such as the way it names new copies of tables or queries. These are small quality-of-life updates meant to make things more consistent with how Windows works.
A recent improvement worth mentioning is wide form support. Traditionally, Access has limited you to a certain maximum form width, which might have been annoying for those with large monitors. In newer updates, this restriction is being lifted for those who choose to use preview or beta versions of the software. This means you can design forms as wide as you like, which can be handy for displaying more fields and content.
Someone recently asked whether, after moving tables to SQL Server, you have to rewrite your Access queries as SQL Server views. The answer is no. Linked tables make it possible for your existing Access queries to continue working seamlessly, regardless of whether the table is an Access table or a linked SQL Server table. You only need to consider moving heavy or complex queries to SQL Server views, or using pass-through queries, when you want to optimize performance. My recommendation is to migrate one table at a time, testing as you go, rather than doing a big, risky migration all at once. Once things are working, you can optimize as needed.
When troubleshooting problems in Access, sometimes the solution is as simple as restarting Access, or even your computer. If you are having issues that seem to defy logic, such as email sending errors or inconsistent behavior, always try closing all Access databases, shutting down Office applications, and restarting Windows. Many times, this fixes odd problems caused by software or system processes not behaving correctly.
A question I get often is whether SQL Server computed columns are a good idea for formatting things such as full names or mailing addresses. My general advice is to keep tables focused on business data, not presentation. Store raw data in the tables, and handle formatting in queries or views. For instance, store just the digits in phone numbers and use queries or reports to apply formatting when you need to display them. Computed columns are most useful for calculations that belong directly in the table for performance reasons, indexing, or business logic, but not for display or formatting.
On the topic of macros and VBA, Access macros do not function quite like the macro recorders found in Excel or Word, but they can be a great learning step for beginners. Macros let you automate simple tasks, and Access has a built-in feature to convert macros to VBA code. To do this, design your macro, then use the Convert Macros to Visual Basic option. You can review the generated code, which often includes helpful comments and error handling. Once you feel comfortable with VBA, you may find yourself moving more actions from macros to VBA code for extra control and flexibility.
If you are working with pass-through queries for SQL Server, remember that Access variables such as TempVars are local to Access and are not visible to SQL Server. If you want to use a value from a TempVar as a parameter in a SQL Server query, you must extract the value and explicitly pass it in your query or stored procedure call. For example, if you have a TempVar named CustomerID, and its value is 42, you can use VBA to pass 42 to SQL Server as a parameter, but SQL Server never knows what TempVar is, only the value you supply. Here is an example of setting a TempVar and using its value:
TempVars!CustomerID = 42
Then in your VBA code, you retrieve the value like this:
Dim custID As Long
custID = TempVars!CustomerID
' Now use custID as your parameter in your SQL or pass-through logic
Sometimes people wonder if Access forms look less attractive compared to web-based applications. Whether your screens look appealing really comes down to how you design your forms, not which technology you use. Access provides plenty of ways to design professional forms, including layout tools, color options, and custom formatting. An effective approach is to use the overlapping windows mode instead of the tabbed document interface, giving users the feel of working with multiple windows on their desktop. You are not limited to tiny text boxes or wasted space, so take advantage of the design features that suit your audience.
When deciding between continuing with Microsoft Access or moving to a web application, do not think of it as an either-or decision. Access shines for internal business apps with Windows users, especially when combined with SQL Server. For remote users, such as those on phones or tablets, a web front end can be added to supplement your Access database. This hybrid approach lets your office staff use Access for rapid data entry and reporting, while mobile users get the convenience of a web interface, both working with the same central data.
If you are asked to build a database for a specific industry, like a pathology laboratory, keep in mind that the hardest part is not the Access side, but understanding the business itself. The fundamental principles you learn with customers, orders, and products apply everywhere, even if the terminology is different. If you understand the basics of tables, relationships, queries, forms, and reports, you can adapt your knowledge to any business. What you need to add is the specific understanding of how that business works, so you know what data to collect and how processes flow. If you are approached to build something for an industry you do not know, be prepared to spend a lot of time learning about their process.
Troubleshooting formulas in Access sometimes comes down to checking that your queries are set up correctly. For example, if you are creating a check register and want to calculate balances with a formula like credit minus debit, double-check that your query calculations refer to the correct fields and that the calculated field appears in your forms and reports as intended. A common source of error is accidentally referencing the wrong fields or misspelling field names. If things do not seem to work as they did in a popular tutorial or example, start from scratch and follow each step carefully, as these videos are usually well-tested and widely used.
One more tip for learning VBA is to examine the code that is generated when you convert macros to VBA. Even though Access does not provide a macro recorder like Word or Excel, converting macros is a valuable way to see how actions translate to VBA code and can help you learn how to script repetitive tasks or customize functions to fit your needs. The code may look more elaborate with added error handling, but it will give you a solid foundation for tweaking or building more advanced procedures.
In summary, the key to building strong Access databases is to represent one type of thing per table, avoid duplicating information, handle repeating data with related tables, and connect everything with relationships. Use queries and views to present and format your data. Start with macros if you are new, and move into VBA as you become more confident. When the time comes, SQL Server can work alongside Access, letting you grow your solution and reach more users if needed. With these practical guidelines, you can build powerful, flexible database solutions for any business without getting bogged down in academic database theory. Focus on clean design and continual learning, and you will find Access to be one of the most productive tools you will ever use. |
