Displays the names for the product categories in the table

Assignment Help Database Management System

Reference no: EM131981427

Access Lab Assignment

Work on the provided Access file, BUS506E Lab Exam to answer the questions below.

1. On the table, SalesOrderHeader, provide the grand total of SubTotal at the bottom row of the records. You may need to convert the Short Text into Numeric data attribute for the SubTotal.

2. Set a validation rule on the table SalesOrderHeader such that OrderDate should be earlier than ShipDate and provide an error message when the rule is not kept during the data entry on the OrderDate and ShipDate.

3. Set up a table-based Lookup table for the data field ProductCategoryID of the table ProductSubcategory, that displays the names for the product categories in the table ProductCategory, instead of the numbers.

4. Using the table SalesOrderHeader, set up a query, SalesQ4_query, that displays SalesOrderID, OrderDate, TotalDue, for the orders made in the fourth quarter of year 2001, that is, from Octerber 1st till December 31st, 2001.

5. We want to know how much were sold by each product in year 2001. Set up a query, SalesByProduct_query, that displays ProductID, Name of the Product, and sum of LineTotal of SalesOrderDetail in the year 2001.

6. Close the SalesByProduct_query from the right side window. In the All Access Objects window, create a new query, SalesByProductWithNoSale_query, by Right-clicking on the SalesByProduct_query, copying, pasting, and renaming. Using Left Join or Right Join, modify the SalesByProductWithNoSale_query, that displays all the products that includes the not-sold products too.

7. Table SalesOrderHeader is set in an inconvenient way because it requires to enter SubTotal, TaxAmt, Freight, and TotalDue individually. These data fields can be readily calculated if we set up a query. Create a query, SalesOrderHeader_extension that displays SalesOrderID, SubTotal (sum of LineTotal of SalesOrderDetail), TaxAmt (8% of LineTotal), Freght (3% of LineTotal), and TotalDue (111% of LineTotal) in Currency format.

8. Using OrderedItems query, set up a crosstab, SaleByCustomerAndProductCategory_crosstab that displays sum of sale amount ([UnitPrice]*[OrderQty]) by each customer and each product category, with the SaleTotal (SaleTotal: [UnitPrice]*[OrderQty]) per customer.

Attachment:- Lab Database.rar

Reference no: EM131981427

Questions Cloud

A group of investors are analyzing the property : A group of investors are analyzing the property to determine if it would be a worthwhile investment.

Comparing and contrasting the strategies by explaining : Define the security strategies of Defense in Depth and Layered Security along with comparing and contrasting the strategies by explaining

Distinguishes management from leadership and managers : Roger works for a business software firm and is passionate about his work. He is committed to delivering high-quality software solutions on schedule.

Discuss the similarities of the flowchart and blueprint : Service organizations tend to manage their processes by use of flowcharting and blueprinting. Discuss the differences and similarities of the flowchart.

Displays the names for the product categories in the table : On the table, SalesOrderHeader, provide the grand total of SubTotal at the bottom row of the records.

Predictive versus adaptive sdlc : Analyze predictive SDLC versus adaptive SDLC. Ascertain at least two (2) advantages and two (2) disadvantages of each approach.

What the organization can reasonably deliver : We know customers might expect or demand more than a service organization can deliver, yet the organization must keep the perception of the value.

Personal data on each employee : Create an Employee table with the columns listed below to hold the personal data on each employee. Take a screenshot of the executed code and the results.

What will be your monthly payment : If you plan to borrow $169,000, what will be your monthly payment?

User Account

All Pages