One to Many Assets Database

[joeyrego]

Hi Guys,

I hope everyone is doing great. I am pretty new to Access 2013 and database design. I am trying to consolidate all of our excel sheets that we use to keep track of assets. Cell phones, Cell phone plans, Network equipment, computer equipment, cameras, etc. There are a bunch of them. I have started to use the Desktop Assets Template. I have been able to customize it for the most part for what I need but I am struggling on one thing that I hope someone can help me with.

We have servers as an asset. There are servers that have multiple IP addresses. I wasn't sure what is the best way to do this. in my excel file I just created a new line even though it was the same server name. something like below.

Server1 IP Address
Server1 IP Address
Server1 IP Address

Its not the best way to keep track of things but this works for me in excel when we need to sort by IP Address. Moving to Access I know this is probably not the best way to do things? I was thinking that I would just create a new field called IP address 1, IP Address 2, IP Address 3 etc. If I do it that way then it really screws me up when I look at the data in datasheet view. its a little difficult to sort by IP address. Does anyone have any suggestions? I have attached my relationship view below not so show the relationships but so that you can see the tables that I have and what fields I am tracking. Any help would be greatly appreciated.

[June7]

Options appear to be:

1. IP addresses table that is a child of Servers table

2. multi-value field in Servers table (I NEVER use multi-value fields)

3. multiple columns for IP Addresses - least desirable approach

What are tblVMServers and tblServers for? What is relationship?

How does Assets relate to phone and server tables?

[joeyrego]

Hi June7.

Thank you for replying so quickly. tblvmservers and tblservers are tables I was playing with. So they aren't set in stone.

tblVMServers is a table that has a list of all my servers and their specific details that was imported from a master excel sheet.
tblServers is a list of all the server names only.
tblPhoneUsers is a list of all users specifically only for the tblPhone Assets so that I could create a one to many so that one person could have many phones
tblPhoneAssets are the details of each phone and plan that is associated with it
Contacts is the default contacts table that comes with the template
Assets is the default contacts table that comes with the template that I have added some fields to
tblSecurity Level is a table that has different security levels in it
tblUser is a table that has my users in it that are allowed to log into the application. I was able to find something online that helped me provide a login form . this is different from the contacts table only because the users who update the database may or may not be in the Contacts table.. Not sure if this it the best way to do it, buts its working for me. any advice would be appreciated.

I was trying to do a one to many relationship between the tblVMServers and tblServers based on server name as one server could have many ip addresses but I was hitting a wall due to different data types in creating the relationships between the two. Then when I changed the data type to match in my master excel file and imported the data there were type conversion errors. I was just not understanding it all.

So really I was playing with the idea of making an assets table for phones, one for regular assets and then one for vmservers. Is this the right approach? What would you recommend? Should I not separate them? Should I put phones, vmservers and all assets into the assets table? If so, like I mentioned I was having some issues with importing data and make sure the correct data types are set so that I don't loose information on import.

I thank you very much for helping me with this. As I mentioned I am pretty new to access and am eager to learn the right way to do things and not necessarily a way that just gets the job done. all from field naming conventions to table naming conventions to labels, combo boxes etc........

Oh. and right now there is no relationship between the assets table and the phones table. they are just all in the same database and I have different forms that refer to each so that the inventory is done in one place. So I created a menu form that directs the user to click on the forms that refers to either vmservers, phones or Assets.


Thanks
Joey

[June7]

Phones and servers would still be listed in Assets table and then tblPhones and tblVMServers would be related detail tables with specialized info for those assets? Those tables need a foreign key field for AssetID?

[joeyrego]

lol. Yeah. Im not really sure if I even need those other tables? That's what I was hoping to get some help on.

[June7]

If you want the details then I would say the tables are needed. Having those fields in the Assets table would not only be contrary to normalization but there is a limit of 255 fields in table anyway.

[joeyrego]

ok. thank you for your help. I think I might have to rethink this a little.