Lessons

Resources

Possible Future Upgrades

Based on user demand, I may record a Part 2 to this seminar. Some optional topics may include:

Placing assets into multiple classifications (equipment, furniture, etc.)
Attachments (scanned invoices, warranty info, pictures, etc.) See ABCD Part 5
User-defined fields for each asset
Search form - all major fields
Move groups of assets from one parent to another
Quick check-in screen for newly acquired assets
Unallocated assets form (no owner or location)
Future maintenance reminders / log
Report on asset transactions (changes) between dates
And more, again based on user feedback from Part 1.

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Asset Management Lessons

Age	Subject	From
15 months	Did You Ever Do Part 2	Sarah Bliss
2 years	Bulk Asset Addition	Michael Olsen
4 years	QueryReport w Asset Format	Nathan Schmidt
4 years	Download Link	Eric Dale

Subscribe to Asset Management Lessons
Get notifications when this page is updated

Intro

In this lesson, you will learn how to build a Microsoft Access database for asset management, including tracking basic asset details like location, owner, condition, purchase and sale values, and retirement dates. We will cover creating a parent-child relationship to group assets together, such as assembling computer parts into a complete unit or grouping units into a training room. You will also learn how to set up a transaction history log to monitor changes and movements of assets, and we will create core tables, forms, and queries to manage all asset and entity information. This is lesson 1.

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 are going to learn how to build a database to track assets. We will start off by tracking all the basic information that pretty much every asset management database can track: the asset's location, its owner, its condition, its value, the value when you purchase it, the value when you sell it, the date you retire it, and all that kind of standard information.

What sets this asset database apart is that we are 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, such as hard drives, memory, motherboards, and so on. Each one of those individual units is an asset and 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 is going to be the primary focus of this lesson.

We are also going to build a transaction history log, so you can see what changes were made, on what date, to which asset. For example, it will say hard drive A42 was changed and 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. I do a lot of the basic database design, tables, forms, and queries 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 again, are very important for today.

Also helpful is my SQL Seminar Part One. It is not required, but I will cover a tiny bit of SQL today. I will show you what you need to know. Of course, I will cover some VBA, so any of my Beginner 300 series of VBA lessons for Access will also be helpful. I will also include the Relationship Seminar, because again, today we will talk about one-to-many 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 cannot guarantee it. I do not think I am using any version specific topics today. It is 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 are 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 are 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 of 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. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from 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, and I will 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 will see that it is 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 will 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 are going to cover in today's class.

In lesson one, we will set up our core tables in 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 will set up an entity query, so we can see either a description of a person's name or a company name based on what data we have.

In lesson two, we will build our core forms. We will build our entity form, an entity list form, and we will build our asset form.

In lesson three, we are going to set up our asset list form and the child subform.

In lesson four, we will utilize that history table we created in lesson one, and we will 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 primary focus of this asset management seminar?
A. Tracking employee attendance
B. Grouping assets together using a parent-child relationship
C. Creating financial reports
D. Managing customer data

Q2. Which of the following is NOT mentioned as a type of information tracked for each asset?
A. Asset's location
B. Owner of the asset
C. Marketing strategy
D. Condition of the asset

Q3. What is an example of grouping assets in a parent-child relationship?
A. Assigning one asset to multiple unrelated users
B. Assembling computer parts into a complete computer asset
C. Mixing financial and user data in one table
D. Only listing assets by purchase date

Q4. What is the purpose of the transaction history log to be built in the seminar?
A. To store user login credentials
B. To track changes made to assets over time
C. To generate marketing emails
D. To backup the database

Q5. Which prerequisite knowledge is NOT specifically required but considered helpful for this seminar?
A. Access Beginner classes
B. SQL Seminar Part One
C. VBA Beginner 300 series
D. Access Advanced Level 5

Q6. What feature of Access will be used to display parent-child asset relationships?
A. Reports with charts
B. Forms with subforms
C. Pivot tables
D. Macros only

Q7. How are the course levels structured in AccessLearningZone.com courses?
A. Beginner, Intermediate, Pro, and Master
B. Beginner, Expert, Advanced, and Developer, each with multiple levels
C. Bronze, Silver, Gold, and Platinum
D. User, Supervisor, Admin, and Owner

Q8. Which Access versions are mentioned as being compatible with the seminar content?
A. Only Access 2021 and later
B. Access 2013, 2010, and 2007 (and possibly earlier)
C. Only Access 2016
D. Access for Mac only

Q9. What advice is given for learning most effectively from the course?
A. Memorize the entire video before using Access
B. Immediately apply the techniques to unrelated projects
C. Watch the lesson all the way through, then follow along step-by-step on your computer
D. Only read the course notes, not watch the videos

Q10. Where can students interact, ask questions, and see answers related to the seminar?
A. Only via private email to the instructor
B. In the student forums on AccessLearningZone.com
C. By calling by phone during office hours
D. By using a paper question form mailed to the course office

Q11. What can students download from AccessLearningZone.com to help with their learning?
A. Sample database files for each course
B. Free Access software
C. Printable exams only
D. Windows system updates

Q12. Which core tables will be set up in lesson one of the seminar?
A. Asset, condition, entity, history, and future log tables
B. Invoice, customer, product, payment tables
C. Login, session, error, performance tables
D. Employee, salary, parking, vacation tables

Q13. What is the main use of the history table in the database?
A. Tracking current inventory levels
B. Storing backup copies
C. Tracking changes and history of each asset
D. Managing database security

Q14. Which types of forms will be built in lesson two of the seminar?
A. Invoice and sales forms
B. Entity form, entity list form, and asset form
C. Report export forms
D. Login permission forms

