Auric Systems Access 2013 Project Create All Records Using I

Auric Systems Access 2013 Projectcreate All Records Using Information

Create an Access 2013 database named AuricSystems. In Datasheet View, add a Number data type field as the second field and set its Field Size to Long Integer as the third field. Switch to Design View, save the table as Customers with the exact field names, and ensure the primary key icon appears in the ID field. Enter two records directly into the table, avoiding leading zeros in the ID field and typing digits with decimal points in the Amount Pledged field without the dollar sign. Close the table and create a form based on the Customers table to add records 3 through 10, entering data similarly to previous entries. Save the form as Customers and open it to verify the data entry. Then, create eight queries:

  • Query 1: List Last Name of all records, sorted ascending
  • Query 2: List Company and Last Name, sorted ascending by Company and Last Name
  • Query 3: List ID, Last Name, and Phone for records with Last Name beginning with "Ru"
  • Query 4: List ID and State for records with ID greater than 7
  • Query 5: List ID and State for records with ID between 6 and 9
  • Query 6: List ID and Last Name for records with ID >5 and Last Name starting with "R"
  • Query 7: List ID and Last Name for records with ID >5 or Last Name starting with "R"
  • Query 8: List ID and Last Name sorted by ID in ascending order

Compare your query results with provided answers, then ensure that forms for each customer are properly created. Submit the complete Access database with all tables, forms, and queries intact.

Paper For Above instruction

Creating a comprehensive database in Microsoft Access 2013 involves meticulous steps to ensure data integrity, usability, and efficient data management. This project illustrates how to structure and populate a customer database, followed by the creation of queries essential for data retrieval and analysis, reflecting common tasks faced by database administrators and developers.

Introduction to Database Development in Access 2013

Microsoft Access 2013 serves as a powerful tool for developing relational databases with user-friendly interfaces. The initial step involves creating a new database named “AuricSystems,” setting the foundation for data organization. The database architecture hinges on defining tables with appropriate fields, data types, and key constraints to uphold data consistency and prevent redundancy.

Designing and Populating the Customer Table

The Customer table is designed with specific fields, including an ID, Last Name, Company, Phone, State, and Amount Pledged. In Datasheet View, you add a Number data type field as the second field and set its size to Long Integer to accommodate larger numerical entries. Switching to Design View ensures precise control over field properties, including setting the primary key in the ID field, which maintains record uniqueness.

Entering data directly into the table for the first two records establishes initial data points. It is important to avoid leading zeros in IDs (e.g., typing 1 instead of 01) and to enter monetary amounts without currency symbols, maintaining consistency in data format—digits and decimal points only.

Creating and Using Forms for Data Entry

Forms enhance data entry efficiency, especially for multiple records. After closing the table, users create a form based on the Customers table, allowing for easier data input in subsequent records (3-10). The form is saved and opened to verify accurate data entry, confirming that the user interface simplifies interactions and minimizes entry errors.

Developing Queries for Data Retrieval

Queries are vital for extracting specific data subsets based on criteria, necessary for reporting and analysis. The project demonstrates eight queries, each designed with specific selection criteria, sorting orders, and fields:

  1. List all last names in ascending order for alphabetic sorting.
  2. List company names alongside last names, sorted by company then last name, enabling organization-based searches.
  3. Retrieve IDs, last names, and phone numbers for last names starting with “Ru,” utilizing criteria with wildcards.
  4. List IDs and states for records with IDs over 7, filtering numeric data.
  5. List IDs and states for IDs between 6 and 9, illustrating range filtering.
  6. Combine conditions with AND: IDs over 5 and last names starting with “R,” refining search results.
  7. Combine conditions with OR: IDs over 5 or last names starting with “R,” broadening the search.
  8. Sort data by ID in ascending order, demonstrating sorting capabilities.

These queries showcase fundamental SQL operations like selection, sorting, and criteria definition, which are critical skills for effective database querying and reporting.

Conclusion and Best Practices

The process highlights the importance of precise field definitions, consistent data entry, and strategic query design. Creating forms for each customer ensures data accuracy and accessibility. Proper use of key constraints prevents duplicate entries, and sorting enhances data readability. Such systematic steps are essential for developing reliable, scalable databases capable of supporting dynamic business needs.

References

  • Hoffer, J. A., George, J. F., & Valacich, J. S. (2016). Modern Database Management (12th ed.). Pearson.
  • Rob,

    K., & Coronel, C. (2018). Database Systems: Design, Implementation, & Management (13th ed.). Cengage Learning.

  • Harrington, J. L. (2016). Relational Database Design and Implementation (4th ed.). Morgan Kaufmann.
  • Fitzgerald, J., & Dennis, A. (2019). Business Data Communications and Networking. Wiley.
  • Microsoft Support. (2023). Create a database in Access. https://support.microsoft.com
  • Aref, S., & Ranjbar, M. (2020). Practical database design and implementation. Journal of Information Technology & Software Engineering, 10(4), 213-225.
  • Maxwell, E. V., & Lewis, A. (2017). Query design principles for relational databases. International Journal of Database Management Systems, 9(2), 35-45.
  • Singh, S., & Khosla, S. (2015). Data validation techniques in database applications. International Journal of Computer Science & Information Technology, 6(1), 69-75.
  • Alavi, M., & Leidner, D. E. (2001). Knowledge management and knowledge management systems: Conceptual foundations and research issues. MIS quarterly, 107-136.
  • Chen, P. P. (1976). The entity-relationship model—toward a unified view of data. ACM Transactions on Database Systems (TODS), 1(1), 9-36.

Related Assignments