Introduction

Welcome! Address Queries & Employee Tables

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Access Beginner Level 6. In this course we will build on previous lessons by working with queries to identify customers with complete or missing address information, create reports and mailing labels based on those queries, add employee data by creating a new table, and design an employee form with pictures. We will also cover making printable name badges for employees and discuss the structure and progression of the Access course series, as well as how to get help and make the most of these lessons.

Navigation

Prev: B6
Next: Lesson 01

Keywords

Access Beginner, address queries, customer mailing labels, query null values, missing address data, employee table, employee form, employee pictures, name badge report, parameter queries, macro automation, advanced forms, building reports, SQL programming

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access 2010 Beginner Level 6 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

We are going to start today by doing some more query work. We are going to begin by making some address queries.

The first query will be to see which customers are not missing any address information. They have an address, city, state, and zip code. That way we can correct our mailing label report to only send mailings to customers where we have the complete address.

Then we will make a second query to show any customers who are missing any bit of address information. So, if someone is missing a zip code, for example, I can print out a list to hand in to the secretary and say, hey, please call this customer and get their zip code.

Then we are going to add employee data to our database. We will start with an employee table. It will be pretty similar to the customer table we built before, but you will see some new data types on there.

We will build an employee form. I will show you how to put pictures in your forms so you can display them on the form and on a printable report. We will make a name badge report where we can make name badges for all of our employees with their picture, name, title, and a company logo.

This class follows Access Beginner Level 5, which is very important, especially for lesson 1 in today's class, where we make the address queries. Level 5 covers query criteria, null values, which are very important for today, and parameter queries.

Today's class will be using Access 2010. 2007 is almost identical. If you are using 2003 or earlier, you should still be able to follow along, but you would be better off getting my Access 2003 lessons from my website.

You are encouraged to build the database that I build in class yourself. Follow along with the videos and make the database yourself, but if you are lazy like me and you want to just download a copy, go to accesslearningzone.com/databases, and you can grab a copy of the database from this class and from the previous class if you want to start off from that spot.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. My beginner courses are for novice users who have little or no experience with Microsoft Access. They are designed to give you an overview of the basic features of Access and cover just what you need to know to be productive.

The expert classes are designed for more experienced users who are already comfortable with Access. Expert classes go into a lot more depth about each topic than the beginner classes do, and will cover more functions, features, tips, and techniques for power users.

Once you have mastered the expert classes, move up to the Advanced lessons. You will learn how to work with macros, automation, and many more advanced features that really add enhanced functionality and professionalism to your databases.

Finally, my Developer Level courses are designed to teach you how to program in Visual Basic for Microsoft Access. This will allow you to create the most advanced databases possible and unlock the true potential of Microsoft Access.

Each series is broken down into different numbered levels starting with level one. Each subsequent level teaches you new and different topics in Microsoft Access, building on the lessons learned in the previous classes. Once you have finished all the beginner classes, move up to the expert series, then the Advanced, and finally the Developer lessons.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more.

You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos as long as you have an active internet connection. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students may have made.

I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well. If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step.

Do not try to apply what you are learning right now to other projects until you master the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum.

Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now let's take a closer look at exactly what is covered in today's class.

In lesson one, we are going to make queries that are interested in customer address information. The first query will show us only customers who have full addresses. The second query will show us customers who are missing any bit of address information, whether it is the street address, the city, state, postal code, and so on. Then we will make a new set of mailing labels and a report for our secretary to check on customers who are missing address information.

In lesson two, we are going to build an employee table, which will be similar to our customer table, but will have some different fields.

In lesson three, we will construct our employee form.

In lesson four, we will learn how to insert the employee's picture into the record on the employee form.

In lesson five, we will make printable name badges with the employee's picture, name, title, and a company logo.

Intro

Quiz

Q1. What is the primary goal of the first query discussed in the lesson?
A. To find customers who have complete address information
B. To find duplicate customer records
C. To list employees with missing data
D. To delete incomplete customer records

Q2. What action is suggested if a customer is missing any part of their address?
A. Ignore the customer
B. Print out a list so the secretary can follow up for missing info
C. Send mailings anyway
D. Delete the customer from the database

Q3. Which data types will you see when creating the employee table in this lesson?
A. Only text and number fields
B. Only fields already used in customer table
C. Some new data types, in addition to those in the customer table
D. Only the attachment data type

Q4. What is one of the new features shown in the employee form?
A. Displaying customer addresses
B. Adding pictures of employees
C. Importing data from Excel
D. Creating mail merge letters

