Nested Levels / Containers Equipment Inventory Database

[ClwFLGator]

I like to think that I have a basic understandings of relational databases. I have worked with Access quite a bit; over the last several years I have written a database to run my landscape company including proposals, job database, daily route sheets, time tracking, employee time cards, billing and export invoices to PeachTree, etc.

But now that I am starting to begin the equipment inventory / maintenance portion of the database, I have come to a design question and I am stumped. I have been thinking about this here and there for months and just don't know what to do. And searching for "nested inventory database design" and variations of that and other terms keep bringing me results about nested tables, reports, etc. which is not what I am after.

I am trying to get my head around the best way to design the structure of the inventory part of the database. We have many many pieces of equipment, and I would like to have some levels or containers in the database, such that individual pieces of equipment (and even sub parts, like an engine on a mower, of which we have spares) can belong in or be assigned to a container, or level, something like the following:

• Truck 1
  
  • Toolbox 1
    
    • Screwdriver
    • Hammer
  
  
• Trailer 1
  
  • Mower 1
    
    • Engine 4
    
    
  • Line trimmer 6
  • Edger 1
  • Blower 5
  
  
• Shop (Shop Equipment)
  
  • Toolbox 2
    
    • Air Impact Gun
    • Dial Caliper
    
    
  • Floor Jack
  • Air Compressor
  
  
  
  
• Shop (Motor Pool)
  
  • Line trimmer 8
  • Edger 9
  • Mower engine 6 (spare)

I would need of course to be able to reassign locations periodically as needed. Also, I would need to be able to print or display some kind of report periodically to make sure nothing is missing. I would like this report to be somehow nested, and if possible indented accordingly as above. Some kind of tree view control would be awesome, but I'm afraid programming that is beyond my abilities and so that is not a requirement. But this is where I am stumped on the structure / design of the fields and tables to achieve this result, particularly on a report.

This database is in it's design phase, so I could implement just about anything. But I am looking for best practice, and of course simplest is best. There must be some elegant / simple way to do this, I just can't get my head around what it is.

[Rod]

I can understand why you searched on 'nested' and can also understand why you found mostly reference to nested queries and the like. Your requirement as you describe it is more akin to an Asset Management system or a Maintenance Management system. I don't know whether there are any Access templates for these; I will leave you to look or others to suggest.

I applaud that you have been thinking about this rather than jumping straight in as many other posters on this site would have done. In fact I believe what you need first is to do some entity and data analysis; physical database design comes later. The main decisions to be made are those of scale and/or detail.

Let me paraphrase the content of your post, as best I can, in such terms that suggest a first-cut ERD (Entity Relationship Diagram). You are interested in the disposition and constitution of a range of physical objects.

• Objects may be contained in another object either loosely (tools in a toolbox, spare parts on warehouse shelves) or attached (engine in a lawn mower).
• These relationships are iterative and form a hierarchy; there is no practical limit to the number of levels of this hierarchy.
• Some objects have no constituent parts (a wrench cannot have anything stored in it or be divided into spare parts).
• Some objects can only have loose contents and not attached contents (warehouse, toolbox, maybe even a customer site).

OK, now for the grand first-cut ERD.


Brilliant, you may sacastically remark but actually from now on, as you develop this model, all you are doing is implementing business rules. The main things of note are:

• There are two possible relationships but each object may have only zero or one active relationship.
• The relationships are entity relationships. I anticipate the physical db design will implement the 'stored' relationship as (1:m) and the 'attached' relationship as (1:1).
• Data analysis should identify separate entities, each of which is a type of object. However I would suggest that new entities are reviewed according to their possible relationships (and maybe properties and methods). For example: 'Cannot-be-attached-cannot-contain-anything' entities such as basic tools; 'Cannot-be-attached' entities such as toolboxes and warehouses; etc. I already anticipate finding 'Discrete', 'Location', 'Spare' and so on, but then at the end of the day these may simply become 'types' of the basic object.
• You may already have physical db implementations of some of these entities in your existing db.

