|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
ABCD Core Part 2 Access Business & Contact Database Core Part 2 Welcome to ABCD Part 2: Core. Total running time is 4 hours, 17 minutes. Lessons
ContentsIn lessons 1 and 2 we build the HelperF form. This allows the user to double-click on any of the combo boxes using Helper data to open a form that lets them edit or add items quickly. Then that form will update the underlying combo box and send the value back to it when closed. In lesson 3 we create the Helper Data Editor. In Lesson 4 we set up Groups so we can categorize people however we choose (students, faculty, employees, parents, etc.) In lessons 5 and 6 we set up Relations (parent/child, co-workers, etc.) Database FilesI'm only posting the 32-bit versions for now. That's what I use. If you are using 64-bit Access, just create a new blank database file and import all of my objects into yours. I'll post a 64-bit version when the Core is finished. If you experience any code that doesn't work under 64-bit let me know. Click here for more information about 32 v 64 bit. ResourcesLearn More
Pre-Requisites RequiredIn order to purchase any level of the ABCD, you MUST have purchased all previous levels. For example, you cannot buy only Level 5. You must first have Levels 1 through 4, then you can buy Level 5. Sorry, but this is a hard pre-requisite. VideosI am recording videos showing how the database is built, but mostly so that you can understand the construction and be better equipped to make any modifications or additions you may need on your own. This will be marketed as a finished database without the need for any enhancements, but, of course, one of the main reasons to use Access is so that you can make changes yourself. The videos will be a quick run-through of how each feature is built, as I'm building it. I will be using advanced features like SQL and VBA from the start, so you may want some SQL or VBA Developer Lessons under your belt if you're going to try to follow along. This is NOT a teaching database. Free Technical Support will NOT be offered. Tech SupportThere is NO free Technical Support provided on the ABCD if you have not taken ALL of my previous Developer lessons. There are a lot of very difficult, advanced programming concepts used in the construction of this database. I do not have the time to provide free support if you get stuck trying to make modifications. All I will do is point you to the appropriate Developer lesson(s) that cover the issue. There is a reason why (a) I say this is NOT a teaching database, and (b) there are pre-requisites. Feel free to ask questions, but I can't spend half an hour explaining concepts that are already covered in my other lessons. LicensingUnlike my other courses and seminars which provide you with royalty-free databases that you can modify and distribute yourself, the ABCD does NOT come with a royalty-free license. You may only customize the database for your business needs and for use within your organization. You may not resell or distribute it to others without express written permission. In addition, if you are using it in an organization with more than 10 users, you must purchase additional licenses. Contact me for additional information. I will post additional details when the Core database is completed. OrderingThere are two purchase options available for the ABCD.
Your FeedbackI will be releasing new Core features and modules on a regular basis. You can see the list of features that are scheduled for development on the Coming Soon page. I would very much like your feedback on what features you think should be available. If you have a specific interest for your business type, let me know.
IntroIn this video, we continue building the core of the ABCD (Access, Business, and Contact Database) using Microsoft Access. I will show you how to create and use a helper data form, set up composite keys, build a helper data editor, and manage groups and many-to-many relationships for people and entities. We will also cover setting up dynamic relations and automatically creating opposite relationships between records. This is part 2.TranscriptWelcome to the ABCD, the Access, Business, and Contact Database Part 2: Building the core of the database, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.Today, we are continuing to build the core of the database. In Part 1, we built a helper table so that we did not have to have a million little tables in our database, like name prefix, suffix, title, gender, and so on. In lesson 1, we are going to build a helper data form. You can double-click on a field like prefix, then the helper form will pop up and show you all the prefixes. You can then add it there. System values, of course, will be locked so they can't be changed or deleted. In lesson 2, we are then going to take that form and make it so you can select a value, click on the Set and Close button, and it will return the value to whatever form and field called the helper form. So we can then use the same form with any field in our database. We will also set up something called a composite key to prevent duplicate values in the helper table. In lesson 3, we are going to set up a helper data editor form where you will see a list of all the helper table types on the left-hand side: prefix, suffix, gender, and so on. On the right side, you will see all the values in there. This will be a double subform with continuous forms. It is neat to set this up. In lesson 4, we are going to set up groups. Each entity, each person, can be in one or more groups. We will then add those groups to a subform on the person's form, the entity form. Then we will go the other way. We will make a group list form where you can see all the groups and which people are in that group. Then we will make it so you can jump back and forth between the two of them. For example, if I have my record up, Richard Rost, I can see what groups I am in. If I want to go to the parents group, that will jump to the group list and show all the parents in that group. That is a many-to-many relationship between people and groups. Speaking of relationships, we are going to set up relations in lesson 5. Relations allow us to set up a relationship between any two people or entities. You can do parent and child, spouses, co-workers, or whatever relationships you want to set up. In lesson 6, we are going to set up opposite relationships. For example, if Sue and Doug are married and on Doug's record you put in that Sue is his wife, it will ask you if you want to put the opposite relationship in. It will then automatically go to Sue's record and put Doug in as the husband. That is creating the opposite relation automatically. Here is a quick walk-through of the new features. From the entity list, let's open up my account. As we did before, you can change any of the helper data right from here, or you can double-click. That will open up the name prefix box. Now it is the helper data. We know it is the name prefix because this guy called it. I can now add a new value in here like Mr. spelled out, hit Set and Close, and then it will update this box and drop Mr. in there. See how that works? If I decide later on I do not want that, I can come in here and delete it. Of course, this Mr. is a system value so I can't hit delete on that. I am hitting delete right now and nothing is happening. But whichever one I pick, if I pick Doctor now, hit Set and Close, it drops that in there. That is the helper data form. The cool thing is it works from every one of these helper drop-down boxes. Helper data now has an editor. This is eventually going to be part of an admin menu. We are going to set that up differently, but for now it is on the main menu. There is the helper data editor. This is two continuous forms side by side and as you click on one of these, it updates this guy. Click and it filters this list. You can add new lists down here or you can add or edit the existing lists in the system easily. We set up groups. Here is our group list. These are all the groups that we set up in the system: Employees, Parents, Starfleet, Students, Vendors. You can set up any group you want. We do not have a group editor yet. That is coming in the next part. You can go to the table right now and add more groups. When you are adding people to groups, which you can do from this form, you can drop down and pick from either their person number or you can drop this down and pick from the person over here based on name, whichever you like, and it will update the other one. Let's go to Element OP Bank and I can now double-click here and it will jump to Element OP Bank's records. Groups are now right here because we changed the tabs so they take on the color of whichever subform you are on. So I can see which groups each person or entity belongs to and I can now jump to the students group right here. Double-click. There are the students. See that? There is Richard Rost and if we double-click there, it goes to his record. So you can jump back and forth between groups and people or entities. We now have relations or relationships. I didn't use the word relationships because to me that says database table relationships, so I call them relations inside the database. Let's go to someone else. Let's go to Will Riker. Go to relations. You can see he has got Jean-Luc Picard listed as a supervisor. Then you have two relations: his wife and his subordinate. Let's add Doug Jones and Doug Jones as a coworker. When I leave that record, the database says the opposite relation does not exist. Would you like to create one now? Say yes. It goes to Doug Jones's record and adds Will Riker as a coworker. That is creating opposite relationships automatically. All of that is covered in Part 2 of the ABCD core database. There are about four hours of video total. For more information on the ABCD, there is the website. Again, this is not a teaching database so I assume you understand the basic concepts. I will show you how I am building the database step by step, but the point is so that you understand the construction so you can modify it yourself. If you do not know VBA or table relationships or any of that stuff, I strongly suggest my Beginner, Expert, Advanced, and Developer courses. I am using Microsoft 365 which is roughly equivalent to Access 2019. However, you should be good with the techniques covered in this class all the way back to about 2007. It should work with every version between then and now. Of course, if you have any questions, post them in the Access Forum. If you would like to have the ABCD customized for your business, contact me. There is my consulting page. You can also feel free to email me directly. Or if you have any questions, feel free to post them in the comments section below the video. QuizQ1. What was the main purpose of creating a helper table in Part 1 of the course?A. To avoid having separate tables for every type of lookup like prefix and suffix B. To store customer order details C. To manage inventory quantities D. To keep track of group memberships Q2. What does the helper data form introduced in Lesson 1 allow you to do? A. Add, edit, or delete prefix values directly and lock system values B. Import data from Excel C. Automatically generate reports D. Link forms to external databases Q3. What is the function of the "Set and Close" button described in Lesson 2? A. Returns the selected value to the calling form and field B. Saves the current form as a template C. Locks the current record D. Exports data to Excel Q4. Why is a composite key set up for the helper table? A. To prevent duplicate values B. To improve form design C. To allow for vertical formatting D. To enable color coding Q5. What does the helper data editor form introduced in Lesson 3 allow you to do? A. See and manage helper table types and their values B. Send emails to users C. Schedule meetings D. Encrypt sensitive data Q6. How are groups used in the database as described in Lesson 4? A. To associate each entity or person with one or more groups B. To restrict data access by user role C. To summarize sales reports D. To track document versions Q7. What kind of relationship exists between people and groups in this database? A. Many-to-many B. One-to-one C. One-to-many D. Hierarchical Q8. What functionality is provided by the group list form? A. View all groups and members, and navigate between them B. Export group data to a CSV file C. Automatically email all group members D. Create complex mathematical formulas Q9. What is the purpose of the relations feature set up in Lesson 5? A. To define relationships between any two entities, such as parent-child or coworkers B. To perform data backups C. To set up user authentication D. To manage product inventory Q10. What happens when you create a relationship like spouse between two people? A. The system can prompt to create the opposite relationship automatically B. Both records are deleted C. Access generates a report automatically D. The system logs the action for auditing Q11. Why does the instructor use the term "relations" instead of "relationships" in the database? A. To avoid confusion with Access table relationships B. Because "relations" is a newer term in Access C. To comply with Microsoft naming conventions D. Because relationships are only available in SQL Server Q12. What version of Microsoft Access is primarily used for this tutorial? A. Microsoft 365 (similar to Access 2019) B. Access 2013 only C. Access 2003 D. Access 2021 exclusively Q13. What is recommended if you do not understand basic concepts like VBA or table relationships? A. Take the Beginner, Expert, Advanced, and Developer courses B. Skip to the end of the video C. Hire a database consultant D. Only read the documentation Q14. Can the techniques in this course be used in versions of Access earlier than 2019? A. Yes, they work back to about Access 2007 B. No, only Access 2019 and newer C. Only with Access 2016 D. Only with Access 2010 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 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. SummaryToday's video from Access Learning Zone focuses on Part 2 of the Access, Business, and Contact Database, or ABCD. I am your instructor, Richard Rost, and in this lesson, we are going to continue constructing the foundation of the database.Previously, in Part 1, we created a helper table to eliminate the need for multiple small tables for things like name prefix, suffix, and gender. Instead, we centralized these into one helper table. In the first lesson of Part 2, we will set up a helper data form. This form allows you to manage values like prefix by opening a form that lists out all available options. Here, you can add new entries directly. Any system-defined values will be locked, meaning they cannot be edited or removed. The second lesson will enhance this form so that, once you select a value and click on the Set and Close button, the chosen value is returned to the original field and form from which you called the helper form. This approach allows a single form to serve all of our dropdown needs. Also, we will create a composite key in our helper table to make sure duplicate entries cannot be added. Lesson three introduces a helper data editor form, where you see all categories of your helper table, such as prefix and gender, listed on one side, with their corresponding values displayed on the other. This is designed using two continuous forms, providing a convenient way to manage all your helper data types and values in one place. In lesson four, we will incorporate groups into our database. Each person or entity in your system can belong to one or more groups. These groups will appear in a subform on the main person or entity form. There is also a group list form, where you can view all members within any group. The process is set up so you can easily switch back and forth between specific records and the groups they are part of. This arrangement effectively handles many-to-many relationships between entities and groups. Lesson five covers relations. Here, we are able to define relationships between any two people or entities in our system. These can include parent and child, spouses, colleagues, or any custom relation you require. In the sixth lesson, we introduce opposite relations. For instance, if Doug is recorded as being married to Sue, the system will prompt you to add the opposite relationship, so Sue will automatically have Doug recorded as her husband. This way, entering a relation on one record will automatically update the corresponding relation on the other record. Now let me give you a brief walkthrough of these new features. From the main entity list, you can open an account and easily update any helper data field either directly or by using the helper form. The helper form clearly indicates which category you are working on, such as name prefix. You can add new entries, like typing out "Mister" instead of simply "Mr." When applying changes, the form updates the value in the original record. System values, as mentioned earlier, are protected from deletion. You have the flexibility to pick values as you need. The helper data editor is now available through the main menu and uses two continuous forms side by side. Selecting a category on the left updates the display on the right, so you can manage values more efficiently. We have also integrated the concept of groups. The group list displays all existing groups, such as Employees, Parents, Starfleet, Students, and Vendors. You can assign people to these groups straight from the form by choosing either their name or their ID number. Switching between a group list and a specific person's group memberships is seamless, making it easy to move back and forth between related records. Regarding relations, the system now allows you to define connections between any two entities. For example, Will Riker might have Jean-Luc Picard listed as a supervisor and can also have relationships such as spouse or subordinate. When you add a new coworker and leave the record, the database will offer to create the corresponding opposite relation for you automatically. All these features are thoroughly covered in Part 2 of the ABCD core database series, which runs about four hours in total. Remember, this project is not intended as a database teaching tool; it is designed for users who already understand basic concepts. If you are new to VBA, table relationships, or similar topics, I recommend my Beginner, Expert, Advanced, and Developer courses. Everything in this course has been built using Microsoft 365 (equivalent to Access 2019), but you will find that the methods should work with versions as far back as Access 2007. If you have questions, you are welcome to post them in the Access Forum. If you would like to discuss customizing the ABCD for your organization, you can reach out through my consulting page or email me directly. I also encourage you to leave comments if you need clarification on anything discussed. You can find a complete video tutorial with step-by-step instructions on everything I covered here on my website at the link below. Live long and prosper, my friends. Topic ListBuilding a helper data form for lookup fieldsAdding and editing values using the helper form Locking system values from editing and deleting Returning selected values to calling forms Implementing composite keys to prevent duplicates Creating a double subform helper data editor Viewing and managing helper types and their values Setting up groups for entities and people Adding groups as subforms on person/entity forms Creating a group list form showing group memberships Navigating between people and group records Establishing many-to-many relationships between people and groups Setting up relations between people/entities Defining relationship types such as parent, child, spouse, coworker Automatically creating opposite relationships in records |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access ABCD 02 part 02 core PermaLink Access Business & Contact Database Core Part 2 |
