do database and th excel

Budget: $3,500,000. You can go under this amount, but not exceed it. This is a three year project, so you must plan accordingly. Working with the RFP_Spreadsheet.xlsx, you will find worksheets with an employee list and room numbers from which to build your Employee Salaries worksheet. In addition to these two worksheets, your Spreadsheet will include the following 5 worksheets that you will create, with worksheet tabs colored and named accordingly:

1. Employee Salaries

2. Technology/supplies

3. Office Rental

4. Office parameters – this worksheet will include information listed below and from which you will build your Office Rental worksheet using multi-sheet references.

5. Summary (this sheet you will create last, but place first in your workbook)

Select from the Employee names worksheet your 20 employees and paste them in the Name column. Add from the room number worksheet, room numbers for the employees. Any list of names and numbers will do. This data forms the foundation for your Employee_Salaries worksheet.

Employees fit into the following categories.

1. At least six (6) fulltime, salaried employees;

2. Five (5), halftime (.5 ) employees;

3. Two (2) hourly, fulltime employees, paid $12.00/hour.

4. One (1) receptionist only

5. The remaining (6) distribution of staff is up to you – any combination of salaried, halftime and hourly.

6. Each employee gets a 3% increase in salary for year 2 and 3

Based on the types of positions you select, assign each employee a Position Title, Status (salaried, halftime, or hourly), and base salary . Remember, you have some flexibility in determining the number and types of position, but you must fit salaries within the three-year budget. Remember, your budget must cover technology and space rental too.

You will include in the status column whether they are salaried (1) or halftime (.5). If they are hourly, you will need to calculate what their wage would be for the year.

Add three additional columns for the salaries for year 1, 2 and 3. Remember, salary in year 1 is the product of status and salary; year two is 3% greater than year 1; and year 3 is 3% greater than year 2.

1. Each of the 20 employees has at least one computer and or laptop

2. Desktop computers and laptops are purchased in year 1.

3. Servers are rented annually.

4. Miscellaneous/Salary & Expenses costs (printer paper and toner, long distance calls, etc.) between $10,000 and $20,000 per year.

5. Developers (programmers, web developers, database managers) require higher-end workstations;

6. project managers, receptionists, personal officers, require midlevel, standard desktop machines;

7. researchers, public relations and technical writers utilize mobile technology (laptops)

8. Rental fees for servers increase 3.5% each year.

9. You backup your data, paying per megabyte. (see table)

The technology you buy depends on your personnel. See table below for cost of specific technology. While backups fluctuate per/month, we will calculate backup costs per year.

Your Technology/supplies worksheet will have the structure below, with additional columns for years 2 and three.

1. Research: Five (5) offices 10 feet x 10 feet

2. Data Processing: Three (3) offices are 12 x 7

3. Administrative: Two (2) offices are 10 x 22

4. Web/technical writing and outreach: Three (3) offices are 8 x 9

5. Information Technology: Two (2) Offices are 9 x 9

1. The cost of rental per month is 1.25/sqft

2. The reception area = 300 sqft

3. IT room = 10’ X 15’

4. Rent will increase by 2% for the second and third year

The summary worksheet is the first worksheet in the workbook, which includes totals from each of the three other worksheets (must use multi-sheet references), including one chart that represents the summary table.

In this section of the project, you will 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 Employee table 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)

· 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-paste location from the Employees and Room numbers Sheet in the same RFP_Project.xlsx file.

Create these fields:

· Employee ID

· Last name

· First name

· Location

· Laptop Table

· Desktop Table

SETTING THE TABLE JOINS

· Network Table

Create these fields:

· Device ID

· Description

· Vendor

· IP number

· Field property: format @@\.@@\.@@\.@@ (may need to adjust for number)

· IP number data type: text

· Location

· Server Table

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

· Field property: format “S_”@@\.@@\.@@\.@@ (may need to adjust for number)

· 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 prefixes and 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 desktops and 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

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. hint: You will also need to create outer joins to the Employees tables from Laptops and Desktop tables to return the full results.

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.

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. Note: you must first add yourself as an employee before entering your workstation information.