If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Newbie Looking for Help
Hello everyone. I work as and intern for a company the has over 1500
workstations and numerous amounts of peripheral. I took it upon myself as a side project to create and MS Access database that will keep track of there hardware and software inventory. Needless to say with, my half a step above basic knowledge of MS Access and no VB or SQL experience I know I am way over my head. What started out as a side project has grown into a monster. However, I like the challenge and the learning experience. I do understand that a database is as only as good as its foundation. Meaning how the tables and relationships are setup. In setting up my tables, I tried to go through the entire life cycles of all the different types of equipment and structure that into my tables and relationships. My question is what information do I need to post to get feedback on the way I structured my tables and relationship. Thank You George L |
#2
|
|||
|
|||
Newbie Looking for Help
In setting up my tables, I tried to go through the entire life cycles
of all the different types of equipment and structure that into my tables and relationships. My question is what information do I need to post to get feedback on the way I structured my tables and relationship. Post all the table layouts, including a description of any fields which are not obvious by reading the name, list the indices (if any) and tell which one(s) are a primary key. That should get you good answers here. Tom Lake |
#3
|
|||
|
|||
Newbie Looking for Help
On Sat, 24 Dec 2005 19:00:25 -0500, Little Penny
wrote: My question is what information do I need to post to get feedback on the way I structured my tables and relationship. I'd suggest a simple readable format such as Tablename Fieldname Primary Key field1 Text meaning if not obvious from fieldname field2 Date/Time field3 Number Tablename Fieldname Primary Key Fieldname Primary Key ... Table1 1:n Table2 ON Table1.Fieldname Table1 1:n Table3 ON table1.Fieldname LookupTable 1:n Table2 ON LookupTable.Fieldname .... John W. Vinson[MVP] |
#4
|
|||
|
|||
Newbie Looking for Help
On Sat, 24 Dec 2005 19:00:25 -0500, Little Penny
wrote: Hello everyone. I work as and intern for a company the has over 1500 workstations and numerous amounts of peripheral. I took it upon myself as a side project to create and MS Access database that will keep track of there hardware and software inventory. Needless to say with, my half a step above basic knowledge of MS Access and no VB or SQL experience I know I am way over my head. What started out as a side project has grown into a monster. However, I like the challenge and the learning experience. I do understand that a database is as only as good as its foundation. Meaning how the tables and relationships are setup. In setting up my tables, I tried to go through the entire life cycles of all the different types of equipment and structure that into my tables and relationships. My question is what information do I need to post to get feedback on the way I structured my tables and relationship. Thank You George L OK here we go..................... Tables Documentation Table: tbl_Computers (PK) Primary Key Field Name Description Type Length ComputerID (PK) Auto Number 4 Computer_Manufacturer ComputerManufacturer Text 50 Computer_Model Computer model number Text 50 Computer_ProductID Manufacturer Product ID Text 50 Computer_SerialNumber Computer Serial number Text 50 Computer_OperatingSystem Operating System Text 50 Computer_RAM Amount of system RAM Long Integer 4 Computer_CPU_Type Type of Computers processor Text 50 Computer_CPU_Speed Speed of Computers processor Text 50 Computer_HDD_Manufacturer Harddrive Manufacturer Text 50 Computer_HDD_Size Harddrive Space Long Integer 4 Computer_HDD_SerialNumber Harddrive Serial Number Text 50 Computer_HDD_IDE Does computer support IDE? Yes/No 1 Computer_HDD_SCSI Does computer support SCSI? Yes/No 1 Computer_HDD_Serial_ATA computer support Serial ATA? Yes/No 1 Computer_DVDRW computer have a DVD +/- RW? Yes/No 1 Computer_DVD Does Computer have DVD? Yes/No 1 Computer_CDRW Does Computer have CD-RW Yes/No 1 Computer_CDRom Does computer have CD-Rom Yes/No 1 Computer_Floppy computer have floppy Drive? Yes/No 1 Computer_Wired_MAC Network Card Address Text 50 Computer_Wireless_MAC Computers Wireless Mac Address Text 50 Computer_Purchase_Date Date of Purchase Date/Time 8 Computer_Domain_Name DNS Name of computer Text 50 Computer_Firewire computer support Firerewire Yes/No 1 Computer_USB Does computer support USB Yes/No 1 Computer_Notes Notes about this computer Memo Computer_Barcode Barcode inventory Text 50 Computer_WarrantyEnd_Date Date Warranty Expires Date/Time 8 UserID Long Integer 4 LocationID Long Integer 4 MonitorID Long Integer 4 Table: tbl_Laptop Description: (PK) Primary Key Field Name Description Type Length LaptopID(PK) Auto Number 4 Laptop_Manufacturer Laptop Manufacturer Text 50 Laptop_Model Computer model number Text 50 Laptop_ProductID Manufacturer Product ID Text 50 Laptop_SerialNumber Laptop Serial number Text 50 Laptop_OperatingSystem Laptop Operating System Text 50 Laptop_RAM Amount of system RAM Long Integer 4 Laptop_CPU_Type Type of processor Text 50 Laptop_CPU_Speed Speed of Laptop processor Text 50 Laptop_HDD_Manufacturer Harddrive Manufacturer Text 50 Laptop_HDD_Size Computer HDD Space LongInteger 4 Laptop_HDD_SerialNumber Harddrive Serial Number Text 50 Laptop_HDD_IDE Laptopr support Serial IDE? Yes/No Laptop_HDD_SCSI Laptop support Serial SCSI? Yes/No Laptop_HDD_Serial_ATA Laptop support Serial ATA? Yes/No 1 Laptop_DVD Laptophave DVD Rom? Yes/No 1 Laptop_DVDRW Laptop have a DVD +/- RW? Yes/No 1 Laptop_CDRom Does the Laptop have a CDRom Yes/No 1 Laptop_CDRW Does Laptop have CD-RW Yes/No Laptop_Floppy Laptop have floppy Drive? Yes/No 1 Laptop_Wired_MAC Laptops Network Card Address Text 50 Laptop_Wireless_MAC Laptop Wireless Mac Address Text 50 Laptop_Purchase_Date Laptop Date of Purchase Date/Time 8 Laptop_NetworkName DNS Name of computer Text 50 Laptop_Firewire Does Laptop support Firewire Yes/No 1 Laptop_USB Does Laptop support USB Yes/No Laptop_Notes Notes about this computer Memo Laptop_Barcode Barcode number Text 50 Laptop_WarrantyEnd_Date Date Warranty Ends Date/Time Laptop_Signature Has User Singed For Laotop Yes/No 1 Laptop_Date_U_Received Date User received Laptop Date/Time 8 Laptop_Date_U_Returned Date User returned Laptop Date/Time 8 UserID LongInteger 4 Table: tbl_Location Description: Field Name Description Type Length LocationID (PK) Auto Number 4 Location_Building Building Text 50 Location_RoomNumber Room Number Text 50 Location_Department Department Text 50 UserID Long Integer 4 ComputerID Long Integer 4 MonitorID Long Integer 4 PrinterID Long Integer 4 PeripheralID Long Integer 4 Table: tbl_Monitor Description: Field Name Description Type Length (PK) Monitor_ID Auto Number 4 Monitor_Manufacturer Monitor Manufacturer Text 50 Monitor_Model Monitor Model Number Text 50 Monitor_ProductID Manufacturer ProductID Text 50 Monitor_Size Monitor Size Text 50 Monitor_SerialNumber Monitor Serial Number Text 50 Monitor_Purchase_Date Date/Time 8 Monitor_WarrantyExpires Date/Time 8 Flat_Screen Yes/No 1 CRT Yes/No 1 Monitor_Notes Memo ComputerID Long Integer 4 LocationID Long Integer 4 Table: tbl_Peripheral Description: Field Name Description Type Length PeripheralID (PK) Auto Number 4 Peripheral_Type Type of Peripheral Text 50 Peripheral_Manufacturer Brand of Peripherals Text 50 Peripheral_Model Model of Peripheral Text 50 Peripheral_ProductID Text 50 Peripheral_SerialNumber Serial Number of Peripheral Text 50 Peripheral_Purchase_Date Date of Purchase Date/Time 8 Peripheral_Notes Notes on this item Memo Peripheral_MAC_Address Mac Address - Where applicable. Text 50 Peripheral_DNS DNS Name, Where applicable. Text 50 Peripheral_IP IP Address, Where Applicable. Text 50 Peripheral_Barcode Barcode Number Long Integer 4 Peripheral_Signature for (Camera, Camcorder etc) Yes/No 1 Peripheral_Date_U_Received Date User Received Date/Time 8 Peripheral_Date_U_Returned Date User Returned Date/Time 8 Peripheral_WarrantyExpires Date/Time 8 UserID Long Integer 4 ComputerID Long Integer 4 LocationID Long Integer 4 Table: tbl_Printers Description: Field Name Description Type Length PrinterID (PK) Auto Number 4 Printer_Manufacturer Printer Manufacturer Text 50 Printer_Model Printer model number Text 50 Printer_ProductID Text 50 Printer_SerialNumber Printer Serial number Text 50 Printer_DNS_Name Network Name Text 50 Printer_Network_Printer Yes/No 1 Printer_Slave Yes/No 1 Printer_IPaddress Text 50 Printer_Wired_MAC Text 50 Printer_Purchase_Date Date of Purchas Date/Time8 Printer_WarrantExpires Date/Time 8 LocationID Long Integer 4 Table: tbl_Software Description: Field Name Description Type Length SoftwareID (PK) Auto Number 4 Software_Company Text 50 Software_Name Text 50 Software_Version Text 50 Software_ProductID Text 50 ComputerID Long Integer 4 Table: tbl_UserInfo Description: Field Name Description Type Length UserID (PK) Auto Number 4 User_FName Text 50 User_LName Text 50 User_PhoneExt Text 50 User_CellPhone Text 50 User_Pager Text 50 User_Email Text 50 LocationID Long Integer 4 ComputerID Long Integer 4 LaptopID Long Integer 4 PeripheralID Long Integer 4 |
#5
|
|||
|
|||
Newbie Looking for Help
First, I want to apologize for starting a new thread in this subject. It was kindly brought to my attention by someone else. Obviously it show my lack
of experience in communication in this type of forum. I will post another massage in my other thread directing any comment here. Again I my apologies. To just add some info to my earlier post. This is my layout for a database to keep track of computer hardware and software. Just to give a little info. There are of 1500 workstations, numerous, printers, peripheral and so on. I want to track them from the time they come in the door until the time there discarded. Part of the naming convention in the domain has to do with the building 98% of the time. Oh, by the way there are about 12 different buildings and many different rooms. One thought of I have in mind was to create a relationship from the location table to computer, printer, and monitors tables. Not sure if that is the best way. In quite a few cases, there are more the one users of a computer. Also looking ahead if a person installs a new computer in room 201. Since the computer info is entered once a computer arrives at the warehouse how do I connect the dots with a form. I'm trying to visualize how this happens and what info I need and how do I get it. In my mind I, lookup the computer info by serial number and attach a location and a monitor to it. However, do I have to sort through 4 or 5 hundred locations id number to link them because the are linked by primary key ID which is a number. Maybe someone can elaborate for me. I know I am in over my head but I am learning. Ok here my table layout that Tom Lake suggested I post for feedback. Tables Documentation Table: tbl_Computers (PK) Primary Key Field Name Description Type Length ComputerID (PK) Auto Number 4 Computer_Manufacturer ComputerManufacturer Text 50 Computer_Model Computer model number Text 50 Computer_ProductID Manufacturer Product ID Text 50 Computer_SerialNumber Computer Serial number Text 50 Computer_OperatingSystem Operating System Text 50 Computer_RAM Amount of system RAM Long Integer 4 Computer_CPU_Type Type of Computers processor Text 50 Computer_CPU_Speed Speed of Computers processor Text 50 Computer_HDD_Manufacturer Harddrive Manufacturer Text 50 Computer_HDD_Size Harddrive Space Long Integer 4 Computer_HDD_SerialNumber Harddrive Serial Number Text 50 Computer_HDD_IDE Does computer support IDE? Yes/No 1 Computer_HDD_SCSI Does computer support SCSI? Yes/No 1 Computer_HDD_Serial_ATA computer support Serial ATA? Yes/No 1 Computer_DVDRW computer have a DVD +/- RW? Yes/No 1 Computer_DVD Does Computer have DVD? Yes/No 1 Computer_CDRW Does Computer have CD-RW Yes/No 1 Computer_CDRom Does computer have CD-Rom Yes/No 1 Computer_Floppy computer have floppy Drive? Yes/No 1 Computer_Wired_MAC Network Card Address Text 50 Computer_Wireless_MAC Computers Wireless Mac Address Text 50 Computer_Purchase_Date Date of Purchase Date/Time 8 Computer_Domain_Name DNS Name of computer Text 50 Computer_Firewire Does computer support Firewire Yes/No 1 Computer_USB Does computer support USB Yes/No 1 Computer_Notes Notes about this computer Memo Computer_Barcode Barcode inventory Text 50 Computer_WarrantyEnd_Date Date Warranty Expires Date/Time 8 UserID Long Integer 4 LocationID Long Integer 4 MonitorID Long Integer 4 Table: tbl_Laptop Description: (PK) Primary Key Field Name Description Type Length LaptopID(PK) Auto Number 4 Laptop_Manufacturer Laptop Manufacturer Text 50 Laptop_Model Computer model number Text 50 Laptop_ProductID Manufacturer Product ID Text 50 Laptop_SerialNumber Laptop Serial number Text 50 Laptop_OperatingSystem Laptop Operating System Text 50 Laptop_RAM Amount of system RAM Long Integer 4 Laptop_CPU_Type Type of processor Text 50 Laptop_CPU_Speed Speed of Laptop processor Text 50 Laptop_HDD_Manufacturer Harddrive Manufacturer Text 50 Laptop_HDD_Size Amount of Computer HDD Space Long Integer 4 Laptop_HDD_SerialNumber Harddrive Serial Number Text 50 Laptop_HDD_IDE Laptopr support Serial IDE? Yes/No 1 Laptop_HDD_SCSI Laptop support Serial SCSI? Yes/No 1 Laptop_HDD_Serial_ATA Laptop support Serial ATA? Yes/No 1 Laptop_DVD Laptophave DVD Rom? Yes/No 1 Laptop_DVDRW Laptop have a DVD +/- RW? Yes/No 1 Laptop_CDRom Does the Laptop have a CDRom Yes/No 1 Laptop_CDRW Does Laptop have CD-RW Yes/No 1 Laptop_Floppy Does Laptop have floppy Drive? Yes/No 1 Laptop_Wired_MAC Laptops Network Card Address Text 50 Laptop_Wireless_MAC Laptop Wireless Mac Address Text 50 Laptop_Purchase_Date Laptop Date of Purchase Date/Time 8 Laptop_NetworkName DNS Name of computer Text 50 Laptop_Firewire Does Laptop support Firewire Yes/No 1 Laptop_USB Does Laptop support USB Yes/No 1 Laptop_Notes Notes about this computer Memo Laptop_Barcode Barcode number Text 50 Laptop_WarrantyEnd_Date Date Warranty Ends Date/Time 8 Laptop_Signature Has User Singed For Laotop Yes/No 1 Laptop_Date_U_Received Date User received Laptop Date/Time 8 Laptop_Date_U_Returned Date User returned Laptop Date/Time 8 UserID Long Integer 4 Table: tbl_Location Description: Field Name Description Type Length LocationID (PK) Auto Number 4 Location_Building Building Text 50 Location_RoomNumber Room Number Text 50 Location_Department Department Text 50 UserID Long Integer 4 ComputerID Long Integer 4 MonitorID Long Integer 4 PrinterID Long Integer 4 PeripheralID Long Integer 4 Table: tbl_Monitor Description: Field Name Description Type Length (PK) Monitor_ID Auto Number 4 Monitor_Manufacturer Monitor Manufacturer Text 50 Monitor_Model Monitor Model Number Text 50 Monitor_ProductID Manufacturer ProductID Text 50 Monitor_Size Monitor Size Text 50 Monitor_SerialNumber Monitor Serial Number Text 50 Monitor_Purchase_Date Date/Time 8 Monitor_WarrantyExpires Date/Time 8 Flat_Screen Yes/No 1 CRT Yes/No 1 Monitor_Notes Memo ComputerID Long Integer 4 LocationID Long Integer 4 Table: tbl_Peripheral Description: Field Name Description Type Length PeripheralID (PK) Auto Number 4 Peripheral_Type Type of Peripheral Text 50 Peripheral_Manufacturer Brand of Peripherals Text 50 Peripheral_Model Model of Peripheral Text 50 Peripheral_ProductID Text 50 Peripheral_SerialNumber Serial Number of Peripheral Text 50 Peripheral_Purchase_Date Date of Purchase Date/Time 8 Peripheral_Notes Notes on this item Memo Peripheral_MAC_Address Mac Address - Where applicable. Text 50 Peripheral_DNS DNS Name, Where applicable. Text 50 Peripheral_IP IP Address, Where Applicable. Text 50 Peripheral_Barcode Barcode Number Long Integer 4 Peripheral_Signature for (Camera, Camcorder etc) Yes/No 1 Peripheral_Date_U_Received Date User Received Date/Time 8 Peripheral_Date_U_Returned Date User Returned Date/Time 8 Peripheral_WarrantyExpires Date/Time 8 UserID Long Integer 4 ComputerID Long Integer 4 LocationID Long Integer 4 Table: tbl_Printers Description: Field Name Description Type Length PrinterID (PK) Auto Number 4 Printer_Manufacturer Printer Manufacturer Text 50 Printer_Model Printer model number Text 50 Printer_ProductID Text 50 Printer_SerialNumber Printer Serial number Text 50 Printer_DNS_Name Network Name Text 50 Printer_Network_Printer Yes/No 1 Printer_Slave Yes/No 1 Printer_IPaddress Text 50 Printer_Wired_MAC Text 50 Printer_Purchase_Date Printer Date of Purchase Date/Time 8 Printer_WarrantExpires Date/Time 8 LocationID Long Integer 4 Table: tbl_Software Description: Field Name Description Type Length SoftwareID (PK) Auto Number 4 Software_Company Text 50 Software_Name Text 50 Software_Version Text 50 Software_ProductID Text 50 ComputerID Long Integer 4 Table: tbl_UserInfo Description: Field Name Description Type Length UserID (PK) Auto Number 4 User_FName Text 50 User_LName Text 50 User_PhoneExt Text 50 User_CellPhone Text 50 User_Pager Text 50 User_Email Text 50 LocationID Long Integer 4 ComputerID Long Integer 4 LaptopID Long Integer 4 PeripheralID Long Integer 4 |
#6
|
|||
|
|||
Newbie Looking for Help
On the whole, you'll have a better chance at getting an answer from some of
the busiest people in the newsgroups (and some of the best answers come from those busy people), if you will summarize the topic of your question in the Subject, rather than describing your skill level or appealing for indeterminate help. For other good suggestions on effective use of newsgroups, take a look at the FAQ at http://www.mvps.org/access/netiquette.htm. And, that FAQ site is just chock-full of good information about Access. Before I'd want to offer you definitive advice on your table design, I'd also want to know what kind of information you want to retrieve, and how you want to use it. A database application is just a "model" of the "real world", and like any "model", only needs to model the real world in sufficient detail to accomplish our purpose for modeling it. If all you want/need is to create reports listing the different equipment and its attributes, just having tables including that information is enough. If you want to create some queries to search for particular equipment and its location, you have to take that into consideration, and if you want more sophisticated functions (with greater productivity gains), that too, will have to be considered. Larry Linson Microsoft Access MVP |
#7
|
|||
|
|||
Newbie Looking for Help
Suggestions - hopefully NOT for confusion:
For User Table. - right now if a user has more than 1 of any of the devices then you have a problem and no way to handle software and multiple versions of software User table Field Name Description Type Length UserID (PK) Auto Number 4 User_FName Text 50 User_LName Text 50 User_PhoneExt Text 50 User_CellPhone Text 50 User_Pager Text 50 User_Email Text 50 LocationID Long Integer 4 User Device table: DeviceTableKey PK UserID FK When assigned When returned DeviceKey FK the physical key of the device Then try to have all of the common infor for all devices in one table with a field saying what it is and maybe other linked tables for any unique information. As new devices and or types of devices are added then this part remains always the same. Maybe a device/software name Then again it can be looked at a different way if any device/software on ONLY being used by one person at a time. If you add who and when to each of the separate tables you can always search all of the tables to get what an inidividual has. It is a simple method but has its drawbacks also. Then if you query each of the tables you can construce a complete list of what an individual has or had, etc. As Larry mentions, it is important to try to figure out what you are going to do with this so as to devise a system that will work easily. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie Needs Help - Finding last number in a table | Chas Large | Running & Setting Up Queries | 3 | December 22nd, 2005 12:22 PM |
Newbie to Access doing Report | Bob Howard | Setting Up & Running Reports | 7 | June 7th, 2005 01:50 PM |
Newbie in Report Designing | Mr. Capuchino | Setting Up & Running Reports | 1 | May 9th, 2005 12:19 PM |
Nasty Normalization for Newbie | Steve Hills | New Users | 4 | July 14th, 2004 03:21 PM |
Newbie Question on Extra Space After Numeric Values | Yellowbird | Worksheet Functions | 3 | March 8th, 2004 05:40 PM |