Welcome

Welcome! Partial Payments & Test DB

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

Welcome to Microsoft Access Developer Level 9. In this course we will make enhancements to the partial payments system in the order entry database, address housekeeping items like improving main menu functionality, and begin building a new test-taker database. We will discuss table structure, relationships, and how to set up new databases, then start creating supporting forms for the test-taker project, including a question form with an answer subform. We will also begin developing forms to take tests, covering filtering and assigning test taker IDs, and storing results, along with some introductory VBA and SQL techniques.

Navigation

Prev: D09
Next: Lesson 01

Keywords

Access Developer, partial payments system, order entry database, test taker database, table relationships, employee certifications, forms, VBA, SQL, answer subform, DMax, insert SQL statement, question form, referential integrity, database setup, fill in

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 9 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to do some additional enhancements and modifications to the partial payments system that we worked on in our order entry database in previous classes, level 8 and before. We are also going to start a new project today. We are going to start building a test-taker database.

Even if you have no reason to build a test-taker database for your business or personal needs, this lesson will have a lot of new information and a lot of cool stuff for you. I get a lot of requests from people who have been asking me to start a new project and show some additional ways to set up tables and relationships. So, we are going to get into starting a new project. We will go back to the order entry database, obviously, but we are going to take a little side trip here and build a brand new database.

You can use this in a lot of different types of businesses. You can use this for employee certifications and similar needs. The lessons that are going to be covered and the concepts that are going to be covered are vital.

Let me get back into our order entry system. This is one of those things where, trust me, even if you think you do not need it, watch the test-taker lessons because there is a lot of cool stuff in there.

For prerequisites, you should have taken Access Developer Level 8 and everything that comes before it, including my Beginner, Expert, and optionally the Advanced series. The Advanced series covers macros, but there is still a lot of good stuff in there with event programming.

For those of you who have never taken my classes before, they are broken up into Beginner, Expert, Advanced, and Developer. This is the Developer series, and every series is broken up into different levels. This is Level 9.

The student databases for today's class can be found at 5NNCU.com/databases.

I am recording using Microsoft Access 2016 with Office 365. However, everything covered in today's class should work all the way back to Access 2007. In fact, now that I am thinking about it, it probably will work with 2003 and before as well. I really did not do much different stuff. I think you are good all the way back to 2003. But if you are using 2003, you really should consider upgrading to 2016. It is getting kind of old now.

Alright, so let us take a look at exactly what is covered in today's class.

In lesson 1, we are going to take some time and do some miscellaneous housekeeping. There are a bunch of little things that came up over the last couple of lessons that I am going to fix today. For example, we are going to add our search form to our main menu. We are going to have it so it opens a customer when you double-click on it. We will show or hide the manager button. We have some problems when we delete a payment; it needs to mark the order unpaid. A lot of little things like that we are going to go over in this lesson.

In lesson 2, we are going to begin a new project called the Test Taker database. I have gotten lots of emails from people throughout the end of the Expert series, the Advanced series, even now in the Developer series. People have been asking me if I could take a little more time and go through setting up a new database with table layouts, structures, and relationships. It is just some more different examples rather than the customers and orders.

I do have the Relationships seminar, which is a great seminar. Watch it; it covers all the different types of relationships. I started recording a different seminar. I was going to make this Test Taker thing a separate seminar a few years ago. I started it, in fact. I recorded a bunch of video for it, which you are going to see in just a few minutes. But I was thinking to myself, this is the perfect database to show you again how to set up a new database with some different tables.

We are going to basically be building a database where students can take a test on the computer. It will ask questions, and you can have multiple choice and fill in the blank. Even though this might not be something relevant to your business or what you are using Access for, I am going to cover a lot of new techniques.

The first lesson or two is just going to be some database setup, but do not worry, I am going to add extra lessons onto the end. So that is why this lesson, today's course, is going to be a little bit longer. The first lesson or two is just table setup and relationships and things like that. But watch it because I want you to follow along with it. If you are struggling at all with table structure and relationships, then take the time. I know this lesson is about 40 minutes long. Watch it, because I go through setting up all the different tables and make sure you comprehend exactly what I am doing.

