+1443 776-2705 panelessays@gmail.com

Steps for final project data dictionary and documenter

Steps to print data dictionary for all tables:

1. Select Database Tools tab.

2. Click Database Documenter icon.

3. Documenter window pops up. Select all table names.

4. Click Options button.

5. Print Table Definition window pops up. There are three sections: Tables, Fields, Indexs:

a. For Tables – Include Properties and Relationships.

b. For Fields – Select Names, Data Types and Sizes only.

c. For Indexes – Select Nothing.

6. Click OK.

7. Documenter window appears, click Ok.

8. Click PDF or XPS icon on upper right tool bar. Save in PDF format and upload to Blackboard


ACCT-361: Queries Lab

Complete the review exercise prior to completing the lab. Detailed instructions on how to create the queries are included in the instructional videos available on Bb in this lesson plan. If you are unsure of the work you completed while following the instruction, you should recreate the queries that were difficult for you on your own. Future labs will have detailed, step-by-step written instructions, as the difficulty level increases. A “queries help” document is also available on Bb if needed.

Pets-R-Us. The REA model of the sale event is below.

The objective of this lab is to help you learn how to query a database to obtain useful information. Copy the Pets-R-Us queries template file in Bb directly to your computer or flash drive. Do not open it within your browser, because you will not be able to save your work. This is an extension of the database you created for the tables and relationships lesson.

Overview:

You will create queries for each of the following questions. Unless otherwise specified, sort all queries by the most logical field. The amount of data in this database is small. You can verify the accuracy of your results by a simple (and required) review of the data.

You can view the underlying SQL for each query after you complete each one. To do this, under the database tools tab, select the documenter and choose the SQL option for your query. For example, the SQL for qryA1 (the first query) is:

