Microsoft Access Database

GuyFawkes73

ACh678and9SV1.docx

Home >Computer Science homework help >Microsoft Access Database

Chapter 9 Assignment

Using the LANDSCAPE database, complete Step 1 through Step 7.

Step 1 Insert the data as a look-up field in the Customer table.

Service Code

S01

S02

S03

S04

S05

S06

Once you have created the look-up field, fill in the field using the data below to guide you.

Customer_No	Service Code
A22	S01
B10	S03
C04	S05
C13	S02
C23	S03
D21	S04
G34	S04
K10	S01
M32	S01
N00	S02
P12	S05
S11	S05
T16	S06
W45	S02

Step 2 Insert a picture in the supervisor table for Janet Febo, Kyle Martinson, and Elena Rogers. See below for what an acceptable picture is NOT.

NOTE: Here is an example of a picture that is not acceptable for a picture of a supervisor. Please make every attempt to size the picture so that all the features can be seen.

NOTE: Here is an example of a picture that is acceptable for a picture of a supervisor. Please make every attempt to size the picture so that all the features can be seen.

Step 3 Establish one-to-many relationship between the tables in the database. Use the tables given to you in the database. Do not create any new tables to establish the relationship(s). Enforce referential integrity for the relationship(s).

Step 4 Create a query that shows the ID Number, Amount Paid, and Balance Due. Return the top 5 records of the customers with the highest balance. All fields in the query are to show when the query is run. Call the query BALANCE_DUE.

Step 5 Create a new query. Each customer has paid an amount toward the total bill due with a balance remaining. The query is to show The Customer Name, Address, City, State, Zip Code, Balance, Amount Paid and what is still owed. The amount spent is based on the balance due minus the amount paid. All fields in the query are to show. Call the query CUSTOMER_OWES.

Step 6 Create a query using all fields from the Customers Request table. Determine how many hours are remaining for each Customer Number. The remaining hours is determined by total hours minus hours spent. Use all fields in the query. Call the query HOURS_REMAINING.

Step 7: Create a parameter query that will ask you for the supervisor’s number of the customer. Include the Customer number, Customer name, address, city, and state. The name of the query is CUSTOMER_SUPERVISOR.

Using the ORDERS database, complete Step 8 through Step 9.

Step 8: Apply the concepts of total queries, create a query which shows the total profit for all orders placed. The answer should be in currency format. The name of the query is called TOTAL_SALES.

Step 9: Apply the concepts of a crosstab query to create the following crosstab query. I would like to see each state and the subcategory quantities for each state and the total profit. The name of the query is called SUBCATEGORY_CROSSTAB.

Using the ADVENTUREWORKS database, complete Step 10.

Step 10: Create an update query. In the product table, we want to increase the safety stock level by 20%. Use an update query to accomplish this. In the query include the Name, Safetystock Level, and Reorder Point field. The name of the query is called UPDATE_SAFETY STOCK. Be sure that all fields show when the query is complete.

Step 11: Upload your work. Be sure that the professor can open what you have uploaded. Be sure you upload all databases as required. Please upload them at the same time. If you make any corrections and need to upload again, please upload all databases. Remember: I only look at your most recent upload.