Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NOTICE: I am right in the path of Hurricane Irma, please read this   dismiss
 
 

Asset Management Seminar

 Part 1: Learn how to track and manage assets in Microsoft Access
 

access asset management seminar

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. Topics include:

- Develop proper asset management tables, queries, and forms
- Create asset groupings (unlimited parent / child relationships)
- Track asset owner, location, condition, values, serial number, more.
- Maintain a change history for any changes to asset

Click here to watch the first full lesson of the Seminar in our Online Theater:

 

 
Access Asset Management Seminar, Part 1
Description: Learn how to build an asset management database in Microsoft Access
Versions: I use Access 2013, however most of the material covered should work fine with most versions of Access.
Pre-Requisites: This course stands alone, however it is very strongly recommended that you have taken my complete Beginner series, and up to at least Access Expert 3 before beginning this class.
Running Time: 2 Hours, 9 Minutes
Cost: $69 - Order multiple courses to receive a discount up to 50% off

 

Here's What You'll Learn

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.

asset form

 

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.

asset database

 

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.

asset owners

 

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.

asset history

 

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.

track asset changes

 

That's what we cover in Part 1 of the seminar. I wanted to build this database to include all of the features that most people will use. I will be adding a lot more in Part 2 of the seminar, based on user feedback. Additional topics to be covered in Part 2 may include:

- Placing assets into multiple classifications (equipment, furniture, etc.)
- Attachments (scanned invoices, warranty info, pictures, etc.)
- User-defined fields for each asset
- Generate and scan barcodes (may be a separate seminar)
- 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.

 

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.

 

Access Asset Management Seminar
 Outline for Part 1

00. Intro (7:55)

01. Setup Core Tables (27:10)
AssetT
EntityT
HistoryT
ConditionT
Query for Entities Based on Available Info
EntityQ

02. Setup Core Forms (26:10)
EntityF
EntityListF
Double Click to Open EntityF
Search Box in List Form
Enter Assets in Table
Assign ParentID
Create AssetF
Make Combo Boxes

03. Asset List & Child Subform (35:09)
Create Asset List Form
Create Child Subform
Click to Open Selected Asset
Recolor Fields Based on Values
Hide Owner, Location for Children

04. History Log (16:58)
Track Changes to Any Asset
Note Field Values in Log Table
Create LogIt Subroutine
VBA / SQL INSERT INTO

05. Topics for Part 2 (10:39)
List of Topics to Cover Next

06. Review (5:09)

 


 

 
 

Student Interaction: Asset Management Seminar Part 1

Richard on 2/5/2015:  In the Microsoft Access Asset Management Seminar, Part 1 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. - Develop proper asset management tables, queries, and forms - Create asset groupings (unlimited parent / child relationships) - Track asset owner, location, condition, values, serial number, more. - Maintain a change history for any changes to asset This seminar is 2 hours and 9 minutes long. Click here for more information on the Microsoft Access Asset Management Seminar, Part 1 including a complete course outline, sample videos, and lots more.
Jim Upton on 2/5/2015: I have Assets which is software which cost a lot more than hardware. Would you just add it the Sub/Child parts?
I would like to see also to track where my Office and windows product keys have gone. But I need them match on the order/code they came in on
Thank the seminar is great

Jim Upton

Reply from Alex Hedley:

I've not finished the course yet but I would think cost is irrelevant you would treat it as a Entity like everything else.

You could create a relationship between order and keys, just have it as another sub form

Steven Schuyler on 2/6/2015: Just my opinion, I like hiding the combo boxes for owner and location!
Chris on 2/6/2015: Owner and Location could be shown but disabled in the child record to save the user having to go to the parent to find out the location.
Chris on 2/6/2015: The simplest "who" could be Environ("UserName")

Reply from Alex Hedley:

That is a useful function.

Steven Schuyler on 2/6/2015: For History, can you demonstrate how to set it up if your tracking the history for multiple tables, such as changes to customers, work orders, invoices, purchase orders, etc...?

Reply from Alex Hedley:

You could log changes against anything, if it's like other seminars just use the custom LogIt Function on any of your Forms. I'll have to watch these to see how it's done in this one.

Ravi C on 2/7/2015: Hi Richard,

I think there is one more part for Reports. Am I right? When that is going to come?

Thanks,

Ravi C

Terry Hopper on 2/8/2015: Sometimes an asset may have more than one line item, either serialized or not. ie: 4 sticks of RAM in a PC; multiple video cards; etc. So; we might consider if the items are NOT serialized the show the line item once with a quantity of 4. If the item IS serialized, then show either 4 different line items with the serial number; OR have a link to open a form that will show the 4 serial numbered items related to that pc.
T-Bone on 2/8/2015: The bar code idea is a good one to keep. If your using a sheet of labels like say, Avery 8160, it would be nice to know which position you might need to print 1 or more labels at. I hope that makes sense. I really would prefer to see a complete seminar on the bar code generation and printing myself.
As for the 'Move a group'; this would be nice. We build kits and some of our kits have serialized items and sometimes we have to "Un-Build" the kit and move the items either back to stock or to another type of kit. This seminar is working very well with the 'Inventory' seminar I have been requesting for several years now. I am able to do my inventory with 'BOM's' now with this Asset seminar.
All I'm missing now is the quantity used in the BOM based on it's unit of measure as to how much the kit(s) cost is.