SELECT tblCustomer.[Customer Last Name], tblCustomer.[Customer First Name], tblCustomer.[Customer Age] FROM tblCustomer WHERE (((tblCustomer.[Customer Gender])=”F” ORDER BY tblCustomer[Customer Last Name], tblCustomer[Customer First Name]

A: Simple Queries (these are from the review)

  1. List all female customers with their last and first names and their age. Save your query as qryA1.
  2. List the item # and description and unit profit in dollars (using the expression, “Unit Profit”) made on each product and sort by item #. Save your query as qryA2.

B: Linked Table Queries

  1. List all the customers, by last and then first names, whom Heather has sold inventory. List each customer only once. Name this query qryB1.
  2. List all the products sold by each clerk. For each clerk, list their name, the item #, and a total quantity sold for each product (e.g., each product will be represented only once for each clerk). Save your query as qryB2.

C: Crosstab Query

  1. Create a crosstab query using the query wizard. Use the query, qryB2, rather than a table, as we are using more than one table for this result. Your row heading will be Item # and your column heading will be Sales Clerk Name. Your resulting answer should include the total number of each product sold, broken down by totals sold by clerk. Please rename your expression for the total quantity to be just “Total.” Make sure the total is a sum and not an average for the output (which is the default in the query wizard). Save your query as qryC1.

D: Not-So-Simple Queries

  1. Which clerk should receive the Outstanding Sales Clerk award for the first few days of operations? Base the award on total gross margin for sales made by each clerk. You will need two queries to answer this question. The first should calculate the gross margin for each line item of each sale. The second will group by clerk and total the margins earned. Name the queries qryD1a and qryD1b.
  2. How much revenue was earned on the Nutro Max Weight Control Dog Food (Item #DS067) on Invoice#1007 List on your answer table the Invoice# and product description in addition to the line item revenue earned. This is typically called the extended price. Name the query qryD2.
  3. Calculate the total sale amount for a selected invoice. Instead of creating a query from scratch, we will copy, paste, and modify a previously created query. To do this, first right click on qyrD2 and choose “copy.” Paste it anywhere in the object pane as qryD3a. In design view of qryD3a, remove the selection criteria for Item # and Invoice#. Save. Create a new query using qryD3a and use only the invoice number and extended price fields. Total the extended price field from D3a. Under the property sheet, rename the title of the field (Caption) to be “Total Invoice Amount.” Now we will learn to build a “parameter query.” In the selection criteria property of the Invoice# field, type [Please enter the invoice number]. Make sure you use the brackets. Save it as qryD3b. This type of query is referred to as a parameter query, because the user will query the database based on a “parameter” entered at the time of the query, rather than using preset criteria.
  4. What were the total revenues received for dog food? Use the like expression and the wild card character “*” to find item #s that begin with “DS.” You will need two queries: One to create total revenues for each dog food item and one to create a total that includes only one record. Name the queries qryD4a and qryD4b.

Now, we will learn to build “outer-join” queries and understand the “null-to-zero” function.

  1. Calculate the ending inventory quantities for all inventory items. You will first need to total the quantity sold for each item # in your first query. Name the field “Total Qty Sold,” and save the query as qryD5a. Use qryD5a along with the inventory table for your second query to calculate the EQOH. Make sure you use the item # field from the inventory table and not the query object. You will need to edit the relationship properties of this second query so that every item in the inventory table is displayed in the output even if there is no corresponding sales for that product
  • (this is the outer join). To edit the relationship, right-click on the relationship line in the query design area and edit the join properties. Save the query as qryD5b.

View your results. If you edited the join properties correctly, you will have a total of 52 records in the second query results, which corresponds with 52 inventory items in the inventory table. Notice that the products that were not sold have null (empty) fields. This is because Access cannot calculate using a null value. We need to correct this so that a zero is substituted for the null value. This is important for completing all outer-join queries that use calculations. To change the null field to zeros, edit the expression you created to calculate the ending Inventory quantity as follows:

EQOH: [Product BQOH]-Nz([Total Qty Sold],0)

Nz is a built-in function available in Access. We will learn more later. The last term reads as follows. If the value in the field [Total Qty Sold] is null, replace the null value with a “0.”

E: Additional Practice Queries (complete these on your own)

  1. Calculations. List all inventory item #’s, descriptions, and percentage markup (use the expression, “Markup”). Set the properties for the “Markup” query expression to format percentages with two decimal places. Which product has the highest markup?
  2. Grouping. List all return customers, i.e., those who have more than one sale (include the customer’s last and first names).
  3. Grouping and calculations. What were the total revenues for each day the company was in operation? You will have a record for each day. Title the revenue field, “Daily Sales.” When learning, it is better practice to separate queries – one for horizontal and one for vertical analysis.
  4. Outer-joins. Calculate the ending inventory value using standard costs for use on the company’s balance sheet. You will use an outer-join and the null-to-zero function so that all inventory items are accounted for regardless of whether they have been sold during the period. You can follow the steps in qryD5 to create the queries for total quantity sold and to calculate ending quantities on hand for each inventory item. Add a calculation to value the ending quantities of each item using standard costs. You should have all 52 products listed in the inventory table. The final query you create will simply total the values from the previous query so that there is only one field and only one record for total ending inventory valuation. Format the total inventory value field for currency and two decimal places. This is the check figure: Ending inventory value should be $15,358.16.
  5. Outer-joins: One more time! Prepare a reorder list for the purchasing department. Again, you will use an outer-join and the null-to-zero function so that all inventory items are displayed regardless of whether they have been sold during the period. The output of the query will provide the purchasing department with a list of products sorted by those products that need to be reordered the soonest. All products will be displayed, along with the description, ending quantities, reorder points, and a calculated field that provides the difference between the reorder point and the ending quantity on hand. You will have 52 products listed.

Systems Development Project
Grading Rubric

Starting Grade

Extra Points Awarded:
Industry relevance (extra attention)
Extra creativity/effort/complexity (in general)
Class or video presention
Extra effort on controls (depending on scope)
2 *:* relationships (if incorporated correctly)
Extra event captured (depends on complexity)
Extra report or 2 events with *:* (depends on complexity)
Fully-functioning system
Extra password code
Extra search feature
Extra unfilled event combo box
Extra set value function

Based on 100%

80%

+2%
+1% to +5%
+5%
+2 to +5%
+3%
+2% to +4%
+1% to +3%
+2%
+1%
+1%
+1%
+1%

Points Deducted:
Missing REA diagram with cardinalities
REA computer drawn, but cardinalities hand-drawn
Events are not related
Only one event is captured
Missing combo boxes
Combo boxes are uninformative (i.e., only the PKs show)
No *:* relationship
*:* form not created with subforms on same form or does not work
Cannot add new record or event (although don’t double-count above)
Maintenance forms not created and no submenus
Maintenance forms created, but no access on submenus
Only one report
Relationships not set
Relationships set for most part, but some cannot be enforced
Missing documentation of tables
No password or hidden database objects window
No main menu at startup of system
No default date
Only one event captured in system
No totals on forms or access to report with totals on forms
Incorrect fields in tables (depends on severity)
No closed loop verifications
Miscellaneous design problems including missing fields from forms
Other problems not listed above – at the discretion of the instructor

-2%
-1/2%
-1%
-30%
-3% to -5%
-2%
-15 to -25%
-10% to -15%
-5% to -10%
-5%
-3%
-3%
-10%
-2% to -4%
-1%
-1% to -2%
-2%
-1%
-15% to -25%
-2%
-1% to -10%
-1% to -3%
-1% to -3%
-1% to -10%

Systems Development Project
Grading Rubric

Starting Grade

Extra Points Awarded:
Industry relevance (extra attention)
Extra creativity/effort/complexity (in general)
Class or video presention
Extra effort on controls (depending on scope)
2 *:* relationships (if incorporated correctly)
Extra event captured (depends on complexity)
Extra report or 2 events with *:* (depends on complexity)
Fully-functioning system
Extra password code
Extra search feature
Extra unfilled event combo box
Extra set value function

Based on 100%

80%

+2%
+1% to +5%
+5%
+2 to +5%
+3%
+2% to +4%
+1% to +3%
+2%
+1%
+1%
+1%
+1%

Points Deducted:
Missing REA diagram with cardinalities
REA computer drawn, but cardinalities hand-drawn
Events are not related
Only one event is captured
Missing combo boxes
Combo boxes are uninformative (i.e., only the PKs show)
No *:* relationship
*:* form not created with subforms on same form or does not work
Cannot add new record or event (although don’t double-count above)
Maintenance forms not created and no submenus
Maintenance forms created, but no access on submenus
Only one report
Relationships not set
Relationships set for most part, but some cannot be enforced
Missing documentation of tables
No password or hidden database objects window
No main menu at startup of system
No default date
Only one event captured in system
No totals on forms or access to report with totals on forms
Incorrect fields in tables (depends on severity)
No closed loop verifications
Miscellaneous design problems including missing fields from forms
Other problems not listed above – at the discretion of the instructor

-2%
-1/2%
-1%
-30%
-3% to -5%
-2%
-15 to -25%
-10% to -15%
-5% to -10%
-5%
-3%
-3%
-10%
-2% to -4%
-1%
-1% to -2%
-2%
-1%
-15% to -25%
-2%
-1% to -10%
-1% to -3%
-1% to -3%
-1% to -10%

ACCT-361: Review Exercise Queries – Information Retrieval from RDBMS

Pets-R-Us. The REA model of the sale event is below.

The objective of this exercise is to help you review basic querying skills you learned in MIS-303. Copy the Pets-R-Us queries template file in Bb directly to your computer or flash drive. Do not open it within your browser, because you will not be able to save your work. This is an extension of the database you created during the tables and relationships lesson.

Overview:

You will create queries for each of the following questions. Unless otherwise specified, sort all queries by the most logical field. The amount of data in this database is small. You can verify the accuracy of your results by a simple (and required) review of the data.

A: Simple Queries

  1. List all customer last and first names and occupations, sorted by customer last name. Save your query as qry1.
  2. List all female customers with their last and first names and their age. Save your query as qry2.
  3. List each customer’s last and first names, occupation, and state, where state = MD. Save your query as qry3.
  4. List the item # and description and unit profit in dollars (using the expression, “Unit Profit”) made on each product and sort by item #. Save your query as qry5.

ACCT-361: Queries Lab

Complete the review exercise prior to completing the lab. Detailed instructions on how to create the queries are included in the instructional videos available on Bb in this lesson plan. If you are unsure of the work you completed while following the instruction, you should recreate the queries that were difficult for you on your own. Future labs will have detailed, step-by-step written instructions, as the difficulty level increases. A “queries help” document is also available on Bb if needed.

Pets-R-Us. The REA model of the sale event is below.

The objective of this lab is to help you learn how to query a database to obtain useful information. Copy the Pets-R-Us queries template file in Bb directly to your computer or flash drive. Do not open it within your browser, because you will not be able to save your work. This is an extension of the database you created for the tables and relationships lesson.

Overview:

You will create queries for each of the following questions. Unless otherwise specified, sort all queries by the most logical field. The amount of data in this database is small. You can verify the accuracy of your results by a simple (and required) review of the data.

You can view the underlying SQL for each query after you complete each one. To do this, under the database tools tab, select the documenter and choose the SQL option for your query. For example, the SQL for qryA1 (the first query) is:

SELECT tblCustomer.[Customer Last Name], tblCustomer.[Customer First Name], tblCustomer.[Customer Age] FROM tblCustomer WHERE (((tblCustomer.[Customer Gender])=”F” ORDER BY tblCustomer[Customer Last Name], tblCustomer[Customer First Name]

A: Simple Queries (these are from the review)

  1. List all female customers with their last and first names and their age. Save your query as qryA1.
  2. List the item # and description and unit profit in dollars (using the expression, “Unit Profit”) made on each product and sort by item #. Save your query as qryA2.

B: Linked Table Queries

  1. List all the customers, by last and then first names, whom Heather has sold inventory. List each customer only once. Name this query qryB1.
  2. List all the products sold by each clerk. For each clerk, list their name, the item #, and a total quantity sold for each product (e.g., each product will be represented only once for each clerk). Save your query as qryB2.

C: Crosstab Query

  1. Create a crosstab query using the query wizard. Use the query, qryB2, rather than a table, as we are using more than one table for this result. Your row heading will be Item # and your column heading will be Sales Clerk Name. Your resulting answer should include the total number of each product sold, broken down by totals sold by clerk. Please rename your expression for the total quantity to be just “Total.” Make sure the total is a sum and not an average for the output (which is the default in the query wizard). Save your query as qryC1.

D: Not-So-Simple Queries

  1. Which clerk should receive the Outstanding Sales Clerk award for the first few days of operations? Base the award on total gross margin for sales made by each clerk. You will need two queries to answer this question. The first should calculate the gross margin for each line item of each sale. The second will group by clerk and total the margins earned. Name the queries qryD1a and qryD1b.
  2. How much revenue was earned on the Nutro Max Weight Control Dog Food (Item #DS067) on Invoice#1007 List on your answer table the Invoice# and product description in addition to the line item revenue earned. This is typically called the extended price. Name the query qryD2.
  3. Calculate the total sale amount for a selected invoice. Instead of creating a query from scratch, we will copy, paste, and modify a previously created query. To do this, first right click on qyrD2 and choose “copy.” Paste it anywhere in the object pane as qryD3a. In design view of qryD3a, remove the selection criteria for Item # and Invoice#. Save. Create a new query using qryD3a and use only the invoice number and extended price fields. Total the extended price field from D3a. Under the property sheet, rename the title of the field (Caption) to be “Total Invoice Amount.” Now we will learn to build a “parameter query.” In the selection criteria property of the Invoice# field, type [Please enter the invoice number]. Make sure you use the brackets. Save it as qryD3b. This type of query is referred to as a parameter query, because the user will query the database based on a “parameter” entered at the time of the query, rather than using preset criteria.
  4. What were the total revenues received for dog food? Use the like expression and the wild card character “*” to find item #s that begin with “DS.” You will need two queries: One to create total revenues for each dog food item and one to create a total that includes only one record. Name the queries qryD4a and qryD4b.

Now, we will learn to build “outer-join” queries and understand the “null-to-zero” function.

  1. Calculate the ending inventory quantities for all inventory items. You will first need to total the quantity sold for each item # in your first query. Name the field “Total Qty Sold,” and save the query as qryD5a. Use qryD5a along with the inventory table for your second query to calculate the EQOH. Make sure you use the item # field from the inventory table and not the query object. You will need to edit the relationship properties of this second query so that every item in the inventory table is displayed in the output even if there is no corresponding sales for that product
  • (this is the outer join). To edit the relationship, right-click on the relationship line in the query design area and edit the join properties. Save the query as qryD5b.

View your results. If you edited the join properties correctly, you will have a total of 52 records in the second query results, which corresponds with 52 inventory items in the inventory table. Notice that the products that were not sold have null (empty) fields. This is because Access cannot calculate using a null value. We need to correct this so that a zero is substituted for the null value. This is important for completing all outer-join queries that use calculations. To change the null field to zeros, edit the expression you created to calculate the ending Inventory quantity as follows:

EQOH: [Product BQOH]-Nz([Total Qty Sold],0)

Nz is a built-in function available in Access. We will learn more later. The last term reads as follows. If the value in the field [Total Qty Sold] is null, replace the null value with a “0.”

E: Additional Practice Queries (complete these on your own)

  1. Calculations. List all inventory item #’s, descriptions, and percentage markup (use the expression, “Markup”). Set the properties for the “Markup” query expression to format percentages with two decimal places. Which product has the highest markup?
  2. Grouping. List all return customers, i.e., those who have more than one sale (include the customer’s last and first names).
  3. Grouping and calculations. What were the total revenues for each day the company was in operation? You will have a record for each day. Title the revenue field, “Daily Sales.” When learning, it is better practice to separate queries – one for horizontal and one for vertical analysis.
  4. Outer-joins. Calculate the ending inventory value using standard costs for use on the company’s balance sheet. You will use an outer-join and the null-to-zero function so that all inventory items are accounted for regardless of whether they have been sold during the period. You can follow the steps in qryD5 to create the queries for total quantity sold and to calculate ending quantities on hand for each inventory item. Add a calculation to value the ending quantities of each item using standard costs. You should have all 52 products listed in the inventory table. The final query you create will simply total the values from the previous query so that there is only one field and only one record for total ending inventory valuation. Format the total inventory value field for currency and two decimal places. This is the check figure: Ending inventory value should be $15,358.16.
  5. Outer-joins: One more time! Prepare a reorder list for the purchasing department. Again, you will use an outer-join and the null-to-zero function so that all inventory items are displayed regardless of whether they have been sold during the period. The output of the query will provide the purchasing department with a list of products sorted by those products that need to be reordered the soonest. All products will be displayed, along with the description, ending quantities, reorder points, and a calculated field that provides the difference between the reorder point and the ending quantity on hand. You will have 52 products listed.