Q15. If a student gets stuck or does not understand something, what is the recommended action?
A. Ignore the problem and skip ahead
B. Watch the video again or ask for help in the forum
C. Start a new project unrelated to the seminar
D. Call Microsoft support

Answers: 1-B; 2-C; 3-B; 4-B; 5-D; 6-B; 7-B; 8-B; 9-C; 10-B; 11-A; 12-A; 13-C; 14-B; 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 covers the first part of the Asset Management Seminar, where we lay the foundation for building a robust asset tracking database. I'm Richard Rost, your instructor.

Throughout this seminar, we focus on gathering all the standard information that an effective asset management database should hold. This includes each asset's current location, ownership, condition, purchase value, sale value, retirement date, and similar general details.

What makes our asset database unique is the ability to group assets in a parent-child structure. Consider a computer store as an example. Each component in a computer - like the hard drive, motherboard, and memory - is a separate asset with its own properties, such as a serial number. When these parts are assembled into a complete computer, that new machine becomes a distinct asset composed of its component 'child' assets. For instance, you might designate it as 'Computer A,' and the database will reflect that it consists of specific parts listed as children. You can even extend this relationship further by grouping several computers together as a larger asset, such as a training room setup.

This capability to organize and nest assets will be a primary focus of our lesson. It opens up a wide range of possibilities for managing and reporting on your assets, beyond simply tracking individual items.

Another major feature we are going to implement is a transaction history log. This log will document every change made to an asset, including the date, the asset involved, and the nature of the change. For example, if a hard drive is moved from one computer to another, the transaction log will record that event, maintaining a detailed audit trail. You can tailor this history log to capture whatever details are relevant to your organization.

Before starting this course, you should have a solid understanding of the concepts covered in my Access Beginner classes, as well as the Access Expert classes up to Level Three. These classes will guide you through fundamental database design, working with tables, queries, and forms, which are skills we will build upon today. Relationships are particularly important, and they are covered in detail in Expert Levels One and Two, while forms with subforms are introduced in Level Three.

While not mandatory, the first part of my SQL Seminar will also prove useful, as we'll briefly touch upon some SQL in this session. I will walk you through any SQL that comes up. Similarly, having some basic knowledge of VBA - such as what is taught in my Beginner 300-level VBA courses - will also be helpful since we will include some simple event programming techniques today. My Relationship Seminar is also relevant, given our focus on one-to-many relationships.

In today's lessons, I am working with Access 2013, but everything we'll cover should work seamlessly with Access 2010 and 2007. For earlier versions such as 2003, I cannot guarantee complete compatibility, but most of the processes should still apply, as I am not relying on version-specific features.

To clarify the structure of my courses, I organize them into four progressive categories: Beginner, Expert, Advanced, and Developer. Beginner classes aim at novices, building up foundational knowledge. As you progress to Expert, then Advanced, and finally Developer levels, you will handle more complex tasks such as advanced macro and event programming and work extensively with Visual Basic for Applications. Each level is further broken into separate courses for manageable learning.

Alongside these general progression courses, I offer topic-based seminars that cover specialized subjects. Some of these focus areas include making web-based databases, constructing calendar-style forms and reports, database security, working with images and attachments, handling work orders, managing accounts payable, learning SQL, generating loan amortization schedules, and more. Full details are available at AccessLearningZone.com.

For any questions on material in today's lessons, I encourage you to visit my student forums. If you're enrolled in the online theater on my website, you'll find a forum panel next to the lesson video. Here, you can read questions and answers from other students, as well as contribute your own. This is a valuable resource for learning and discussion.

If you're watching lessons elsewhere, you can still access the forums any time at AccessLearningZone.com/forums.

To maximize your learning experience, I suggest that you first watch each lesson in its entirety, without worrying about working alongside me at the computer. Once you've absorbed the content, replay the lesson and follow along step by step as you create the sample database. Focus on mastering the exercises from class before trying to apply these techniques to your own projects. If you run into difficulties or need clarification, feel free to re-watch the lesson or post your questions in the forums, and I'll do my best to assist.

The most important thing to remember is to approach the material with an open mind. Access might seem challenging at first, but it becomes much easier with some practice and confidence.

While I highly recommend building the database along with me as shown in class, you also have the option to download the finished database file from my website at AccessLearningZone.com/databases. Sometimes, reviewing and dissecting an existing database can be the fastest way to learn, just as I advanced my own understanding by working with the Northwind Traders sample database years ago. There is a downloadable sample for each of my courses on my site.

Now, let's outline what we will cover in today's class:

First, we'll create the essential tables and queries. These will include a table for assets, condition information, entities for tracking both owner and location, a history table to log transactions, and a future log for maintenance tasks. I'll show you how to create an entity query that displays either a personal or company name, depending on your data.

Next, we'll build the core forms: an entity form, an entity list form, and an asset form.

After that, we'll put together an asset list form with a subform that displays the asset's child relationships.

Lastly, we'll make full use of our history table by building a VBA function that executes SQL code to record changes in any asset fields you choose to track, whether that's changes to a parent asset, an asset's condition, or location updates. All of these changes will be recorded in your history log.

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

Building core tables for asset management
Tracking asset details such as condition and value
Creating an entity table for owner and location tracking
Setting up a history table for asset changes
Developing a maintenance log for assets
Creating entity queries for flexible name display
Building core forms: entity form and entity list form
Designing the asset form for detailed tracking
Setting up the asset list form with a child subform
Implementing a parent-child asset relationship
Constructing a transaction history log
Writing a VBA function to log asset changes
Tracking specific field changes with SQL in VBA