As you can see here in the screenshot, you get departments like math, English, and science. They relate to certain classes like Math 101, Math 102, Math 103. Each class can have different tests. Each test can have different answers, and there are students, as you can see in the bottom there. I want you to see how all the relationships work between the different tables. I want you to understand the referential integrity and what is going on there. So this lesson is mostly going to be review.

What we are going to do, then, when we get into the actual taking of the test stuff, we are going to get into some really cool VBA. I am going to show you how you can have the database actually ask the questions and store the results in the table. We will do some SQL and some really cool stuff. So there is just going to be a little bit of setup involved before we get to the really cool stuff, which we will do very shortly.

Alright, so enjoy this. You will notice the date. I actually recorded this back in 2016, but we are going to do more advanced stuff with this soon. So, enjoy.

In lesson three, we are continuing with the Test Taker database. We started in part two. We are going to build some supporting forms.

In lesson four, we are continuing with our Test Taker project. We are going to build a question form with an answer subform right below it, so we can very easily and quickly add new answers or change them, delete them, whatever. We will have a check box, and we can indicate which answers are correct.

In lesson five, we are continuing with our Test Taker, as part four. We are going to make a form to begin actually taking the test. We will have different combo boxes so you can pick a student, and then we will drill down department, class, test. We will disable them as we go along. When we pick science, it will filter the class and show you just the classes from that department and so on. We will make a Begin Test button that will assign the test taker ID with a little insert into SQL statement. Then we will use DMax to look it back up again so we can use it for actually taking the test. So that is what is going on in this lesson.

Intro

Quiz

Q1. What is the main focus of lesson 1 in this class?
A. Performing miscellaneous housekeeping tasks in the order entry database
B. Building a completely new database for payroll
C. Creating new forms for sales orders
D. Formatting reports for printing

Q2. What new project is introduced in this class?
A. Payroll manager database
B. Inventory tracking database
C. Test Taker database
D. Expense report database

Q3. Why is the Test Taker database relevant even if you do not need it for your business?
A. It demonstrates advanced SQL queries only
B. It covers valuable concepts for tables, relationships, and structure
C. It focuses only on VBA functions
D. It is optional and not useful for real world databases

Q4. What skills or lessons does Richard recommend as prerequisites for this class?
A. Only the Beginner series
B. Only the Expert series
C. Access Developer Level 8 and everything before it
D. No prerequisites required

Q5. In the Test Taker database, what types of relationships will be demonstrated?
A. Relationships between only two tables
B. Relationships between customers and products
C. Multiple tables such as departments, classes, tests, answers, and students
D. Relationships with external SQL servers

Q6. What kinds of question types does the Test Taker database support?
A. Only multiple choice
B. Only essay answers
C. Multiple choice and fill in the blank
D. Only numerical response

Q7. Which lesson introduces actually taking the test in the Test Taker database?
A. Lesson 1
B. Lesson 2
C. Lesson 4
D. Lesson 5

Q8. What tool does Richard mention for retrieving the newly created Test Taker ID?
A. DLookup
B. DCount
C. DMax
D. DSum

Q9. In building forms for the Test Taker database, what feature is used to show answers and mark them correct?
A. A drop down menu only
B. An answer subform with a check box
C. A static report
D. An input mask

Q10. What does Richard emphasize about following the setup and relationship lessons for the Test Taker database?
A. Skip them if you already know forms
B. Watch them closely to fully understand table structures and relationships
C. Only review them if you have time
D. Focus on VBA code only

Answers: 1-A; 2-C; 3-B; 4-C; 5-C; 6-C; 7-D; 8-C; 9-B; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Microsoft Access Developer Level 9. I am your instructor, Richard Rost.

In this class, I'm building on the partial payments system we've been developing in the order entry database. We'll make some improvements and fixes to that system based on issues and ideas from previous classes, especially Level 8. In addition, I'm starting a brand new project: a test-taker database.

Even if you have no intention of making a test-taker database for your business, I encourage you to stick around for these lessons. I've received lots of requests from students wanting to see how to set up new databases from scratch, including different approaches to table structures and relationships. We'll step away from customers and orders for a bit and explore some new techniques using this project, but do not worry, we'll come back to the order entry system as well.

