Select Rename, and type in the name. Creating a Query Using the Employee Table

Tiger Paws LLC Access Assignment
This assignment is going to deal with Human Resources, its role within the organization and the technology used to support it. Our hypothetical company is going to be Tiger Paw Holdings LLC.
Database Assignment Requirements
For this assignment you are going to use a database that has been created for you. The name of this file is: Tiger Paws Holdings LLC- Student File.
1. Download the file from Blackboard. Save it directly onto your desktop.
When you open the file, it will look like this:
2. You are going to use the Applicant Input Form to input data for our applicants. It is like one you would complete if you were going to apply for a job.
Double click on Applicant Input Form.
When you do your screen will look like this: See Next page
As you can see, 2 records have been entered for you. How do you know how many records you have? Look at the bottom of the screen and it will always tell you which record you are on, and how many total there are. In this screen shot we are at 1 of 2.
Click on the right arrow to get to the last record.
Click on the Submit Application button on the right side of the screen.
If done correctly, you will be looking at a blank record.
Entering Data
You are now ready to enter data onto the form. Follow the directions on the form. The data you enter does NOT need to be accurate.
For the highest education level, you will be choosing the entry from a pull-down list.
For the resume field it is not necessary to include a full resume here. Just type in a sentence or two about this applicant.
You do not enter the Applicant Number. It is computer generated and will be entered by Access when you start entering the applicant’s first name.
Requirement: Add a third applicant.
After you submit the application, click on the X on the right side of the window to close the Form.
Review Results in the Table
Double click on the Table name.
When it opens, you will be in Datasheet view. As you can see, you can insert data, copy data, and delete records directly from the Table. What you don’t see are the instructions and other aids to fill in the form. Since management is responsible for the accuracy of data input, and our industry goal is 99.5%, this would not be the best way to input data. That is why most data is entered or collected through custom designed forms.
Close the window by clicking on the X again.
Creating Queries to Problem Solve
Now that there is data in the table, we can do some problem solving. You are going to create 3 different queries.
a. LIST query. Gives us a list of records without criteria.
b. AND query. Gives us a list of records that satisfy specific requirements.
c. OR query. Gives us a list of records that satisfy more generalized requirements.
Query Requirements
Each item below is a separate Query. Each Query in all assignments this semester must have 3-4 fields that are used to answer them. Do not use all the fields that are in the table in each query. For a query to be successful at least one record must show when you run it.
When you work in Access make sure you are only working on 1 thing at a time. If you are going to create a query, then that is the ONLY thing that should be open in the workspace. Any tables, forms, other queries, reports should all be CLOSED. Failure to close tabs and only have 1 item open at a time will result in conflicts and errors.
a. First query: In Query Design, sort all the records by applicant’s job code and last name. Sort by Job Code in descending order, and Applicant Last Name in ascending order. Do not sort all 3-4 fields. For best results use Job Code as your first field selected. (Name it: LIST query)
b. Second query: Find all the records that satisfy two specific criteria that you create. For this you need criteria under 2 separate fields on the Criteria row of the Query Design. Do not sort any fields. (Name it: AND query)
c. Third query: Find all the records that satisfy one or the other of two criteria that you create. Criteria for one field is on the Criteria row, and the other is on the Or row in Query Design. Do not sort any fields. (Name it: OR query)

Creating a Report Using the Report Wizard
Your boss would like some information on the applications for the various job openings. The easiest way to do that is to create a report for them arranged by job code. If you need help going through the steps in Report Wizard, use the tutorial n Blackboard for creating a report with the following requirements:
a. Only include 3-4 fields. In this case make sure you use Job for Code Posting, Applicant First Name and Applicant Last Name as a minimum.
b. Group by Job for Code Posting.
c. Sort by Applicant Last Name.
d. Report title needs to have the name of the company, subject of the data, and the time period of the data.
Creating a Table in Access
You are now going to create your own table in Access. Does that mean you have to create a new file? No.
You may have multiple tables in an Access file. A company will have hundreds of tables- employees, vendors, customers, trucking companies, freight rates, federal tax tables, state tax tables, colors, seasons, product, and the list goes on and on. We are going to add a second one to this file.
a. To get started, select Create, Table Design. This table is going to be about Employees. That’s right, we are converting applicants to employees. Once they become employees, we can add information we couldn’t ask or collect when they were applicants to avoid any possible legal violations on recruiting and hiring.
You are now going to create a table with the fields from the Applicant table, and add these new fields: Date Hired, Date of Birth, Current Salary, Married with Dependents, Married without Dependents.
Open the Applicant table in design view. Click on the top left corner of the window, highlighting all the fields of the table. Select Copy. Close the window.
Return to your new Table 1 you are creating. From Home on the ribbon, click on Paste. It will paste a copy of all the fields from the other table along with their properties in this new table.

You will use the same fields from the applicant table, except change the field name from Applicant to Employee. Employee ID Number, Employee First Name, etc. Use the field properties from the applicant table as well. For the new fields you need to determine the data type, and properties. Adjust the field properties in design view as you name the fields. Do not leave fields with the default values. Use Attachment as your Data Type for picture fields.
When you create a table the first field is a primary key indicating that it is an auto number. A primary key field is a unique field. That means that no other record can have the data that is in that field in that record. Examples of unique data are: sku, bar code number, social security number, employee number, customer number, purchase order number, invoice number. Brand name, style number, first name, last name are not unique fields and should not have a primary key. If you don’t have a unique field, you should just click on the Primary Key icon and turn it off.
b. Using Datasheet view, enter 4 complete records.
Make sure that at least 2 of your employees have the same Employee Job Code. DO NOT use the same code for all your employees.
c. When you are done adding the records, close your table. Make sure to name it: Employees.
If you didn’t, you can rename it when it is closed. Make sure nothing is open in the workspace window. Right click on Table 1. Select Rename, and type in the name.
Creating a Query Using the Employee Table
You are now going to create a List Query, sorted by Employee Job Code. The fields to be included are Employee First Name, Employee Last Name, Current Salary and Employee Job Code. Label it: Employee Data for Excel.
Creating a Report Using the Employee Table
You are now going to create a Report using the Report Wizard Include the following fields: Employee Job Code, Employee First Name, Employee Last Name, Current Salary, and Picture.
a. You must group the employees by Job Code.
b. You must select Summary Options and select Sum, Detail and Summary, and Calculate percent of total for sums. This will give us information by Job Code, and totals for the company. The report will tell you what percent of your salary is going to each job title in your company. Select OK.
c. You must sort lowest to highest salary.
d. Don’t forget an appropriate report title. Titles must tell us who, what, and when about the data that is included in the report.
When you look at the report in Preview mode, your field names may not be completely visible. Your picture will be 3 inches in size- too big. You need to resize it.
Submitting the Completed File
Before submission your file should look like this:
To upload the file into the drop box you must close the file. Now you can submit it from your desktop.
The file will NOT upload if it is open.

Last Completed Projects