Asset Management

[lf1991]

Good day everyone,

I am new to this forum, hopefully I have put this in the correct catagory.

I am trying to set up a basic asset management DB using MS Asset management DB template - the standard assets work fine. how ever it doesn't include peripherals at the moment.

I am wanting to add a part to where we can assign peripheral assets to a user. and it takes it out of a pool.

for example,
HDMI cables 1 meter - we would have 10 of them. We then assign 1 cable to a user.

Once assigned it would decrease the amount available in the database and increase the amount assigned.

Would this be possible?

I only have basic knowledge of Access - its been quite some years since I have used it.

 

[theDBguy]

Hi. Welcome to AWF!

Have you looked at the Asset Tracking Database Template available from MS? It might have what you need.

 

[lf1991]

Hi. Welcome to AWF!

Have you looked at the Asset Tracking Database Template available from MS? It might have what you need.

Sorry I forgot to mention - I am using the template from MS, it doesn't really have anything like I am needing. (i'll update the main thread message)

 

[theDBguy]

Sorry I forgot to mention - I am using the template from MS, it doesn't really have anything like I am needing. (i'll update the main thread message)

Hi. Thanks for the update. Can you post a stripped down version of your db, just in case someone can take a look?

 

[lf1991]

Please see attached. I was getting "module not found" error earlier but seemed to have stopped (for now).

Regards
Liam

 

[theDBguy]

Please see attached. I was getting "module not found" error earlier but seemed to have stopped (for now).

Regards
Liam

Hi. Thanks. If Assets can have multiple Peripherals and each Peripheral can belong to multiple Assets, then you would have a many-to-many relationship. If that's the case, you need to add a junction table between Assets and Peripherals to store that information (which Peripherals belong to which Asset).

 

[Pat Hartman]

I never use or recommend the MS templates. They are too simplistic for most uses but the real problem is the poor schemas and naming standards.

A peripheral is an Asset and so belongs in the Asset table. but the problem goes deeper. You want to be able to take advantage of common descriptions so the Asset table should actually be two tables. A master table which includes a generic name and perhaps some other more specific information such as manufacturer and maybe a type field that you can use for reporting so you can see printers separately from PCs. Then the child table would contain the serial number and more specific information as well as a foreign key to the contacts table if the equipment is assigned to a person. The details table also includes a foreign key to the major asset the peripheral is attached to. My thoughts are vague at the moment because I don't know what you need the database to do for you and how much information you want to store or if you want to maintain history. So the whole thing can get very complex.

At its simplest put the peripherals in the Asset table and add a type field that this is a peripheral . the other value would be master or whatever you want to call the thing that peripherals are attached to. The type field will be used to control the contents of list boxes you will use when attaching one item to another. So when you add a PK, the subform for the peripherals will have a combo that restricts the assets to only peripherals that are not assigned to a different "master" item.

But think about how much duplication of attribute data this schema will result in and think about if you want to break the asset table as I described earlier.

peripherals would not be assigned to multiple items. The relationship is 1-m. Even a printer doesn't belong to multiple PC's. It belongs to the one it is plugged into or to the network server.