Access Asset Seminar
Track and Manage Assets in Microsoft Access
Welcome
In this Microsoft Access tutorial you will learn how to build a database to manage your assets. You will learn how to create grouped assets (with unlimited grouping levels), track locations, owners, values, and keep a complete log of changes to the asset's history.
 Resources
Main Seminar Goals
- Develop proper asset tables, queries, and forms
- Create asset groupings (unlimited relationships)
- Owner, location, condition, serial number, more.
- Maintain a change history for any changes to asset
Topics Covered
This database covers all of the basics for tracking assets. We'll begin by creating our asset table and form where you can store all of the information you need about each asset: owner, location, condition, notes, serial number, acquired date and value, current date and value, and retired date and value, and more.
Then we will expand that and create a one-to-many relationship between assets themselves. This way, each asset can be a child of another asset. This allows you to create asset groups. Take, for example, a computer system. It may have a hard drive, processor, keyboard, memory, etc. Each of these are individual assets, and they all belong to the parent asset, which is the computer itself. The parent can have its own serial number, location, owner, etc.
You can double-click on any of the child assets in the subform to open up that specific asset's information. You can also double-click on either the owner or the location combo boxes to open up the details for those entities.
You will learn enough VBA code and SQL to be able to hide or recolor certain fields based on their values (for example, change an asset name to blue if it has a parent record, or hide the location if it doesn't exist). We will also build our own subroutine to log any changes to any fields we wish to track in our history log.
If you want to note when a change was made to, for example, the asset's location, you can track that in the history log.
If you want to be able to track assets in Microsoft Access, this is the perfect seminar for you. You will learn everything mentioned above. You will have access to the full database that we build in class with all of these features. If you have any question whether this seminar is right for you, please feel free to contact me.
Pre-Requisites
It is strongly recommended that you have completed my entire Access Beginner and at least the first three (3) lessons of my Expert series.
Version
I am using Access 2013 in this seminar, however the lessons are perfectly valid for all versions of Access from 2007 and up. It's currently 2022 and I just made sure everything works fine with Access 2019 and Office 365.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access asset management, asset tracking
Intro
In this seminar, you will learn how to use Microsoft Access to build a comprehensive asset management database. We will cover how to track essential asset details such as location, owner, condition, and value, as well as how to implement a parent-child relationship to group related assets, like assembling computers from individual components. You will also see how to set up a transaction history log to monitor changes and transfers for each asset. Throughout the seminar, we will create core tables, queries, and forms, and introduce key database design principles, relationships, and basic VBA techniques.
Transcript
Welcome to Part 1 of the Asset Management Seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In this seminar, we're going to learn how to build a database to track assets. We'll start off by tracking all the basic information that pretty much every asset management database can track, such as the asset's location, its owner, its condition, its value, the value when you purchased it, the value when you sold it, the date you retired it, and all that kind of standard information.
What sets this asset database apart is that we're going to be able to group assets together in a parent-child relationship. For example, if you look at a computer store, they may have different assets that are computer components - hard drives, memory, motherboards, and so on. Each one of those individual units is an asset. It has its own unique properties, including a serial number, that you might want to track in your database.
When you build a full computer, you assemble all those parts together. That new, whole unit can now also be tracked as an asset. Let's call it computer A, and it consists of all those child records - the hard drive, the motherboard, the processor, and so on. You may even go one step further and take a bunch of computers and track them as an asset, maybe call it the training room. There are all kinds of things you can do once you set up this parent-child relationship to group your assets together, and that's going to be the primary focus of this lesson.
We're also going to build a transaction history log, so you can see what changes were made on what date to each asset. For example, it will say hard drive A42 was changed, it was moved from this computer to that computer on this date. That will be tracked in the transaction history log, and you can track whatever information you want.
Prerequisites: The bare minimum you should know before taking this class is the material covered in my Access Beginner classes and my Expert classes up to level three, at a minimum. I cover a lot of the basic database design, tables, forms, queries, and all that in the beginner classes. Expert One and Two focus on relationships, which are very important concepts covered today. In Access Expert Level Three, I cover forms with subforms, which are again, very important for today.
Also helpful is my SQL Seminar Part One. It's not required, but I will cover a tiny bit of SQL today. I'll show you what you need to know. And of course, I will cover some VBA, so any of my beginner 300 series of VBA lessons for Access will also be helpful. I'll also reference the Relationship Seminar, because today we will talk about relationships in detail.
I will be using Access 2013 today. Everything I cover should work just fine with 2010 and 2007. It probably still works with 2003 and earlier, but I can't guarantee it. I don't think I'm using any version-specific topics today. It's pretty generic.
My courses are broken up into Beginner, Expert, Advanced, and Developer level classes. Beginner level classes are for novices; you should understand all the topics covered in them by the time you get to the Expert level classes, which you're in now. When you finish all the Expert level classes, the Advanced classes will cover event programming and macros, and the Developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels - Level 1, 2, 3, and so on.
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 and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of 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're watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all the questions that other students have asked, as well as my responses to them, and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.
If you are not watching these lessons on my website, you can still visit the student forums later by visiting AccessLearningZone.com/forums.
To get the most out of this course, I recommend 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.
Don't try to apply what you're learning right now to other projects until you've mastered the sample database from class. If you get stuck or don't understand something, watch the video again from the beginning, or tell me what's wrong in the student forum, and I'll do my best to help you.
Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you'll see that it's really easy to use.
I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at AccessLearningZone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You'll find there is a sample database for each of my courses on my website.
Now let's take a few minutes and go over exactly what we're going to cover in today's class.
In lesson one, we'll set up our core tables and a query. A table will track our assets, the condition of each asset, the entity, which will be used to track the owner and the location of each asset, a history table, which will be used for tracking the history of each asset, and a future log for what you have to do for the asset, like a maintenance log. We'll set up an entity query, so we can see either a description, a person's name, or a company name based on what data we have.
In lesson two, we'll build our core forms. We'll build our entity form, an entity list form, and we'll build our asset form.
In lesson three, we're going to set up our asset list form and the child subform.
In lesson four, we'll utilize that history table we created in lesson one, and we'll create a VBA function to run some SQL to track changes to any field in the database that we want to. If you want to track when the parent was changed, or the condition of the asset was changed, or when the location was changed, you can track all of that in the history log.
Quiz
Q1. What is the main purpose of the database that will be built in this seminar?
A. To track asset locations only
B. To manage event programming
C. To track assets and their related details in a database
D. To secure data through encryption
Q2. What distinguishes this asset management database from standard ones?
A. Ability to store images for each asset
B. Ability to handle complex parent-child relationships between assets
C. Automatic integration with Access web apps
D. Automatic calculation of depreciation
Q3. Which of the following relationships can be established in the asset management system described?
A. One-to-one only with single assets
B. Parent-child relationship grouping assets together
C. Only one level of grouping is allowed
D. Assets can only be linked by serial number
Q4. What is the function of the transaction history log in this database?
A. Keeping personal notes on asset owners
B. Tracking all changes made to each asset, such as location and condition changes
C. Recording passwords for assets
D. Storing images of each asset
Q5. What does Richard recommend as the minimum prerequisite knowledge before taking this seminar?
A. Only Developer-level classes
B. Only Advanced classes
C. Beginner and Expert Access classes up to level three
D. No prerequisites are necessary
Q6. Why is knowledge of relationships in Access especially important for this class?
A. Because only forms are used in the class
B. Because parent-child asset grouping relies on relationships
C. Because macros are heavily used
D. Because only value tracking is needed
Q7. Which Access versions is this seminar primarily designed for?
A. Access 2013, but also works with 2010 and 2007
B. Access XP only
C. Only the latest Office 365 version
D. Access 2003 exclusively
Q8. What does Richard recommend students do to get the most out of the course?
A. Read the entire textbook first
B. Watch each lesson once fully, then follow along step-by-step
C. Experiment with their own projects immediately
D. Skip to the last lesson for the final database
Q9. How can students interact and get help if they have questions about the seminar?
A. Email only
B. Use the student forums on AccessLearningZone.com
C. Call Richard directly on the phone
D. There is no support provided
Q10. What sample databases does Richard recommend using for learning by "tearing apart"?
A. Only Northwind Traders database
B. Any available online template
C. Sample databases provided for each course on AccessLearningZone.com
D. Sample MS Excel files
Q11. In lesson one of the seminar, what main components will be set up?
A. Only a query for entities
B. Core tables and a query, including asset, condition, entity, history, and maintenance log tables
C. Just forms and reports
D. Only macros are created
Q12. What main Access feature is NOT emphasized as essential in the introduction?
A. Tables and queries
B. Forms with subforms
C. Relationship design
D. Web server configuration
Q13. What is the benefit of using the parent-child relationship between assets in this database?
A. It enables grouping of related assets such as computer parts and assembled computers
B. It restricts assets to only one location
C. It does not affect reporting
D. It only applies to tracking accounts payable
Q14. Which of these is NOT mentioned as an additional seminar offered by Richard?
A. Web-based databases
B. Loan amortization schedules
C. Writing work orders
D. Accounting for payroll taxes
Q15. What is the suggested approach if a student gets stuck or does not understand something in a lesson?
A. Ignore the lesson and move on
B. Watch the video again or ask in the student forum
C. Immediately jump to the developer-level classes
D. Wait for a future lesson to clarify
Answers: 1-C; 2-B; 3-B; 4-B; 5-C; 6-B; 7-A; 8-B; 9-B; 10-C; 11-B; 12-D; 13-A; 14-D; 15-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 focuses on building an Asset Management database with Microsoft Access. I'll guide you through the process of constructing a system to help you keep track of your assets, starting with the foundational details that any standard asset database needs to manage: asset location, owner, condition, purchase and sale values, retirement date, and other key information.
What makes the approach here unique is that we're not just tracking assets individually, but also enabling you to group assets together using a parent-child structure. For example, think about a computer store's inventory of individual components—hard drives, memory sticks, motherboards, and so on. Each of these pieces is its own asset, with properties like serial numbers. Once you build a complete computer from these parts, that entire unit becomes its own asset, which we might call Computer A. Computer A, in turn, is associated with several child records: the hard drive, motherboard, processor, and so forth. You might even decide to group several computers as one asset, such as the equipment in a training room. This parent-child relationship allows for flexible grouping and comprehensive tracking of your assets, and it's a central theme of this lesson.
We'll also develop a transaction history log for your assets. This log will keep track of all actions, such as when a particular hard drive is transferred from one computer to another. You will be able to record what was changed, who made the change, and when it occurred, giving you full traceability.
Before starting with this seminar, you should be comfortable with the material from my Access Beginner courses and have progressed through the Expert classes up to level three. These will give you the necessary foundation in database design, relationships, and working with forms and subforms—skills that are all vital for today's class. It will also be helpful if you are familiar with the basics of SQL and VBA, although I'll introduce the essential parts you'll need along the way. My Relationship Seminar is another resource you may find valuable since we're delving into those topics.
For this seminar, I'm demonstrating everything using Access 2013. However, the lessons are compatible with Access 2010 and 2007 and may work with older versions, as I am not relying on version-specific features.
Let me also explain the structure of my course offerings. My curriculum is divided into Beginner, Expert, Advanced, and Developer levels. You're currently in the Expert section, so you should be comfortable with everything from the Beginner modules. Once you move on, the Advanced courses will introduce you to event programming and macros, while the Developer track will focus on VBA. Each of these categories includes multiple levels that build on one another.
Alongside my core classes, I host specialized seminars that cover topics such as designing web-based databases, creating calendar-style forms and reports, securing your data, managing images and attachments, writing work orders, organizing accounts payable, working with SQL, building loan amortization tools, and more. All details about these offerings are available on my website.
As you progress through these lessons, I encourage you to engage with other students in my forums. When watching the lessons on my site, you can see questions, answers, and comments from other students alongside each video. Reading through these discussions and participating will enhance your learning experience. If you are not watching on the website, you can always access the forums at any time by visiting my site.
To truly benefit from this seminar, I recommend first watching each lesson all the way through without trying to replicate anything. Then, replay the lesson from the beginning and follow along step by step in Access, creating the database as demonstrated. Do not try to use these techniques on your own projects until you've completed the sample database, as this helps prevent confusion and ensures mastery of the topics. If something is unclear or you run into trouble, review the video again or reach out to me in the forums for assistance.
Keeping an open mind is essential. Access can seem complex at first, but with some patience and practice, you'll discover how user-friendly it really is.
I strongly suggest you build the same database with me as you watch, but if you would like a completed copy to reference or reverse-engineer, you can download it from my website. Taking apart an existing database is often an effective way to learn, much like how I learned by exploring Northwind Traders many years ago. Every course I offer comes with a sample database for further study.
Let's go over the plan for today's course:
First, in lesson one, we'll design our essential tables and set up a query. These tables will track your assets, their conditions, owners, and locations (using what I call the entity table), maintain a history of asset changes, and provide a to-do or maintenance log. We'll also create an entity query to display a proper description, which could be a person or company name, depending on the data entered.
In lesson two, we'll begin constructing the main forms—an entity form, a listing form for entities, and an asset form.
Lesson three will involve building out the asset listing form and its associated subform for managing child assets.
In lesson four, we'll use the history table we built earlier and develop a VBA function that records changes to any desired field in the database. This function will allow you to keep a detailed record anytime an asset's parent, condition, or location changes, storing all of this information in the history log for easy reference.
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
Setting up core tables for asset tracking
Creating a table for asset condition
Building an entity table for owners and locations
Setting up a history table for asset changes
Building a future log for asset maintenance
Designing an entity query for flexible name display
Building an entity form for data entry
Creating an entity list form
Constructing an asset form
Setting up an asset list form
Creating a child subform for asset grouping
Implementing a parent-child relationship for assets
Utilizing a history table for tracking asset changes
Creating a VBA function for change tracking
Running SQL from VBA to log field changes in history
|
