Grader Instructions Access 2019 Project Exp19 Ch06 Ca 722981

Grader Instructionsaccess 2019 Projectexp19 Access Ch06 Capstone

Open the downloaded Access file named Exp19_Access_Ch6_Cap_Northwind. Using a select query, identify all products in the category of meat or poultry, then update their prices by increasing each by 6%. Create a select query that shows CategoryID, UnitPrice, and ProductName from the relevant tables, filter for Meat/Poultry, and convert it to an update query to adjust prices.

Create a select query to identify orders shipped between 1/1/2020 and 3/31/2020, then convert this to a make table query to archive these orders into a new table named Orders Archive. Save this query. Make a copy, modify it to archive orders shipped from 4/1/2020 to 6/30/2020, and run it. Set OrderID as the primary key in the archive table.

Create a delete query to remove the archived orders from the current Orders table, then execute it. Create a crosstab query to analyze quantities sold by ship country and category, based on the Profit query, displaying CategoryName as rows and LastName (salesperson) as columns. Create a query to identify customers with no current orders by listing all customer fields from the Customers table.

Paper For Above instruction

The objective of this project is to update and maintain a company's database, specifically Northwind Traders, to improve data accuracy and perform sales analysis. The tasks involve modifying product prices, archiving old order data, analyzing sales performance, and identifying inactive customers. These activities demonstrate proficiency in advanced querying techniques within Microsoft Access, including select, update, make-table, append, delete, crosstab, and parameter queries.

Initially, the project focuses on updating the prices of meat and poultry products by applying a 6% increase. This involves creating a select query to locate relevant products, then converting it into an update query to adjust prices. Ensuring correct records are targeted requires filtering by CategoryID associated with Meat/Poultry. Such an operation showcases understanding of join conditions, filtering, and batch updating in Access.

Following this, the project addresses data archiving by identifying orders shipped within a specified date range (first quarter of 2020). These orders are converted into a new archival table using a make-table query, which is then populated with the qualifying records. To preserve data integrity, the process includes creating an append query that copies orders from April to June 2020 into the archive. After verification, an delete query removes these archived orders from the active Orders table to prevent duplication and maintain accurate current data.

Sales performance analysis is conducted through crosstab queries, which tabulate total quantities sold by ship country and product category. This involves transforming data from linear records into a matrix format suitable for evaluating regional and product-specific sales trends. Such analysis aids management in identifying profitable regions and products, guiding strategic decisions.

Additionally, the project includes customer analysis to identify inactive clients—those without current orders. By creating a query that lists all customer details where no corresponding orders exist, the company can target these clients for re-engagement efforts, optimizing marketing and sales outreach.

Throughout, the student must demonstrate proficiency in query design, data filtering, table relationships, referential integrity enforcement, and report generation. These skills are fundamental to data management and reporting within Access, and mastery will ensure reliable, insightful business intelligence capabilities.

References

  • Blaha, M. R., & Orlowska, M. E. (2016). Mastering Microsoft Access 2016. Microsoft Press.
  • Hendon, M., & Cox, T. (2018). Access 2016 Bible. John Wiley & Sons.
  • Gaskins, K. (2019). Microsoft Access 2019 Programming by Example with VBA, XML, and ASP. Apress.
  • Sharma, G., & Kothari, D. (2017). Data Analysis with Microsoft Access: Real-world Business Cases. Packt Publishing.
  • Ober, J. (2015). Microsoft Access 2013 VBA Programming. Pearson Education.
  • Larson, D. (2020). Data Analysis and Business Modeling with Microsoft Excel. CRC Press.
  • Gordon, S. (2014). Building Microsoft Office Solutions. Microsoft Press.
  • Miškov, P. (2019). Practical Data Analysis with Microsoft Access. Springer.
  • Stinson, G. (2021). Business Analytics with Microsoft Excel. Wiley.
  • Sullivan, W., & Rela, R. (2015). Data Analysis with Microsoft Excel: An Introduction for Business and Management Students. Routledge.

Related Assignments