There are a whole host of business rules such as, 'Cannot store this vehicle in that vehicle', 'Cannot store more than this quantity here', etc. I suggest that you do not attempt to implement these rules; by the sound of it your business is fairly large but you will be the one using the system and these rules will be second nature to you.

There is also the topic of scale and/or detail - granularity - that only you can decide. This is why it is difficult for a remote third party to help you as there may be different understandings of the detail required. As an example, you mention engines; are you interested in tracking spark plugs? How about consumables?

Bad example above: what I really mean is the assembly - sub assembly construct; do you want to track spares that themselves are used on spares?

So far I haven't been very practical. If you want I can guess and send you a sample db design based on what I have said above.

[ClwFLGator]

Rod,

Yes it sounds as if you clearly grasp nearly exactly what I am trying to do! Thank you for putting it in proper database terms, as well as taking the time to write such a detailed reply!

On the questions of scale and granularity, the number of objects (scale) could be, I dunno, several hundred to a few thousand? We have (at this time) a few trucks, a few trailers, several mowers, and numerous pieces of hand held 2 and 4 stroke equipment (line trimmers, blowers, edgers, chain saws, etc.). In addition to that, we have a lot of hand tools, shop equipment, etc. Numbering every rake individually may seem like overkill, but I don't know of any other way to keep track of everything and hold crews accountable without having a detailed inventory of each truck + trailer by location. In the future, the numbers of equipment will be growing a little bit, but I thought it best to start implementing this system now before things get out of hand.

Getting down to granularity, I think I would stop at individual tools, pieces of "capital equipment," or meaningful sub-assemblies (meaningful for the purposes of equipment maintenance / inventory / assigning to location). For example, spark plugs, filters, etc. would be considered consumables and not inventoried in this system. We have a storage closet to keep those things locked up that only I (or the mechanic / shop manager, if / when we get one) would have access to. I don't think we would be keeping inventory of these, we may one day but that would be another supply inventory system. A better example would be something like a hydrostatic drive pump, a transmission, an engine, or even a gearbox on a set of gas shears. These are things that break and we have spares of, sitting on a shelf. These would be numbered individually, and then assigned to a piece of equipment they were attached to, or given a location of "shop - spare" or somesuch to note their current / default location.

I am trying to think of an instance of when a spare would contain another spare but I cannot. After a certain level of granularity as I have described, there would be a lot of further information on each piece of equipment. So some of this info you are asking about would be contained in those subforms / related tables. For example, the main Equipment Inventory form (based on a table) would contain all the particulars about a certain piece of equipment (purchase date, model year, model no., serial number, etc.) with a tab (related table) showing scheduled maintenance services (ex. for truck: oil change every 3,000 miles, transmission service every 30,000 miles, etc.). There would be a list of 0 (think rake) to many (think mower or truck) of these scheduled services, depending on requirements. There would also be a list of "completed services" (another tab / related table) where we would list the dates, miles / hours, notes, hours spent and parts costs of completed maintenance services as well as unscheduled repairs. This would be a maintenance and repair log for various purposes. These are all one to many relationships of course, with the equipment # being a Foreign Key in another appropriate table. I am also thinking probably another tab on the form showing common part numbers. Any piece of equipment could have 0 (rake) to several (mower) part numbers of common replaceable parts for easy reference (ex. air filter, spark plug, etc.).

On the question of rules, I don't think I would be implementing any. Your assumption is correct that I will be the only one using / maintaining this system, and I agree that they would not be necessary. I understand your concerns about implementing logic rules to insure that a truck would not be placed inside another truck for instance, but to me these things are obvious. Also, anything accidentally "placed" in an incorrect location would be turned up when we do inventory. One of the main reasons for this system will be to print out a detailed inventory of assigned equipment for a particular crew, trailer, or the shop periodically and check and make sure nothing is missing or has been moved to another location.

