Create a database that contains the inventory of your organization's technology, as well a table including employee information. Build your database named IT_inventory, linking users to workstations, i.e., each computer (desktop or notebook) will be assigned to a particular employee. You will need to create an Employee table with appropriate fields within the IT_inventory, Access database.
The Employeetable will be joined, through the employee ID field, to the Desktop and Laptop tables.
Your IT_inventory database will include the following tables (including one query and one report, generated after all data and tables are complete), fields and field properties:
Tables (5)
• Desktop
• Employee
• Laptop
• Network (includes switches, router, and printers )
• Server
Query and report (2)
• Desktop/laptop (query)
• Desktop/laptop (report)
a) TABLES:
Employee Table
Create a table called Employees that will be linked to the Desktop workstation and Laptop tables by Employee ID. Again, only workstations and laptops will be linked to an employee (not servers, printers, etc.).Make sure that Employee ID field name uses Short Text as a Data Type. Once fields are created, you use "External Data" to append and add records from Employee Sheet in the RFP_Project.xlsx file to populate your table and cut-and-pastelocation from the Employees and Room numbersSheet in the same RFP_Project.xlsx file.
Create these fields:
• Employee ID
• Lastname
• First name
• Location
◊ LaptopTable
|
Note: ALL fields will use "Short Text" as data types.
|
|
FIELD NAME
|
FIELD INSTRUCTIONS
|
|
Device ID
|
- Use Lookup Table using existing table, "Employees"
- Follow these steps to create your lookup:
o Use Lookup Table and select:
"I want the field to get the values from another table or query."
o Select Table: Employee
o Select Field: Last Name
o Press Next
o Select Last Name Ascending
o Press Next
o Uncheck "Hide key column (recommended)"
o Select Last Name Column and click and drag it to the left of the Employee ID column
o Press Finish
o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME
- Show Device ID plus the last name of the employee assigned to the computer, i.e. "LT-Smith"
o Format the "Field Properties" so that the prefix for the type of device ("LT-" for laptop) appears at the very left of the entry is followed by the employee name by adding a lookup table from the Employees table, e.g. LT-Smith
Hint: In the Field Properties for Device ID use Format: !"LT-"
|
|
Description
|
- Select: "I will type in the values that I want"
o Web Server
o Developer's Workstation
|
|
Vendor
|
- Select: "I will type in the values that I want"
o Dell
o HP
o Apple
|
|
Operating System
|
- Select: "I will type in the values that I want"
o Windows 7
o OSX
o Linux
|
|
Employee ID
|
- Use Lookup Table using existing table, "Employees"
- Follow these steps to create your lookup:
o Use Lookup Table and select:
"I want the field to get the values from another table or query."
o Select Table: Employee
o Select Field: Last Name
o Press Next
o Select Last Name Ascending
o Press Next
o Uncheck "Hide key column (recommended)"
o Press Finish
o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME
|
|
Close and press "Yes" to save the table
|
◊ DesktopTable
|
Note: ALL fields will use "Short Text" as data types.
|
|
FIELD NAME
|
FIELD INSTRUCTIONS
|
|
Device ID
|
- Use Lookup Table using existing table, "Employees"
- Follow these steps to create your lookup:
o Use Lookup Table and select:
"I want the field to get the values from another table or query."
o Select Table: Employee
o Select Field: Last Name
o Press Next
o Select Last Name Ascending
o Press Next
o Uncheck "Hide key column (recommended)"
o Select Last Name Column and click and drag it to the left of the Employee ID column
o Press Finish
o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME
- Show Device ID plus the last name of the employee assigned to the computer, i.e. "DT-Smith"
o Format the "Field Properties" so that the prefix for the type of device ("DT-" for laptop) appears at the very left of the entry is followed by the employee name by adding a lookup table from the Employees table, e.g. DT-Smith
Hint: In the Field Properties for Device ID use Format: !"DT-"
|
|
Description
|
- Select: "I will type in the values that I want"
o Web Server
o Developer's Workstation
|
|
Vendor
|
- Select: "I will type in the values that I want"
o Dell
o HP
o Apple
|
|
Operating System
|
- Select: "I will type in the values that I want"
o Windows 7
o OSX
o Linux
|
|
Employee ID
|
- Use Lookup Table using existing table, "Employees"
- Follow these steps to create your lookup:
o Use Lookup Table and select:
"I want the field to get the values from another table or query."
o Select Table: Employee
o Select Field: Last Name
o Press Next
o Select Last Name Ascending
o Press Next
o Uncheck "Hide key column (recommended)"
o Press Finish
o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME
|
|
Close and press "Yes" to save the table
|
SETTING THE TABLE JOINS
• Select DATABASE TOOLS then Relationships
• Make sure Employee, Desktop, and Laptop Table are visible
• Join Table:EmployeesField:Employee ID to Table:Desktop Computers Field:Employee ID
• Check "Enforce Referential Integrity"
• Click "Join Type" and select the second option (Left Outside Join)
• Press Create
• Join Table:EmployeesField:Employee ID to Table:Laptop Computers Field:Employee ID
• Check "Enforce Referential Integrity"
• Click "Join Type" and select the second option (Left Outside Join)
• Press Create
◊ NetworkTable
Create these fields:
• Device ID
• Description
• Vendor
• IP number
o Field property: format @@\.@@\.@@\.@@ (may need to adjust for number)
o IP number data type: text
• Location
◊ ServerTable
Create these fields:
• Device ID, simply enter "S1" or "S2" - you do not need to format or create a lookup table with the server device IDs)
• Description
• Vendor
• IP number
o Field property: format "S_"@@\.@@\.@@\.@@ (may need to adjust for number)
o IP number data type: text
• Operating system (Windows 2008 Server)
• location
The number of devices with which you will populate your databases is below:
• No more than seventeen ( 17) desktop workstations (the number will depend on staffing you choose)
• Three+ (3+) (depending on staffing you choose) Laptops
• One (1) File server
• One (1) Applications server
• One (1) Web server
• One (1) Router
• Two (2) switches
• Four (4) printers (networked)
Use the following device-name prefixesand IP numbers in the desktop workstation, Laptop, Server and Network tables:
Device prefixes are as follows:
• Desktop: begin with DT (followed by the employee's last name)
• Laptops: begin with "LT" (followed by the employee's last name)
• Servers: begin with "S" (S1 or S2)
• Switches: use SW1 through SW10
• Printers:use P1 through P4
• Routers:use R1 through R2
IP numbers:
IP numbers for desktopsand Laptops are assigned through DHCP, so there is no need to enter them into the database. Servers, printers and routers have IP numbers within the following range. You can use any IP number in this range as a dedicated IP number:
25.13.55.16 - 25.13.55.255
b) QUERY
Design a query that links Employee, Desktop and Laptop tables, and returns a table listing data from the following fields. What you should have is a query that returns all a table containing all the employees in the database, what equipment they use, their location and name.
Query Name: Desktop/laptop
Query Items:
• Desktop workstation device or Laptop device
• Employee ID
• Last Name
• First Name
• Location
You will need to link the Desktop, Laptops and Employees tables for this query.
c) REPORTS
From the Desktop/laptop query you generated, create a report which lists employee ID, first and last name, location, and Device ID (desktop or laptop). The report should be arranged alphabetically by employee last name.
d) FORMS
From the Employee, Desktop, and Laptop tables, create three (3) forms in Columnar format reflecting all fields from in the Desktop, Laptop, and Employee tables. Enter your name in the Employee form, and then enter information for both a Desktop and Laptop computer.