BC2015

Thanks everyone for your help. I think I've figured out how to make portals work, and that means that I have my tables talking to each other correctly, which is great! Now comes the hard part of actually setting up layouts that do what I want them to do. Using Portals, I've now got a P.O. layout that tells me what assets are associated with any particular P.O. record (along with those assets' information like location and license code). I've also got a Locations layout that now tells me what assets are located in any particular Location record. This is a GREAT start! I've also determined that I don't need a "Software" table because, as was mentioned previously, it would merely be a glorified Value List. I'll just create a Value List for Software and it should work fine that way (I'll create a dropdown list to select the software title/version, and be sure to maintain it separately) Anyway, thanks again for all your help!

Ok, I apologize, but I'm lost again. How do I make layouts that are actually useful for looking up information? These should be simple things to do, but I don't understand how to use the layouts to do this. I get how to physically manipulate the design of the layouts and field boxes etc., but I don't get which table should be the base of which layout for which type of information, and where the fields should be pulled from for each field displayed. For instance, how would I create a layout that would output: A list of computers (and the rooms they're in) For each computer, a list of each of the software titles/versions that are installed For each piece of software, the license code and P.O. for that license code Or a layout that would output: A list of software titles/versions For each piece of software: A list of the computers it's installed on The license code for each installation The P.O. for each license code Or how can I create a layout for each room that will tell me what hardware assets are located in that room? Your help is most appreciated. Thanks.

Makes sense. I think I'm starting to get the hang of it. Sort of. Now I've got to make layouts that show the information that I want to show. Bah!

Great points. This does look like it would work better. This new relationships graph would allow me to assign a new computer to a room without affecting any of the other assets in that room, whereas the previous relationships would not have allowed that? I'm not too concerned with having the change log records related to the rooms. It's more important that they be related to the assets, which happen to be located in whatever room they're located in. Here's another mockup...

Wow, that second one looks a lot simpler, and might be more what I need, actually. I'll try that out. What would be the advantages of one way over another though? Attached is a mockup of this.

Oh! Now I get it! So, something like this?

So... Like this?

How's this?

That makes sense. Thanks so much for your help. I'm going to work on this today.

Oh, ok. I was misunderstanding. I thought you were saying I needed to flatten the entire database into one table for some reason. Disregard. What you're saying now makes a lot more sense! Yes, the ChangeLog entry could definitely affect more than one asset. Which table would ChangeLog records go into? ChangeLogItems or ChangeLog? I'll play around right now and update with a screenshot of what I end up with.

Thanks for the reply! The self-join looks like a good idea. So, one massive table with a ton of fields for the Assets table, and one separate table for the ChangeLog, related to the Assets table by the AssetID that the record pertains to?

First-time setting up a complex (or what I think is complex) database, and really could use some help. The idea is to be able to create an inventory of Computers, Peripherals, Software, and Software Licenses, (and where each is installed/located), along with the Purchase Order information for each item, and the Vendor for each P.O. Here's how I sort of see it breaking down, but I'm confused as to how to setup the tables and relationships: One Computer can have many Purchase Orders associated with it. One Computer can have many Vendors/Manufacturers/Sales Reps associated with it. One Computer can have many pieces of Software installed on it One Computer may have many Peripherals One Computer can have many Change Log entries associated with it. One piece of Software (software title) can be installed on many Computers One piece of Software can have many Licenses associated with it One piece of Software can have many Purchase Orders associated with it. One piece of Software can have many Vendors/Manufacturers/Sales Reps associated with it. One piece of Software can have many Change Log entries associated with it. One License can be installed on only one Computer at a time One License can have only one piece of Software associated with it. One License will have only one Purchase Order associated with it. One License can only have one Vendor/Manufacturer/Sales Rep associated with it. One License can have many Change Log entries associated with it. One Peripheral can only be installed on one Computer at a time One Peripheral can only have one Purchase Order associated with it. One Peripheral can only have one Vendor/Manufacturer/Sales Rep associated with it. One Peripheral can have many Change Log entries associated with it. One Purchase Order can have many Licenses, Peripherals, and/or Computers associated with it. One Purchase Order can have only one Vendor/Manufacturer/Sales Rep associated with it. One Purchase Order can have many Change Log entries associated with it. One Vendor/Manufacturer/Sales Rep can have many Purchase Orders associated with them. One Vendor/Manufacturer/Sales Rep can have many Computers associated with them. One Vendor/Manufacturer/Sales Rep can have many Peripherals associated with them. One Vendor/Manufacturer/Sales Rep can have many pieces of Software associated with them. One Vendor/Manufacturer/Sales Rep can have many Licenses associated with them. One Vendor/Manufacturer/Sales Rep can have many Change Log entries associated with them. Change log is: A manually-created list of notes for any changes, upgrades, problems, maintenance events, etc. performed for the entire department. Needs to be filterable/sortable by Date, Computer, Peripheral, Software, License, P.O., Vendor, and/or type of event. Event types would be from a manually-created value list. Would be great if I could have a layout specifically for entering these events. Also, not sure how I would go about entering events if one event affects multiple computers or peripherals at the same time. I might have to just make duplicate entries (one for each piece of equipment) to link an event to each piece of equipment it affects? I've begun setting up tables for: - Computers - Software - Licenses - Peripherals - Installations (join table joining everything?) - Vendors/Manufacturers/Sales Reps - Purchase Orders - Change Log (not sure what fields I need in this) I'm also trying to figure out the Primary Keys and Foreign Keys, how the relationships should work, and how I can create a layout for let's say Computers that will show me: - Computer info (computer name, location, hardware specs from the Computers table) - P.O. number for Computer (from the P.O. table) - List of installed licenses (including software name/version, license number, vendor, and P.O. associated with that license) (portal to the Licenses table, filtered by ComputerID?) - LIst of installed peripherals (including the type of peripheral, manufacturer, name/serial number, vendor/P.O., date of install) (portal to the Peripherals table, filtered by ComputerID?) - Change Log list for that computer, sortable by date and/or type of change. (portal to the Change Log table, filtered by ComputerID?) And a layout for Software that will show me: - Software name/Version - Type of Software (standalone software, software plugin, etc.) - List of Licenses associated with this Software (including software name/version, license number, vendor, and P.O. associated with that license) (portal to the Licenses table, filtered by SoftwareID?) And a layout for Vendors/Manufacturers/Sales Reps that will show me: - Vendor Contact info (sales rep name, phone, email, website, etc.) - List of P.O.s associated with this Vendor, sortable by date, and expandable to see the individual items within that P.O. (such as license numbers and where those licenses are installed, or peripherals and where those peripherals are installed) If anyone has any advice, I sure would love the help. Thanks.