Instructions: From the following information, create tables in a new database and join the tables by the primary and foreign keys. Be sure that all fields appear in the relationship window. Print a copy of the relationship window. The tables should be normalized and properly account for the described relationships. You will have to determine the necessary fields and should use appropriate field names that describe the field’s usage. You do not need to add records. You only need the tables (no data) joined in the relationship window.
I recommend that you read the online examples in the Database Normalization Handout 2015 and the section in Ch. 4 on normalization.
Data Needs for the Keystone Corp. Revenue Module:
Customer information, Sales agent information, Inventory information, Invoice information. Assume all sales are for cash only and that there are no installment sales, taxes or receivables. Note: Some fields may not be needed and you may have to add some fields.
Label as Page 2.
II. FULL DATABASE (You will create a database file and a Word document)
This exercise is based on a moving company, RealDealMovers, Inc. The moving company moves between several northwestern states and can also warehouse goods during the move. They own a number of warehouses in different states. Each warehouse has separate rate structures. The company must track information on the employees, customers, trucks, and warehouses in their database.
Download the files (zipped) for the database project from Canvas. Place them in a folder called AccessProjectFiles. Also, store your database in this file and periodically create a backup.
Instructions: Open Access and create a new database by importing the Excel files and the Access file into a new database named: your lastname_firstname_ section.mdb (ex. jones_tom_601.mdb). Perform the following exercises and print the results in MS Word by page number as indicated (it is essential that each page is properly referenced so that you receive appropriate credit). All information must be presented in a professional manner. Be certain that the screen-prints are readable and that field have been adjusted so that all entries can be read.
Import the following Excel files:
Import the following file from the MS Access database:
When importing files, be sure to specify (1) that the first row is a heading and (2) the primary key. Some primary keys are complex. These can be added after the table is created. You can use the same field names that were imported. However, be careful that the field data types are correct.
Be sure to backup your database periodically.
CREATE KEYS AND CHECK DATA TYPES
Some primary keys are complex (e.g., they require more than one field). To create a complex key, go to Design View and hold the Ctrl key down while selecting each field that will be a key. Then click on the primary key icon.
In Design View, check to see that all Primary Keys (PK) and Foreign Keys (FK) have the same data types. Establish complex keys for the following:
tblUnitRental: CustID, WarehouseID, UnitID
tblStorageUnit: UnitID, WarehouseID
Create a Relationship Window showing all of the tables. Join the tables properly and establish relational integrity. Arrange the tables neatly.
Create the following four input masks in tblEmployee:
tblEmployee – masks for SSN, WarehouseID, Zip, and Phone
Create validation rules for the following:
tblEmployee – rules for HourlyRate, StartDate
tblStorageUnit – rules for UnitSize
Hints: Hourly rate must be null or greater than zero. Start Date must be less than today’s date. Unit size will be limited to the existing unit sizes (use the IN function).
Page 3: Relationship Window with fully normalized tables showing relational integrity.
Page 4: Employee table Design View showing Input Masks
PAGE 5: StorageUnit table Design View showing Validation Rules
NOTE: For each of the following queries you will print a single page. At the top show the query in Design View (only the first page). At the bottom show the table in DataSheet View. Be sure to adjust columns to make the headings fully readable (especially the calculated fields). Adjust the copy so that the font is large enough to be readable. Each page should be printed in
Landscape orientation. Use the Snipping Tool (in Accessories) to cut the image from the screen and Ctrl + V to paste to the Word document. If you must use two pages then number as 6a, 6b etc.
In the following queries, print design view first.
Be sure that the fields are expanded to be readable – especially calculated fields. The print must be large enough to be readable. If you have access to the snipping tool (Windows 7 +) it makes cutting more efficient.
I suggest doing all of the steps that are straightforward first and then returning for those that are more difficult. Google will address any questions you might have.
Assume that only drivers with driving records of ‘A’ or ‘B’ are allowed to drive the large four-axle trucks. Create a list of these drivers who drive four-axles sorted by last name. Include pertinent information and save as qry4AxleDrivers.
Print as Page 6
The supervisor wishes to learn the driving records of the drivers who are doing poorly and their length of employment in days.
Create a new table (a Make Table query) of drivers having records less than a ‘B’ including their names, locations, start and end date, and length of employment sorted by length of employment and last name.
Print as Page 7
The supervisor asks you to create a
CrossTab query to show how many drivers of each record type live in which state. The state would be in the row and the record type in the column heading. Save as qryRecordsByState.
Print as Page 8
The supervisor would like a single query that determines the oldest employee(s). Save as qryOldest. Your query should return name, id, and birthdate. Hint: Use DMIN.
Print as Page 9
The supervisor would like a CrossTab query reporting the number of employees by type of position in each state. The position title would be the row and the state the column heading. Be sure to use position title (not ID). Save as qryPositionByState.
Print as Page 10
The supervisor would like to increase hourly rates by eight percent for employees who joined the company prior to 2010. (This will be an Update Query.) Save as qryIncreaseRates. Before running the query you may wish to copy the Employee table and create tblEmployeeOriginal (as a backup).
Print as Page 11
Management has requested an employee contact list with the employee names combined as last, first (combined into a single field using Concatenate) and other pertinent information sorted by last name. Save as qryContactList.
Print as Page 12
Management would like to know if any employees with the same last name and address. Create a Make Table query that includes all employees who have the same last name and address. Save as qrySpouses. Hint: Use the Query Duplicates Wizard.
Print as Page 13
Create a Cross-Tab query to show the drivers actual mileage (column) by job id (row). Save as qryDriverJobs.
Print as Page 14
Management would like to see a table showing total rents by customer by warehouse. Save as qryRents.
Print as Page 15
Show the SQL statements for the queries in steps 8 and 10 above. (Simply open the query in Design View, right-click and choose SQL View.) Note how the query appears in SQL.
Print as Page 16
USING SQL STATEMENTS IN A QUERY
SQL stands for Structured Query Language (see handout). Use the SQL handout to create the following queries.
In tblEmployee, increase the salary of salaried employees by 15%. Increase the salary of hourly employees by 10%. Show the SQL statement only and the query results. Using tblDriver, tblJobOrder, and tblJobDetail, create a SQL statement to sum the estimated
distances by driver. Print DriverID, JobID, Sum of Distance Estimates. Show SQL statement only and the query results.
Print as Page 17.
I suggest that you create a query for each report ordering the fields as you want them to appear in the report. Use the report wizard to create the basic report. Then use design view to add subtotals, totals, images, etc. There is an online document that explains how to use design view.
In the design of the report, keep the following in mind.
Create the report using the Wizard and then modify it in design view.
Create a single query that has all of the required components in the correct order.
Include the RealDealMovers logo at the top of the report.
Show all dollar amounts with a dollar sign and two decimal places.
At the bottom of every page include the page number, date, and name of the person who prepared the reports in the format “Prepared by Your Name.”
1. Create an income from jobs report using the following assumptions:
RealDeal charges $.65 per mile plus $.25 per pound for each job. The driver’s payment must be deducted to determine net income for the moving job. Drivers receive $45 per job plus their mileage rate. Prepare an income report that contains: the moving date, the job id, the driver’s name, the rate, the mileage and weight of the job, and the income calculations described. At the end of the report, show total income, total costs (payments to drivers), and total net income.
Note: The Jobs Revenue report will require a report header, page header, detail area, page footer, and report footer.
Print Report as Page 18
2. Create an income from storage unit reports using the following:
Group the information by warehouse and show the name of the renter by last name. Include the rent per unit, the total rents per each warehouse, and a grand total for all warehouses.
Note: The Storage Unit Revenue report will require a report header, page header, detail area, page footer, and report footer.
Print Report as Page 19
3. Create a report from the second SQL query as follows. Show the DriverID, JobID, Sum of Distance Estimates.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.