So hopefully that answers your questions re: scale, granularity, etc. If not, I would be happy to answer any further questions. What I am wondering now is, how to implement that in a data structure? Here "where the rubber meets the road" so to speak, is where I am at a complete loss (even after thinking about this quite a bit). Particularly such that I will be able to print out some type of nested / indented reports showing equipment locations for purposes of taking inventory?

I don't think that I would need a whole example database, just an explanation of the crux of the issue regarding the data relationships we have described, and how to implement them. In my ignorance, I have come up with a couple ideas (or rather, directions to go in) but I get lost when trying to visualize how those will translate into the inventory report I am describing (indented, etc.).

[Rod]

Great, much as I had anticipated. You raised the problem about hammers, rakes, etc. before I asked the question. I think it may be possible to build into the design a feature for unnumbered items where, if a toolbox is supposed to contain a hammer, it does not matter which one. (Maybe you could go to the granularity of differentiating between Stanley and Great Neck.)

One situation that may be unhelpful is that I use Access 2007 and I notice you are back on Access 2000. This might just lead to my assuming things you have not got.

Your volumes, current and future, are well within Access' capability. You indicate that you have experience with Access so I shall focus on suggesting a solution and giving the code and/or design; the cosmetics are up to you.

After replying to your original post I could not get the requirement out of my mind and havebeen playing. Here are some snapshots of what I'm doing.





Give me a couple of days - I have some other ideas I want to try - then I'll get back to you.

[ClwFLGator]

Excellent! I see the direction you are going, and am excited to see what else you might come up with! Also, anyone else reading this thread with something to add, please feel free to chime in! I most certainly appreciate the individual help Rod is giving me here, but I also believe "the more, the merrier" especially when it comes to brainstorming / initial ideas. In fact, I am not sure if I should throw out there some of my initial ideas / directions or not at this time, as I don't know if they would be helpful, or just muddy the discussion.

And actually, I was thinking about numbering individual hand tools, to a point. Rakes, shovels, etc. would have a number. I would carve the number at a uniform location, probably on the metal head part of the tool where the handle attaches with an engraving tool, then go over the engraved writing periodically with a permanent marker. I have done this before and it works well. This would be to prevent one crew losing a rake or garbage can (which happens often enough) and simply taking a rake from another crew or truck. Where I would stop would be at a level of "tool kit" or similar although I would detail the contents of said tool kit within a memo field under that tool kit. I think that would be the most practical scenario.

Well, I will throw these ideas out there and leave it up to those who know more about database design / structure to evaluate whether they are any good or not. One idea I had was an idea of "levels" which is similar in some ways to what Rod is suggesting here (although very different in other ways). I had:

Level	(Sort of) Similar to Rod's	What I had called it (examples)
0	Discreet	(rake, hand tool)
1	Store	Top level (shop, truck, trailer)
2	Equipment	(mower, or could have been toolbox in my system)
3	Component	(engine, transmission, etc.)
4	(not similar)	sub levels, depending on specific location
5	(not similar)	sub levels, depending on specific location

An important distinction is that my "levels" did not have the characteristics (some being able to contain other containers, or items, or not) as the "Asset Types" that Rod is discussing; they were just organizational levels which could contain anything, the depth of the levels varying by location. I thought this would be simple to create these indentation levels in a report for instance, but it didn't work out that way.

Operating under the principle "simple is best," at one point I also had the idea of making a field in (what Rod is now calling) tblAssets called "contained in" where you could simply pick another piece of equipment.

Both of these ideas started to fall down of course as soon as I tried to figure out how to output an inventory list in some sort of indented report. Unless I am missing something, if so please post! Otherwise, I am thinking that this is a structural data problem that may best be solved somehow along the lines of what Rod is describing.

[Rod]

In fact, I am not sure if I should throw out there some of my initial ideas / directions or not at this time, as I don't know if they would be helpful, or just muddy the discussion.