Hilton Turner on 2/10/2015: I would like to see a portion of this series devoted to creating a report with up to ten (10) groupings (parent/children relationships) that can be exported to Excel.
T-Bone on 5/4/2015: When can we expect to see Asset Management Seminar Part 2?

Reply from Alex Hedley:

I think the next Access courses are being developed so maybe after those.

Cheryl Hokanson on 5/12/2015: I have been waiting for the barecode seminar. Is it about ready to come out?

Reply from Alex Hedley:

It's still in development.
Was there anything in particular you wanted to see in it?

J Wilkins on 6/16/2015: Will your seminar cover the different depreciation methods. This would be helpful and I believe essential for proper asset management. It shows on the balance sheet as a contra asset account called Accumulated Depreciation.

Reply from Alex Hedley:

The full outline is shown here.

Depreciation methods aren't shown in Part 1 but suggestions are being noted for following parts.

Gene on 6/16/2015: Alex, I wouldn't hold your breath. I was also waiting. This is long overdue (as was Part 1) with no communication about the status. I wound up finishing it myself (by many internet searches) and I've now got a very good and feature rich asset management database. And I know quite a bit more about Access now. Sorry you have been waiting so long as well. I'd be happy to share any info I can if possible.

Reply from Alex Hedley:

Thanks Gene, any ideas or problems you came across and fixed would help others when making theirs.

Duncan Mfolwe on 2/18/2016: Suggestion for Topic to be Included in Asset Management Part 2: Future Maintenance Log
James Gray on 3/10/2016: Rich,
Very anxious for the second installment. I want to use this method to keep track of both supplies and equipment on several ambulances and stock room, tie it to mandatory maintenance and check lists for all the medical equiipment we have, and automate the purchase order process in my inventory module. Can you give me any idea of timeing? Thanks.

Duncan Mfolwe on 5/20/2016: Excellent seminar. I can't wait for Part 2.
Scott Sorgi on 7/16/2016: Has Asset Management Part 2 been released?

Reply from Alex Hedley:

Not yet

Alhaji L on 7/29/2016: Is the Depreciation of Assets going to be considered in these seminars. I think it should as a proper Asset management should include the depreciation of that asset as well. Please revert on this.
David Williams on 9/6/2016: When do you expect to have Part 2 finished? I have an asset database I have been setting up where I am trying to track which asset is signed out to which employee. Each employee can and does have multiple assets assigned to them. As new employees are hired it would be nice to be able to add the new employee on the fly without to much trouble for the end user.


Reply from Alex Hedley:

I know he's working on the a couple of Expert lessons first to cover Macros etc.

Brian M on 9/21/2016: In part 2, I would like to see a query where you enter a bunch of parentIDs (such as a field of multiple barcode scans comma delimited) and it spits out a report of all the assets that are included in them. I can't quite figure out how to parse it into an OR statement that works.

Reply from Alex Hedley:

You could do this in VBA.
You have the Field name, use Split
Split ( expression [,delimiter] [,limit] [,compare] )
Then you could build a dynamic IN ('x', 'y') statement looping your above array
Add this to your SQL statement and set that as the Record Source of your Form, that's covered in the SQL Seminars.

Blog Post

MSG on 12/7/2016: Any progress with Asset Part2? I also have an asset DB that I want to use to sign out expensive equipment on a weekly basis to complete jobs and then sign back in after mission completion so I know what is and what is not available for my tech folks? Thanks, your classes are great and really could use this.
James Gray on 3/16/2017: Any ideas of timing for the second seminar? Seems like Rich is pretty busy lately and new courses are coming out pretty slowly....

Reply from Alex Hedley:

I think Rich wants to finish the Macros for Advanced and release a few Beginner 2016 courses for Excel/Word etc.
He does enjoy Access Seminars the most so hopefully will throw one in between.
It's a bit out of date but worth checking the Updated Production Schedule

Jon Turner on 3/19/2017: I'm in agreement with all the elements you say you're looking at introducing but as with some other comments, I'd like to track signing out equipment to individuals in a 'temporary loan' subform/table. Also, I'd like to be able to tackle the servicing of assets and keeping servicing records.

Thanks! Hope Part 2 is coming soon!

Reply from Alex Hedley:

Great ideas.
Would you have a loan from and to date and set the to when they return?

Jon T on 3/25/2017: Hi Alex, yes, exactly. It would be great to have a log where an asset is loaned out to an entity and you have a loan record where the from and to date are completed with the to field completed when they return the asset.

Reply from Alex Hedley:

I'm sure Rich can add that in.

Sam W on 5/17/2017: Did you ever complete Asset Management Part 2? I have ordered Part 1 and would like to see what you have developed in Part 2. I am interested in the Bar Codes (especially how to enter bar codes already used), I am also interested in the tracking changes (how to display name or serial number, not just the item numbers 9 to 4). I would also like to see how to list items in a particular location in a report style (I have 100's or even 1,000's of pieces I track and would like to list all of these items in 1 report for inventory purposes, plus add bar codes to this list).

Reply from Alex Hedley:

No, it's still on the Waiting List.

Deon R on 6/1/2017: Hi Richard,

- Scan barcodes
- generate barcodes
- Depreciation - Accumulated depreciation
- Financial report on Asset depreciation

Just to mention a few.

We could really use part 2 of this Seminar.

Any ideas on when this will be done - especially for your faithful Insiders' ...:)

Kindest regards,

Deon

Reply from Alex Hedley:

I've nudged him

Shane J on 9/7/2017: Can't wait to see Part2
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP