Newbie Looking for Help

  • Thread starter Thread starter Little Penny
  • Start date Start date
L

Little Penny

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
 
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.
Click to expand...

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
 
My question is what information do I need to
post to get feedback on the way I structured my tables and
relationship.
Click to expand...

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]
 
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
Click to expand...
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
 
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
 
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
 
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.
 
Back
Top