The concepts covered in this test-taker project are very flexible and can apply to a wide range of businesses. For example, you could use it for tracking employee certifications and other assessments. There is a lot to learn here that's useful for any Access developer.

Before we jump in, it's important that you're up to speed with my Access Developer Level 8 class and all the classes before it, including the Beginner and Expert series, as well as the Advanced series if you want to cover macros and more event programming. If you are new to my classes, I divide them into Beginner, Expert, Advanced, and Developer levels. Each series is further divided into levels - today we are on Level 9 of the Developer series.

You can download the databases used in this class at 5NNCU.com/databases.

I'm demonstrating everything using Microsoft Access 2016 from Office 365. However, everything in today's class should work all the way back to Access 2007. In fact, aside from some minor differences, it'll probably work all the way back to Access 2003. Still, I recommend upgrading if you are using something that old.

Let me give you an overview of today's lessons.

In Lesson 1, we'll focus on some general housekeeping in the order entry system. There have been a collection of smaller items that have come up recently that I want to address. We'll be adding the search form to the main menu, setting it up so you can open a customer by double-clicking, showing or hiding the manager button, and correcting some issues related to deleting payments so the order remains marked unpaid. A lot of these are quick fixes and small improvements.

In Lesson 2, we start the new Test Taker database. Many of you have been asking for more examples on how to create new databases with different kinds of tables, relationships, and structures. Rather than always sticking with customers and orders, this is a good opportunity to walk through a different kind of project. I actually planned to make this a separate seminar a few years ago and even recorded some video for it, which I'll be using in this lesson. This database will allow students to take tests on the computer, featuring multiple choice and fill-in-the-blank questions. Even if this database model is not directly useful for your organization, the methods and strategies I cover will broaden your understanding of Access development.

At the start, the first couple of lessons focus on table structure, relationships, and database setup. Even if you feel that you're comfortable with setting up tables, I suggest watching the material carefully, especially if those areas have ever caused you trouble. I take my time and walk through the process, making sure you fully understand how each part fits together. The lesson is a little longer than usual, around 40 minutes, but it's worth it.

To give you a sense of the table structure, you will see departments like math, English, and science, which are linked to classes such as Math 101, Math 102, and Math 103. Each class can have multiple tests, each test can have multiple answers, and students are also included. The goal is to clearly show how these different tables and relationships work together, including enforcing referential integrity. This section is mostly a review, so take it as a refresher if table relationships have ever confused you.

Once the setup is finished, we move on to more advanced features. I'll show you how the database can actually administer tests, present questions to students, and record responses. There is some great VBA involved and we will be writing SQL statements to manage the data. It starts with foundation work, but we quickly get into more advanced territory.

This original material was recorded in 2016, but I'm updating it and we'll be doing even more with it now.

In Lesson 3, I continue building the Test Taker database by adding supporting forms.

In Lesson 4, I focus on creating a question form with an answer subform underneath, allowing us to add, update, or delete answers quickly and indicate which ones are correct using a check box.

Lesson 5 takes us further with the Test Taker project. We set up the form to actually begin taking a test. You'll see how to use combo boxes to select the student, then drill down through department, class, and test choices. We'll set this up so selections are filtered as you go from one box to the next. There will be a Begin Test button that uses an insert SQL statement to assign a test taker ID, and then we'll use DMax to look it up again for tracking purposes during the test process.

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

Partial payments system enhancements
Order entry database modifications
Adding search form to main menu
Opening customer records with double-click
Showing or hiding manager button
Updating order status when payment deleted
Test Taker database project introduction
Setting up tables and relationships
Building departments, classes, and tests structure
Creating student records and relationships
Maintaining referential integrity
Question and answer table setup
Building supporting forms for Test Taker
Creating question form with answer subform
Indicating correct answers with checkboxes
Creating form to begin taking a test
Filtering combo boxes by department and class
Building Begin Test functionality
Assigning Test Taker ID with SQL insert
Using DMax to retrieve new ID for test

Primary Topics

partial payments system, database housekeeping, table design, relationships, test-taker database, VBA programming, form creation, referential integrity

Secondary Topics

DMax function, SQL statements, search forms, subforms, combo box filtering, database navigation