It's always hard to abandom one's 'bright ideas,' even for me Sometimes I spend a considerable time developing a feature that turns out to be inappropriate or not wanted. Anyone want three pages of redundant code?

Where I would stop would be at a level of "tool kit" or similar although I would detail the contents of said tool kit within a memo field under that tool kit.

I'll be suggesting a different, more formalised solution. My solution will provide you with a total count of these items should you want it.

I thought this would be simple to create these indentation levels in a report for instance, but it didn't work out that way.

Numeric levelling -urghh! - only worked in the 80s for Charts of Account - and then not too well. (As an aside: Bill of Materials systems derive the level rather than instating it as a user variable.) One of my axioms is: that if you get the data design right, then all else becomes straightforward. OK, the data analysis methodology recommends that one includes reporting requirements but it's surprising how often this is superfluous.

Operating under the principle "simple is best,"

Apparent simple solutions are often very complex behind the scenes.

how to output an inventory list in some sort of indented report

Piece of cake. I'll show you an interim screen solution after a few more hours. (The recursion and SQL is proving to be a struggle.)

Seriously, my ideas seem to be working. It has proved easier to have only one entity/table (will be three after implementing the requirement for unnumbered tools). The 'types' exist only: to help clarify the user's (and developer's!) thinking: to provide users with some visual mnemonics on output and to provide a basis for some rules to ensure the data structure is kept sensible. I do not propose to use levels but could derive them if required.

At this rate I should have something by Tuesday my time (GMT - 8). It will take the form of: "Is this the sort of thing you want?" Actual code and db design will follow once agreement is reached.

[Rod]

Like/not like?

[ClwFLGator]

It's always hard to abandom one's 'bright ideas,' even for me Sometimes I spend a considerable time developing a feature that turns out to be inappropriate or not wanted. Anyone want three pages of redundant code?

I'll be suggesting a different, more formalised solution. My solution will provide you with a total count of these items should you want it.

Actually, I don't think that would be necessary. I think a simple list of small and/or incidental tools in a memo field would work out better than having to make another entire entry/row into tblAssets for every single tiny thing (and also your additional time coding, additional tables, etc.). Just as long as we could get the contents of that memo field (could be separate "inventory detail" memo field) to show up on the nested report for inventory.

One of my axioms is: that if you get the data design right, then all else becomes straightforward.

I believe you are on the right track with this. I was just "throwing ideas out there." I didn't think they were necessarily good ideas.

Apparent simple solutions are often very complex behind the scenes.

I generally also would not give a fig for the simplicity on the near side of complexity.

Seriously, my ideas seem to be working. It has proved easier to have only one entity/table (will be three after implementing the requirement for unnumbered tools). The 'types' exist only: to help clarify the user's (and developer's!) thinking: to provide users with some visual mnemonics on output and to provide a basis for some rules to ensure the data structure is kept sensible. I do not propose to use levels but could derive them if required.

Again, the unnumbered tools is not a requirement, at least from my end. I could be fine with some sort of memo field or other solution as outlined above. Levels are absolutely not a requirement, and in fact would probably be counter-productive, compared to what you seem to be working on. Again, just one of my early "ideas" I was "throwing out there."

[ClwFLGator]

Neither.

"Love" is more like it!

Like/not like?

[Rod]

I'm attaching an mdb file. Please note, it is not complete as it lacks add/update/delete functionality. However I want you to have a look at it:

• To prove that you can open a db I save as 2000
• To let you 'play' with it - won't take too long as it does very little - and give me feedback

You may need to include a reference to MsComCtl. To do this

1. Go to VBA coding window
2. Click Tools on the menu bar
3. Select References from the drop-down list
4. Scroll down the list of libraries until you see something like 'Microsoft Windows Common Controls ...'
5. Click the checkbox
6. Click OK

If you don't find this library then shout and I'll send you the dll.

The form to open once the db is up and running is frmAssetDisposition. Hope all goes well. Meanwhile I'll tidy up and include the add/update/delete features.

