Video Rewind
By Richard Rost
3 years ago
Video Rewind for February 2023
It's time for another Video Rewind, which is a quick recap of all the videos I released recently. If you are interested in any of the topics listed, just click on the links below.
Links
Keywords
#shorts, #windows, #mswindows, microsoft windows, access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #videorewind
Subscribe to Video Rewind
Get notifications when this page is updated
Intro This page features the monthly Video Rewind from computerlearningzone.com, where I recap all the tutorials released in February. Highlights include working with images and Zodiac symbols in Microsoft Access, building and refining the association database, using helper tables, and managing groups and subforms. I cover tricks like evaluating math in text fields, grouping contacts in reports, troubleshooting continuous forms, and importing objects from other Access databases. There are also tips for using the split function in both Microsoft Excel and Access, storing IP addresses, sorting combo boxes, handling multiple joins, creating query aliases, managing checkboxes relationally, right-aligning list box columns, dealing with file name characters, locking fields, and working with colors in VBA. Quick Queries and other viewer questions are covered as well.Transcript Hey folks, it's Richard Rost from computerlearningzone.com. It's time for another video rewind, a quick recap of the videos that I released last month.
For a while I was doing this weekly, but weekly is too much. I can't do weekly. So we're going to do it once a month, and I have a couple from January that I still have to cover, but moving forward, this is going to be done monthly.
Now, if you like these video rewinds and you find them helpful, post a comment down below or at least like this video so I know that you're enjoying it and I'll keep making them.
We learn how to display images from the web right in our Access forms and reports. We learn how to display Zodiac signs and symbols, these guys right here, little symbols, based on a person's birthday. It'll show you what their Zodiac sign is.
We did a lot more work with the association database that I started in January. This is a database that I'm building from the ground up so you can see how a professional builds databases from scratch. An association is any kind of organization like a church, an educational institution, a social club, and more. So if you want to see how I would build a database from scratch, go start with part one and work your way up.
Part two, we continued the basic build, and in part two, we do layout of the forms for the database. In part three, we'll continue working with the tables and forms. In part four, we'll work on the person form, the family form, and the head of family combo box. In part five, we'll make a family member list, an add family member form, all kinds of different stuff.
Part six is where we build the helper form, one of my favorites. Instead of having a million little tiny tables that store things like gender or states or things like that, you put them all in one table called the helper table. Part seven, we continue with the helper stuff. We make combo boxes to pick specific pieces of information for your forms.
In part eight, we make little subforms so you can switch between things like contacts and demographics without ever having to leave the customer form, the person form. In part nine, we're going to switch families over to groups so a family can be anything. It could be a company, it could be another organization. We make it more generic and more user-friendly.
In part ten, we make a multi-level group form. So in part nine, you can open up a person's record and see what groups they're in. In part ten, we're going to go the other way, open up a group and see what people are in it. We're going to use multiple nested continuous forms. That's really cool.
We learn how to quickly change the data source of a query. So if you want to change from customer to person, you can easily go into your query and make that change without having to rebuild the whole query.
This one's a really cool trick. I show you how to do math in a text box field just like in Excel. Now, in Excel, you can type in =10+5 and Excel will do the math for you. Access can't do that, but I'll show you how to make it do that. You'll type in, for example, 5+5+5+5, and it'll pop in 20 and save that value. If you do any kind of accounting in Access like I do all the time, this is very, very handy.
I'll show you how to group specific contacts on top of a report. For example, you want James Kirk and me on top of the report and then everybody else. I'll show you how to do that. I'll show you what to check if your continuous forms aren't working. If you build a continuous form and you can't figure out why it's not working, if it looks like that, then I'll show you how to fix it.
We'll learn how to import objects from another Access database. For a little Excel fun, I'll show you how to use the text split function in Excel to split data into separate cells. So if you've got an address like that, for example, you can split it out into address, city, state, zip, and so on. Then, like I always do when I show you something cool in Excel, I show you how to do the same thing in Access. So we'll do a split function in Access. Same thing; got an address: city, state, zip. All right, use the split function.
I'll show you the best way to store IP addresses in your Access table in case you need to do that. I'll show you how to have them properly sort numerically or alphabetically, whichever you prefer.
I've brought back quick queries, which I haven't done in a while. This is where I take a whole bunch of short questions that people have sent me that don't really merit a whole video on their own, and I put them together, and we have quick queries. Here's all the topics covered in this one. You can just read them; I'm not going to read them all to you.
I'll show you how to use conditional formatting to see if a value has changed from the previous record. This is a weird request, but basically, you've got a group ID here. If it goes 1-1-1, you want to see when it changes. So now it changed to 2, it changed right away to 3, you've got a bunch of 3s, then there's a 4. See how that works? That's kind of neat.
I did a whole video talking about why you shouldn't use the first and last functions. Instead, stick to the max and min, and this video explains why.
I'll show you how to find duplicates and delete them. We'll learn how to change the sort of a combo box on the fly. So here you've got a combo box sorted by first name and then last name. We'll make two little buttons over here, little labels, you click on them, and then you can sort by last name first, then first name. That's pretty cool.
We'll see how to do multiple joins from the same table multiple times. For example, let's say each customer has a sales rep, a service tech, and maybe an instructor. Those are all from your employee table. So I'll show you how to do that: same table, multiple joins from it.
We'll learn how to create an alias in an Access query and why you'd want to. I'll show you how to calculate the max value from multiple fields. Access has functions for doing max values across a single field from multiple records, but what about multiple fields from the same record, like test one, test two, test three, and so on?
I will show you a proper relational method for handling multiple yes/no fields, check boxes, for a single record. For example, on your customer, you've got a bunch of yes/no fields, check boxes. Is active, is customer, is prospect, is on the waiting list, and so on. Well, that's not a good relational setup. If you've got 20 or 30 of them, that's just bad. So I'll show you how to do it in a subform with a properly relational setup using a second table.
This one's by popular demand. I'll show you how to make a column in a list box right align, just like in Excel. So you've got a currency value over here, and we've got it right aligned inside that column.
This one's a problem I personally had. I'll show you how to deal with weird characters in file names. I downloaded a file that I got from YouTube with my membership list in it and there was a weird character in there, and my database couldn't read it. So I'll show you how I dealt with it.
We'll learn how to lock a specific field once a value is typed into it. So they type in a credit limit, boom, it's locked. You need to manage it or edit it to change it.
We'll learn how to work with colors in VBA.
Finally, this isn't a video, but I got the Microsoft MVP award for the third time this month, so I'm very happy about that.
So that's all for February. Now it's currently March 15th, and I've already released a bunch of new videos for March. Check them out on my website. I will cover those videos in detail in the March video rewind.
If you enjoy this rewind, let me know by posting a comment down below or at least like this video. That will be your video rewind for February 2023.
Live long and prosper, and I'll see you next time.Quiz No quiz available.Summary This month's video rewind from Computer Learning Zone offers a summary of all the tutorials and lessons I released last month. While I initially tried producing these recaps weekly, I found that monthly updates are more manageable and still provide helpful overviews of everything we've covered. If you find these monthly reviews helpful, I'd appreciate it if you let me know with a like or a comment.
In February, I demonstrated how to integrate images from the web into your Access forms and reports. I also showed you how to display Zodiac signs and symbols based on a person's birthday within your database.
Much of the month was spent expanding the association database, which I started building in January. The purpose of this project is to show you how a professional database is constructed from scratch. An association database can suit a wide variety of organizations, from churches and schools to social clubs and more. I recommend starting with part one if you want to follow the entire process.
In part two, I walked through form layout basics. Part three was all about building and refining tables and forms. Part four focused on developing the person form, the family form, and adding a combo box for head of family selection. In part five, I built a family member list and an add family member form.
One of my favorite methods appears in part six, where I introduced the helper form. Instead of creating lots of small tables to store things like gender, state, or other lookup values, we can house all of those in a single helper table. This concept is further refined in part seven, where I created combo boxes linked to the helper table for easier data selection.
In part eight, I demonstrated the use of subforms, allowing you to view details like contacts and demographics without leaving the main form. Part nine took the family concept further by broadening it to "groups" so that a group could be any type of organization, increasing flexibility. With part ten, I created a multi-level group form. Now you can view all the people in a group directly and work with nested continuous forms for more advanced navigation.
I included a tutorial on quickly changing the data source of a query. If you want to switch from querying "customer" to "person," you can do so without needing to rebuild from scratch.
I also covered a neat trick to allow users to perform math directly in a text box, similar to how Excel handles calculations. This lets users enter something like "5+5+5+5" and have Access automatically calculate and save it.
Grouping contacts at the top of a report was another topic. For example, if you want certain people's names to always appear at the top, I showed you how to accomplish that. I also explained troubleshooting steps for when a continuous form does not work as expected.
I provided instructions on how to import objects from another Access database. Shifting gears a bit, there were also Excel tips, such as using the text split function to separate address data into individual fields. Naturally, I followed this with how to perform a similar split operation in Access.
Storing IP addresses in your Access database was another lesson. I showed the best ways to ensure proper sorting, whether numerically or alphabetically.
Quick queries have made a return. This feature gathers several short questions from viewers that do not warrant a full video on their own. You can find the list of topics covered in this month's quick queries within the video itself.
Conditional formatting to highlight when a field value changes from the previous record was addressed as well. For example, if a group ID shifts from one number to another, you will be able to visually catch that switch immediately.
I also discussed why you should avoid using the first and last functions in your databases, suggesting max and min instead, and offered an explanation for that recommendation.
Finding and deleting duplicate records was another useful feature this month. I also demonstrated how to change the sort order of a combo box dynamically, allowing users to choose how records are sorted.
Multiple joins from the same table in a query was another common situation I addressed, particularly useful when, for example, a customer might be associated with a sales rep, a service tech, and an instructor, all from the employee table.
I went over how to use aliases in Access queries and why they matter. For those needing to calculate the maximum value across several fields in a single record instead of across records, I provided a solution for that as well.
Handling multiple yes/no fields for a single record can get unwieldy, so I advocated for a proper relational setup using a related table and subforms, rather than piling on lots of checkboxes in a flat table.
Another common request was covered: how to right align a column in a list box, especially useful for displaying currency.
I shared a real-world issue I encountered when dealing with unusual or unexpected characters in file names, which sometimes prevents a database from processing imported files correctly. I walked through the steps I took to address this.
Locking a form field after data entry is another topic, useful when you want, say, a credit limit to become uneditable after it's set, unless specifically managed.
I also explored working with colors in VBA for those wanting to enhance user experience with visually distinct interfaces.
In other news, I was honored to receive the Microsoft MVP award for the third time this month.
That wraps up the highlights for February. The current month, March, already has a number of new videos for you to explore on my website, and I'll be covering those in the next monthly rewind. If you like these recaps, please let me know so I can continue producing them.
For a complete video tutorial with step-by-step instructions for everything I discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List This is a monthly recap video summarizing tutorial topics covered in February, rather than a specific tutorial or tech help lesson. |