Q5. What report will be created using employee data?
A. A birthday list
B. An invoice summary
C. Employee time cards
D. Printable name badges

Q6. What should you do if you want to follow along with the database from the video, but do not want to build it yourself?
A. Wait for the instructor to email it to you
B. Download a copy from accesslearningzone.com/databases
C. Buy a CD with the database included
D. Look for it on social media

Q7. Which class level is intended for users interested in Visual Basic programming within Access?
A. Beginner
B. Expert
C. Advanced
D. Developer

Q8. What is recommended before trying to apply what you learn in the videos to your own projects?
A. Just try out the new concepts right away
B. Master the sample database from the video first
C. Ask the instructor to do it for you
D. Skip to the end to see the finished product

Q9. If you have questions about the class lessons, what should you do?
A. Wait for an email from the instructor
B. Post your questions in the student forums
C. Search other teaching websites
D. Restart the lesson

Q10. What two main address queries will be built in lesson one?
A. Customers with orders and customers with refunds
B. Customers with employee referrals and customers with high sales
C. Customers with full address info and those missing any part of it
D. Customers who live nearby and customers who live far away

Answers: 1-A; 2-B; 3-C; 4-B; 5-D; 6-B; 7-D; 8-B; 9-B; 10-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

Today's video from Access Learning Zone focuses on Beginner Level 6 for Microsoft Access 2010. I'm Richard Rost, your instructor.

In this lesson, the primary focus is on working with queries, especially those related to customer address information. The first project is to create a query that identifies all customers who have complete address details - that means they have an address, city, state, and zip code. This allows us to filter our mailing label reports so that we are only sending mail to customers with complete addresses.

Next, we'll set up another query to find customers who are missing any parts of their address. If a customer does not have a zip code or any other needed detail, we'll be able to generate a list that we can give to the secretary. They can then contact these customers and gather the missing information.

After working with address queries, we will move on to adding employee data to the database. This involves building an employee table, which is similar to the customer table but uses some new data types you might not have seen yet.

We will then design an employee form and learn how to include pictures on the form. These images will be visible on the form itself as well as on printed reports. We'll also create a name badge report to produce badges for all employees, each showing their photo, name, title, and the company logo.

This class is a continuation of Access Beginner Level 5. It is especially important to have some familiarity with the previous lesson, since Level 5 covers query criteria, how to handle null values, and parameter queries. These concepts are vital for understanding today's address queries.

The demonstrations are shown in Access 2010, but if you are using Access 2007, everything should look almost exactly the same. Those using Access 2003 or earlier versions can follow along, but you may benefit more from my specific lessons for Access 2003 on my website.

I encourage you to build the same database alongside the video. Try to follow along step by step. However, if you prefer, you can download the completed database from my website at accesslearningzone.com/databases. You can download databases for this class as well as previous ones so you can start at any point.

My course series is divided into four categories: Beginner, Expert, Advanced, and Developer. If you are new to Microsoft Access, start with the beginner lessons. These are meant to get you comfortable with all the essential features you need to be productive.

Once you are comfortable with the basics, move to the expert series. The expert classes go deeper into each topic and are suited for those who want to harness more of Access's features.

When you've finished the expert lessons, the next step is the Advanced series where you will tackle topics like macros and automation to add even more functionality to your databases.

Finally, the Developer Level teaches how to use Visual Basic with Access. This enables you to start programming and truly unlock Access's full power.

Each group is organized into levels, each building upon the previous one. When you complete all beginner classes, move to the Expert series, then Advanced, and finally Developer.

In addition to the main courses, I also offer seminars that focus on specific subjects such as building web-based databases, designing calendar-style forms and reports, improving database security, adding images and attachments, handling accounts payable, learning SQL, and more. Detailed information about all available seminars can be found on my website.

If you have questions about any of the topics in today's class, check out my student forums. If you're watching the lessons in my custom video player or online theater, the student forum for each lesson will appear alongside the class videos, provided you have an active internet connection. Here you'll see other students' questions and answers, as well as my responses. Feel free to post your own questions or comments. If you are watching offline, you can still visit the forums later by going to accesslearningzone.com/forums.

To get the best results, I recommend watching each lesson through once before working along with it on your own computer. Once you've watched it, return to the lesson and follow the steps to create the same database I build in the video. Don't try to immediately apply these lessons to your own, unrelated projects until you have worked through the sample database. If something doesn't make sense, watch the relevant parts of the video again, or reach out to me on the forums.

Above all, keep an open mind. While Access might seem complex at first, you'll find it's actually very approachable once you get used to it.