MSAFAssets.mdb

[Rod]

It's gone very quiet. Do you still need help?

[Leander]

Hi Rod,

I am reading this in Jan 2014 and I just want to thank you for a wonderful set of teaching posts. I am another novice who has been struggling for several weeks with a similar Inventory Location problem, buying books and reading multiple online discussions and other resources. Yours is the best by far, and I especially like your teaching re: basic concepts and ways to think about design. Thanks for a great contribution to the world!

Leander

[Rod]

Hi Leander,

Thanks for the accolade. I have never been a programmer or developer but after a lifetime spent in and around business orientated computer systems I think I've picked up some things worth passing on. My interest in Access started when in 1992 my then boss gave me a laptop and a copy of Access v.1 for my evaluation. At that time I was not familiar with the concepts of Object Oriented design and I found everything so confusing. Today I see people making the same mistakes - not appreciating the underlying structure - that I made. I want to get them up to speed faster than what I achieved.

Microsoft are guilty of causing a lot of agony. They market Access as the "layman's answer to computing," a kind of computing equivalent of painting by numbers. Access has too many features that encourage users to jump straight in and 'feel' their way through. I refer to this as the 'secretary' approach - with no intent to be demeaning to secretaries. By all means use these features for prototyping, concept testing and the like but each hour spent in planning your project will be repaid tenfold later in the development. There is no substitute for knowledge of and competence in programming techniques, data analysis and database design, etc. Without a fundamental grasp of these your project will soon become a disaster - and it's then you post on this forum.

