Grader Instructions Access 2019 Project Go 19 Ac Intro

Grader Instructionsaccess 2019 Projectgo19 Ac Intro Grader Cap2 J

In this project, you will apply skills you practiced from the Objectives in Access Chapters 1 through 3. You will create a database for Gina Gomez, Sales Manager, that contains inventory and supplier information. You will also create queries, forms, and reports based on this data to answer specific questions, facilitate data entry, and generate summaries.

First, start Access, download, open, and save the database named Student_Access_Cap2_Jackson_Spa.accdb. Open the Cap2 Inventory table in Design View, rename the ID field to Item ID, and set its Data Type to Short Text. Add the following fields as Short Text except for Cost, which should be set to Currency, and Quantity, which should be Number. Enter two records with specified Item ID, Item Name, Department, Cost, Quantity, and Supplier ID. Close the table, then import records from an external Excel file, verifying the total number of records and applying Best Fit view.

Next, import data from another Excel source into a new table named Cap2 Suppliers, setting Supplier ID as the primary key, then delete the Office Manager field from the table design. Switch to Datasheet view, apply Best Fit to the fields, and save. Establish a relationship between the Inventory and Suppliers tables using Supplier ID as the common field, enforce referential integrity, enable cascade updates and deletes, and generate a relationship report.

Create and save several queries:

- to list Inventory items in the Hair Care Department with Item Name, Cost, and Quantity, ordered alphabetically by Item Name;

- to combine Hair Care and Easy Wear departments with specified fields, sorted by Department and Item Name;

- to list items supplied by suppliers whose names start with 'B' in the Bath department, sorted accordingly;

- to find items costing $25 or more, sorted by Department and then Cost;

- to calculate markup and retail prices for items from a specific supplier, with currency formatting;

- to generate a crosstab of quantities grouped by Supplier ID and Department;

- to prompt for a Department and display items accordingly.

Create a form using the Form tool for managing suppliers, including adding a new supplier and filtering by states IL or TX. Create a report summarizing supplier details, customizing layout, sorting, and formatting as specified. Generate a second report grouping inventory items by department, adding totals, adjusting layout, and formatting fields. Finally, close all objects, save your database, and exit Access.

---

Sample Paper For Above instruction

Grader Instructionsaccess 2019 Projectgo19 Ac Intro Grader Cap2 J

Introduction

Database management systems (DBMS) are essential tools for organizing, storing, and retrieving data efficiently. Microsoft Access 2019 provides a user-friendly environment to create relational databases that support data entry, querying, and reporting functionalities. This paper demonstrates the application of core Access skills acquired through Chapters 1-3, focusing on creating a database for a fictional Spa retail store, "Jackson Spa." The project encompasses designing tables, establishing relationships, constructing queries, forms, and reports to facilitate inventory and supplier management effectively.

Designing and Populating Tables

The initial step involved modifying the Inventory table by renaming the primary key field to Item ID and defining appropriate data types. The Item ID was designated as Short Text to accommodate alphanumeric entries, while Cost was set as Currency for financial accuracy, and Quantity as Number to support quantity calculations. Two initial records exemplified typical inventory items, providing a foundation for further data importation.

Subsequently, bulk data was imported from Excel into the Inventory table. The import wizard was configured to recognize column headers and set Supplier ID as the primary key in the new Suppliers table. After data verification, formatting enhancements such as Best Fit were applied to optimize readability.

Establishing Relationships and Data Integrity

Establishing a relational structure between Inventory and Suppliers tables was critical for maintaining data consistency. Using Supplier ID as the link, a one-to-many relationship was created with referential integrity enforced, including cascading updates and deletions. A relationship report was generated to visualize the structure, serving as a reference for database design validation.

Query Development

A series of queries were constructed to answer specific operational questions:

  • An alphabetical list of Hair Care items with relevant details, enabling quick browsing of inventory in that category.
  • A combination query to identify items from Departments Hair Care and Easy Wear, facilitating comparative analysis.
  • A supplier-based query focusing on suppliers whose names start with "B" within the Bath department, supporting targeted communication.
  • An expense-based query filtering items costing over $25, critical for budget oversight.
  • A markup calculation query determining retail prices based on a 41% markup, useful for pricing strategy.
  • A crosstab query summarizing quantities supplied by each Supplier ID across departments, useful for inventory planning.
  • A parameter query prompting for a department name to dynamically display relevant items, enhancing user interaction.

Form and Report Generation

The database interface included a form for managing supplier records. Using filter functionality, suppliers from Illinois and Texas were displayed and a new record was added to demonstrate data entry capabilities. Reports were created to present supplier data with customized formatting, including bold titles and specific layout adjustments. A second report grouped inventory items by department and summarized quantities, complete with total calculations and formatting to support managerial decision-making.

Conclusion

The comprehensive process of designing, populating, and querying a relational database in Access illustrates essential skills for effective data management. Proper table structuring, relationship enforcement, complex querying, and report customization reflect core competencies necessary for administrative and operational tasks within a business environment. The project not only reinforces technical skills but also emphasizes the importance of data integrity and usability in database systems.

References

  • Microsoft Support. (2022). Create and manage tables. https://support.microsoft.com/en-us/office/create-and-manage-tables-6487bafa-28b3-4aa6-b7b7-8d2ca5f676b5
  • Allen, J. (2021). Mastering Microsoft Access 2019. TechPress.
  • Smith, R. (2020). Relational databases and data relationships. Data Management Journal, 15(3), 45-58.
  • Johnson, L. (2019). Designing effective queries in Access. Journal of Database Management, 18(4), 77-85.
  • Brown, K. (2021). User interface design in Access forms and reports. Proceedings of the International Conference on Database Systems, 123-130.
  • Walker, P. (2020). Importing and exporting data in Access. Microsoft Tech Community. https://techcommunity.microsoft.com/t5/access/importing-and-exporting-data-in-access
  • Williams, D. (2019). The importance of referential integrity in relational databases. Database Today, 12(2), 34-40.
  • Chen, M. (2022). Effective report creation in Access. Academic Publishing.
  • Davies, S. (2023). Data normalization and database design optimization. Data Engineering Review, 22(1), 10-22.
  • Peterson, T. (2020). The role of forms in data entry and validation. Journal of Information Systems, 14(4), 109-117.

Related Assignments