Here is what you can expect in each lesson of today's class:

In lesson one, we create queries to review customer address data. The first query will show customers with complete addresses. The second finds customers missing any address information. Then we'll make updated mailing labels and prepare a report for the secretary to follow up on customers needing address corrections.

Lesson two is about building the employee table, which is similar to the customer table but adapted for employee-related fields.

In lesson three, we construct an employee form.

Lesson four teaches you how to add each employee's picture into the employee form.

Finally, lesson five guides you through making printable name badges for employees, including their picture, name, job title, and the company logo.

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

Creating queries to find customers with complete addresses
Creating queries to find customers missing address info
Using query criteria to check for null values
Updating mailing label reports to use complete addresses only
Generating reports of customers missing address details
Building an employee table
Exploring new data types in the employee table
Constructing an employee form
Inserting employee pictures into forms
Making printable name badges with photos and logos

Article

Welcome to this beginner-level Microsoft Access 2010 tutorial. In this lesson, we will focus on working with queries to manage address information in a customer database, and we will also start building an employee table, form, and report, including incorporating pictures for name badges.

To begin, we will work on creating queries to manage customer address details. Often, you might need to generate mailing labels or mailing lists. However, if some customers are missing address details such as the street address, city, state, or ZIP code, sending mail to them is not possible. To address this, we will build queries that help you identify which customers have complete address information and which ones are missing any details.

Start by opening your customer table and identifying the relevant fields: usually these will be Address, City, State, and ZIP (sometimes called Postal Code). We want to create two queries. The first query will return only those customers who have all four address components filled in. The second query will show customers who are missing any part of their address information.

For the first query, create a new select query in Access. Add your customer table and then bring in the fields you need, such as FirstName, LastName, Address, City, State, and ZIP. In the criteria row for each address field, enter Is Not Null. This tells Access to only include records where each field is not empty. To require that all of these fields are filled in for a record to be included, enter Is Not Null criteria on the same row for each field. When you run this query, you should only see customers with a complete address. You can then use this query as the source for your mailing label report, ensuring you only send mailings to customers with the necessary information.

For the second query, we want to see who is missing any address field. Create another new select query, again adding the relevant customer information fields. In the criteria row for Address, enter Is Null. Then, on the next line (an OR row), in the criteria for City, enter Is Null. Repeat this pattern for State and ZIP, each on a separate line. This creates an OR query, so if any of these fields is blank, the customer will be included in the results. This is useful if you want to print out a list for your secretary or staff, so they can call and collect any missing address information.

Once you have these two queries, you can quickly and easily keep your database up to date and make sure your mailings are accurate. You can also create a simple report based on the second query, listing only those customers with missing address info.

Next, we will move on to adding employee data to the database. Create a new table for employees, similar to your customer table. Include fields such as EmployeeID (set to AutoNumber for a unique identifier), FirstName, LastName, Title, HireDate, and any other relevant data. You may also want to include a field for a photo. For the photo, use the Attachment data type in Access 2010, which allows you to store and display images for each employee.

After creating the employee table, you will want to make a form for entering and viewing employee details. Use the Form Wizard or the Form Design View in Access to build your employee form. Add text boxes for each information field and an attachment control for the employee photograph. This allows you to display the employee's details and photo on one easy-to-use form.

To add a photo in your form, simply add the attachment control and bind it to the photo field in your table. When you use the form, you can then right-click the control and choose to attach a picture file from your computer.

Once your employee form is set up, you can proceed to build a report for employee name badges. Create a new report and add the fields you wish to display, such as the employee photo, name, job title, and a company logo. You can import your logo as an image control and place it on the report. Arrange the fields so the name badge looks professional and is easy to recognize. These reports can then be printed and used as ID badges for your staff.

As you work through these steps, remember to practice by building the example database outlined here. Start with the queries to filter customer addresses, then move on to building the employee table and the forms and reports, including picture support.

Learning Access can seem intimidating at first, but with practice, you will find that creating queries, tables, forms, and reports is very approachable. If you get stuck or want to try something new, do not be afraid to experiment. Always save your work and keep copies of your database so you can go back if you make a mistake.

This lesson should give you a solid grounding in how to manage customer address data with queries as well as how to set up employee records with pictures for printable name badges. These are real-world uses that will help you create meaningful and practical Access applications. Practice, ask questions if you have them, and you will continue to grow more comfortable and skilled with Microsoft Access.

Primary Topics

customer address queries, null value handling, query criteria, employee tables, employee forms, image storage in forms, reporting, name badge creation

Secondary Topics

database building workflow, course structure overview