Access is frowned upon by 'mainstream' IT professionals, partly I think because it is out of their control. They sneer at VBA, possibly because of the word 'Basic' in the name, and last they heap scorn on Access because of its limited size and tendency to be single-user. I agree, Access will not handle a mirrored, on-line, 24/7, customer accounts system for an international clearing bank. So what? There are millions of small applications where Access is ideally suited. Access has a screen/form/window design utility second to none. If you want to know just how tedious form design can be, then design a new form in Outlook. (That's VB6-like and VB6 was considered good in its day.) IntelliSense means you can write near error free VBA code first time. I've no doubt other programming platforms are now providing utilities that match Access but I doubt if any surpass it. The form design and the ability of VBA to execute 'instantly' provides a platform for true rapid development. Given a data design and some form/screen sketches (a storyboard?) I could have a modest club membership system up and running within two days. ("Up yours!" you C++ developers as you sit there waiting for yet another compilation to finish. ) The JET RDBMS is very robust and DAO provides near native routines meaning very fast database retrieval. ADO provides more of an industry standard set of routines but sacrifices some of the JET-specific features. JET is ODBC compliant meaning that Access can communicate with any other ODBC compliant RDBMS; I have personally worked with an Access development communicating with IBM's DB2 and Oracle.

My concern is that Microsoft are moving away from supporting Access as we all know it. Each version introduces more 'black box' commands. I have a very standard setup and the command to retrieve my contacts from Outlook has never worked, it crashes every time. Yet I cannot debug it. It is obvious that Microsoft wishes to abandon VB6, which is what VBA is based on. When? Who knows. What is the future of Access?

I spent my working career as a Business Systems Analyst and a Consultant. This means I have encountered and analysed many different business systems in a multitude of industries. I believe this gives me an edge in some situations over my more technical orientated colleagues. There are many expert technicians who contribute to this forum who are far more qualified to advise you on technical matters; I am constantly learning from them.

Leander, if you need help or advice don't hesitate to post back.

Regards,

Rod

PS I've just reread my first paragraph which could be interpreted to mean you have to develop Access projects using OOP concepts. No you don't; what I meant is that everything in Access - everything in all the Office applications for that matter - is an object and understanding the basics of OOP will help in using these objects - particularly in addressing them.

[dgaletar]

Hey Rod, much like "Leander" I am reading this post in 2014 also. I also am having a terrible time creating a somewhat (or at least thought to be) simple inventory system.

I have laid the structure out on paper, listed everything in Excel to see what it may look like, and created about 7 inoperable databases on my desktop (I guess to remind me of how stupid I can be!).

Anyway, this is the setup:

• We are a University located in Washington, DC
• We have about 100 vehicles/pieces of equipment in our fleet
  
  • (just for your knowledge) they include everything from buses to lawn mowers
  
  
• We already have a database (that I created) that tracks:
  
  • each vehicle's basic information (year, VIN, license plate, truck number, etc.)
  • each vehicle's drive train information (engine size, trans type, ABS system, etc.)
  • each vehicle's "Service Details" information (type & qty of oil, size of tires, size of windshield wipers, oil, air & fuel filter numbers, etc.)
  • each vehicle's "Service History" (what has been done & when, etc.)
  • additional notes for each vehicle

Now, up to this point, all of the parts that we may have "stocked" for these vehicles was collected in a "pile" in a fairly small room. It basically consisted of a pile of parts that, when we ordered, probably ordered two of while only using one of them. The second apparently went into this "pile".

One Saturday we came in and cleaned out the "pile/room". We were not surprised to see that most of the parts in there belonged to vehicles/equipment that we had long since gotten rid of.

To avoid EVER running into this situation again, we have recently built a moderate sized store room to store these parts. We have also sought the assistance of one of our parts suppliers to help us lay the area out for best use.

This is what we came up with:

• As stated above, each vehicle/piece of equipment has an assigned number to it (known as the CUA#)
• Our storage room has several large racks in it
  
  • Each rack is broken down by:
    
    • Rack Type (Vehicle, Mower, Snow Equipment, and otehrs to be named in a future date (we're still working on those shelves - lol)
    • Shelf Number (self explanatory)
    • Bin Number (counted from left to right)
    
    
  • Each Bin is assigned a Vehicle/Mower/etc. number
  • Each Bin contains a certain assortment of "Stored Parts" for that vehicle/piece of equipment

An example would be:

• "Vehicles" Rack, "#2" Shelf, "#4" Bin belongs to "CUA# 103", and contains:
  
  • Air Filter, Oil Filter, Wiper Blades, Front Brake Pads, Rear Brake Pads

Here is what I am trying to do:

Inventory the parts that I currently have "In Stock" in the bins and put together an order for the parts needed to fill the bins properly.

So, in other words, if we were to do a service on CUA# 103 today, we would probably use the "Air Filter" (PN AF484), "Oil Filter" (PN LF110), and maybe the "Front Brake Pads" (PN 106.11940). Now, since the "Air Filter", "Oil Filter", and "Wiper Blades" come from one Vendor, while the "Front & Rear Brake Pads" come from another, at the end of the week when I did my inventory, my "Order to be Placed" might look like this:

Parts to Order:

Vendor: Parts Authority

Part Number: AF484 Qty.: 1

Part Number: LF110 Qty.: 1

Vendor: Delcoline

Part Number: 106.11940 Qty.: 1

My problem is here:

I AM CONFUSING THE HECK OUT OF MYSELF DESIGNING, RE-DESIGNING, AND OVER RE-DESIGNING THIS STUPID THING!!!

Here's what I have thus far:

A "Brands" table:


A "Categories" table:


A "Parts" table:


A "Suppliers" table:


...and a "Vehicles" table:


How in the world I built a massive database like our vehicles one and cannot figure this one out is beyond me!!! (Why does Access always make me feel like an idiot?!?)

Anyway, if you have any time that you could use to put me on the right track here, I would GREATLY appreciate it!!!

Thank you in advance for your time and consideration in this matter,

DG

[Rod]

DG,

I shall get back to you. Let me reread your post tonight and get to grips with your requirements; I'm sure there is a solution.

Seven inoperable databases? If you want the medal, you have to try harder than that; the record is many more than seven. :-)

Rod