Hello people, first time poster here.

I recently started a job in the IT field and with in my first week, I realized that the asset tracking in my department was horrendous. There is no central database for storing information and we have no idea who has what or when it was given etc. I've taken it upon myself to create an access database (I took database design in college) but I've been having some major issues normalizing my data and setting the right relationships. I'm not looking to be spoon fed, I just need some help along. That said, let me explain my situation and what I've done so far.

1. I plan on tracking the desktops, monitors, laptops, and phones. The desktop and laptop have a sticker with an asset number, but the monitors and phones do not.

2. Not everyone gets a laptop, and some may have 2 laptops (although I'm not certain yet, still looking into it)

3. I plan using username as my primary key, because i want to be able to perhaps link to AD for some other functions.

4. There are some desktops really dont have an owner, they are placed in areas for entire departments to use (this is my biggest issue right now)

So far I've broken my tables to something like this...

Employee -> usr_name(PK), Emp_name, dept.

desktop --> d_assetNum(unique), d_model, d_serial(unique), d_os, n_name, date_given, date_taken

notebook --> n_assetNum(unique), n_model, n_serial(unique), n_os, n_name, date_given, date_taken

phone --> phone_num(unique), phone_model, phone_serial(unique), date_given, date_taken

monitor --> monitor_model, mon_serial(unique), date_given, date_taken

issues i have with this are, 1, I dont know how to link the whole thing and make it relational in access.
Also, how to I make it so that if someone doesnt have a notebook that row isnt empty? Do i have a yes/no attrib for laptop_ownership in employee?? Thank you very much just for reading this, I'll continue to work on it and hopefully one of you awesome pros can help me out.

Hi Simmy

Welcome to UA

Why not use the Assets Template from within Access?

This will give you a starting point


Cheers

Mike

IMO, make all of your PK's an autonumber. If your storing the user name you should still be able to work with AD.
I think you need something like:

tblEmployee
EmployeeID - PK
F_name
L_Name
User_Name
DepartmentID - FK from tblDepartment

tblDepartment
DepartmentID - PK
Department_Name

tblAssets
AssetID - PK
Asset_Name
Asset_Num
Asset_Type - FK from tblAssetType
OS_ID - FK from tblOS
ModelID - FK from tblModel
Serial_Num

tblOS
OS_ID - PK
OS_Name (XP, Vista, Linux, etc)

tblAssetType
AssetTypeID - PK
AssetType (notebook, desktop, workstation, phone, etc)

tblModel
ModelID - PK
Model (Dell latitude, inspiron, etc)

and last but not least, since an employee may have more that one laptop, a join table for assets & employees

tblEmp_Asset
Emp_AssetID - PK
EmployeeID - FK from tblEmployee
AssetID - FK from tblAsset
Date_Assigned
Date_Retrieved

Take a look at the Access templates on microsoft.com, there is one for asset tracking that may be helpful.

HTH
Matt


P.S. Welcome to UA!

Edited by: MattJ on Thu Aug 30 10:54:03 EDT 2007.

1. A computer is a computer. Laptop or desktop is an attribute of computer so you should have only one Computer table.
1a. You should tag everything you will track. This means applying your own asset tag or using the S/N of the item.

2. If people might have more than 1 PC (desktop and laptop, 2 laptops) then you need a junction table to marry staff to item.

3. Use an autonumber as your PK, you can still include username and link to that if you want to connect to AD.

4. In the junction table mentioned 2., fill the EnployeeID with a deptID.

Employee -> usr_name(PK), Emp_name, dept.
See 3. Also names should be broken down at least into first and last

desktop --> d_assetNum(unique), d_model, d_serial(unique), d_os, n_name, date_given, date_taken
notebook --> n_assetNum(unique), n_model, n_serial(unique), n_os, n_name, date_given, date_taken
See 1. You should only have one computer table What is n_name?

phone --> phone_num(unique), phone_model, phone_serial(unique), date_given, date_taken

monitor --> monitor_model, mon_serial(unique), date_given, date_taken

The junction table can also be used to link to phone and monitor. You just need to make sure the PKs or join fields will not be duplicated.


I like Matt's idea of ONE Assets table for all assets. You can then add an assets attributes table to record the different things about an asset. For example; Computers might have RAM & HD sizes. Phones, might have speakerphones, multi-line, etc. Monitors could be different sizes.

Edited by: ScottGem on Thu Aug 30 10:56:27 EDT 2007.

Thank you very much mike, templates are online im assuming? Also am i even in the ball park in making this efficient?

If you mean over the current process yes. But if you mean from a normalization standpoint, you have work to do.

Hi Simmy
I think Scott and Matt have just about cobered all the starting issues you need??

Mike

It turns out that no one has more than 1 laptop or desktop, so that's not an issue. The phones are cell phones so there is only 1 line.


BTW you all have been EXTREMELY helpful. If you were all anywhere near Mass, lunch would be on me.

But, if you do what Matt and I are suggesting, with a single Assets table, you will still need the junction table to assign assets to people or locations.

For the assets table, not every asset has an OS, monitors and phones do not track the OS.

Then create a separate junction table for Assets and OS'

tblAsset_OS
Asset_OS_ID - PK
AssetID - FK
OS_ID - FK

God I hate to ask this question, but how do i do that? Forgive me

Also is there an online course I should take to further/refresh my grasp of the concept? I feel behind.

Or - as Scott suggested, create an attribute table, and simply have the OS as an attribute in that table:


tblAttribute
AttributeID
AttributeType (OS, hardrive space, RAM, processor, etc)

tblAssetAttributes
AssetAttributesID
AssetID
AttributeID
Attribute_Value

How do you do what?
The best online course you can take is to spend time here at UA.

Here you go:

[*]Database planning tutorial Part I
[*]Database planning tutorial Part II
[*]argeedblu's FAQ on Design Strategies
[*]Noah's FAQ on Normalization
[*]Jerry's FAQ on Normalization
[*]GroverParkGeorge's PDF on Normalization
[*]Jerry’s FAQ on Fully Qualified Naming
[*]Jerry's FAQ on Glossary
[*]Danny's FAQ on Reserved Words
[*]Autonumbers - What They Are / Are Not
[*]Primary Keys - Natural vs Surrogate
[*]Naming Conventions - Hungarian Notation
[*]Crystal's ACCESS Basics for Programming
[*]Database Answers (free models)
[*]MSKB 283878: Description of the database normalization basics
[*] The Evils of Lookup Fields in Tables

That's why I suggested the Asset attributes table to track attributes specific to each type.

I'm so lost now, I have to sit down and do this out on paper before i try to input it into access. Thank you all for your help

Hi simmi,
All the suggestions you got from Scott, Matt and Mike are great if you really care about all this. If your goal and need for output is only to know about the asset and its current user and not about what workstation has what OS and or internal components or the history of asset users etc. you can greatly simplify your structure.

tblAssets
AssetID PK
EmpoyeeID FK (lblCurrent User)
DateAquired
PONumber
InvoiceNumber
Cost
CategoryID FK (Workstation,Notebook, Monitor,Phone etc.)
SerialNumber
ModelNumber
TagNumber (Asset Label such as W001"Workstation", N001"Notebook", M001"Monitor",P001"Phone" etc.)
ManufacturerID FK
VendorID FK

tblEmployees
EmployeeID PK
EmpLName
EmpMInitial
EmpLName
other attr.if needed

tblAssetCategories
CategoryID PK
CategoryName

tblManufacturers
ManufacturerID
Manufacturer
other attr. if needed

tblVendors
VendorID
Vendor
other attr. if needed

If you have a need for output with lot more details and or to track some history of particular assets than I would consider all of options what guys suggested above.

In the future I would like to track history of asset, but for now, I simply want to be able to tell who currently has what, when they got it, what dept they are in.

Im starting to think it would be easier to break it into 2 database. One for laptops/desktop because they have asset numbers and everything else because they dont. is this a good idea?

I'm working on this right now, I will post what I have in a few

Here is what I have thus far...

Looks good, except that you are storing the username in the assets table instead of the user ID. Keep everythign in a single database.
If you want to track asset history in the future, you might as well go ahead and set up yor db to handle it. Go ahead and get things right from the start - modifying this db (once populated) to track history would be a pain in the a**.

I agree with Matt. I can understand tracking computer assets separately from phone since the two assets are different types. But you would be better keeping them together as we've outlined. The latest strucutre is fine with the exception of the ID as Matt pointed out. Now you expand this to tract the attributes of each asset.

OK i think i've done what I need to do, how is this matt?

Since only desktop and laptop have assetnumbers, i think i should move assetnumber into the attributes for desktop/laptop instead of the asset table. What do you think matt?

Hmm... You should only have one attributes table. The way your relationships are set up now, you can't store attribute informaion for each asset. You relations indicate that you will be storing attributes for the asset type, not the asset itself.
Since a laptop/pc can only have one model number, serial number, etc... why not include those fields in the asset table?
Even though phones and monitors don't have an OS, I would be tempted to keep the OS in the assets table and just include a "Not Applicable" choice in my list of operating systems.


I would have a table that lists each type of other attribute that you might want to store. I would then have another table that would inlcude the assetID, the ID from the attribute type table, and then the value of that particular attribute.

Scott - your thoughts?

But I mean if lets say for an asset, the assettype is 1, then couldnt i branch to the desktop attrib and list the options? and if 2, i branch to laptop attribs, etc. I may be wrong, but it feels like I have more options of listing each attribute this way.

I'm thinking of moving serialnum, assetnum into the separate attribute tables. It just seems like it's more detailed.


Edit: actually i cant more assetnum, then it would be stored in 2 places. It has to stay in the asset's table.

Edited by: simmy27 on Thu Aug 30 14:22:23 EDT 2007.

I'm moving serial number back to asset folder.

Edited by: simmy27 on Thu Aug 30 14:27:58 EDT 2007.

I wouldn't. Think about it - if you have a single table, you can use a single query and form... With multiple attribute tables, you must create a separate interface for each asset type.
Also, what happens when you have a new type of asset? Would you rather have to create new tables, queries, forms, etc... every time instead of just adding a new record?
Also, separate attribute tables on their own violate the rules of normliazation. You are storing a single type of information - asset attributes. You shouldn't have separate tables storing the same type of data.

See attached for my 3 1/2 ¢
Notice the phone number is not attached to the asset. The phone number does not depend on the asset - it depends on the phone line and which port it's been punched to.
Therefore I tied the phone number table to the employee table.

Edited by: MattJ on Thu Aug 30 15:20:00 EDT 2007.

Matt, I worked off your suggestions, but there was only 2 things wrong. We are only tracking cell phones and specific attributes of the computer such as how much ram, hdd size, etc are not needed either. You've basically done most of the design for me (if not all), now i just need to relate phones into the assets and what not. thank you so much matt.

I'm keeping the specific attributes (hdd, mem, etc) on just in case i need it in the future, but I cant for the life of me figure out how to get the phone in there.

Noticein the relationships window, attribute type is related to asset_attributes. Add "Cell Phone Number" as a record in the attribute type table. Add the cell-phone asset to the asset table. In the asset_attribute table, record the asset ID, the attribute type ID for the cell phone number type, and enter the actual number in the attribute value column.

Ok what goes in the "cell-phone" in the asset table? I can actually have the cell phone number in there can I? I have everything working beautifully right now, except the cell phone area. I will continue working and populating the database and will send in a few. Thank you mark, you might be the greatest person in the world right now.

Nope - you would need to add another field to the asset table to store the phone number. The wya I set it up, the phone number would be handled exactly the same as the RAM, HD, or processor data.
Post it up when your done and I'll show you.
Glad I could help!

p.s.(its Matt, not mark, but that's OK 'cuz I've been called a lot worse )