معرفی مایکروسافت اکسس با استفاده از تکنیک‌های برنامه‌نویسی ماکرو: مقدمه‌ای بر توسعه پایگاه داده دسکتاپ به‌وسیله مثال

Learn Microsoft Access by building a powerful database application from start to finish. Microsoft Access ships with every version of Office, from Office 2019 to Office 365 Home and Personal editions. Most people understand the value of having a reliable contact database, but few realize that Access can be an incredibly valuable data tool and an excellent gateway for learning database development. Introducing Microsoft Access Using Macro Programming Techniques approaches database development from a practical and experiential standpoint. You will learn important data concepts as you journey through each step of creating a database using Access. The example you will build takes advantage of a massive amount of data from an external source of nutritional data (USDA). You will leverage this freely available repository of information in multiple ways, putting Access to the test in creating powerful business solutions that you can then apply to your own data sets. The tables and records in this database will be used to demonstrate key relational principles in Access, including how to use the relationship window to understand the relationships between tables and how to create different objects such as queries, forms, reports, and macros. Using this approach, you will learn how desktop database development can be a powerful solution to meet your business needs. What You Will Learn Discover the relational database and how it is different from other databases Create database tables and establish relationships between them to create a solid relational database system Understand the concept and importance of referential integrity (RI) in data and databases Use different types of Access queries to extract the information you need from the database Show database information in individual, customized windows using Access Forms Present insightful information about the database using Access Reports Automate your database solutions with macros Who This Book Is For Anyone who wants to learn how to build a database using Microsoft Access to create customized solutions. It is also useful for those working in IT managing large contact data sets (healthcare, retail, etc.) who need to learn the basics in order to create a professional database solution. Readers should have access to some version of Microsoft Access in order to perform the exercises in this book. Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: A Primer on Databases Types of File Access Access Tables vs. Excel Spreadsheets Files How to Build a Database Relational Databases Referential Integrity Tips to Build a Database from Scratch Creating a Database for Food Intake Control Relating the Tables Microsoft Access Databases Tables Fields Field Properties Field Indexes Multiple Fields Index Queries Forms and Reports Macros Modules Naming Conventions The Microsoft Access Interface The Database Window The Relationship Window Opening, Closing, and Displaying Database Objects Summary Chapter 2: Creating and Using Database Tables The Nutritional Database Structure Creating the Nutritional Database Using the Datasheet View Using the Table Tools Fields Tab Adding Fields with the Datasheet View Adding a Field Using the Fields Tab Adding a Field Using the Click to Add Option Using Design View The AutoNumber Data Type The Short Text Data Type Adding Fields with Design View Using an Input Mask for a Phone Number Understanding Input Mask Syntax Creating the Email Field Defining the Format Property for a Short Text Field Using Format on a Short Text Field Saving the Persons Table Inserting Persons Records Creating the PersonsMeals Table Using a Lookup List for the MealName Field Using the Lookup Data Type Using the Lookup Tab Using the Date/Time Data Type Defining a Default Date/Time Value Defining the Format Property for a Date/Time Field Using an Input Mask for a Date/Time Field Inserting PersonsMeals Records Relating the Persons and PersonsMeals Tables Deleting All the Table Records Referential Integrity Between Persons and PersonsMeals Adding Tables to the Relationships Window Creating a Table Relationship Inserting Related Records Updating Table Values Creating the PersonsMealsFoods Table Relating PersonsMeals and PersonsMealsFoods Inserting Related Records for Food Items Cascading Delete Records Summary Chapter 3: Creating Queries The sr28.accdb Nutritional Database The Relationships Window: A Source for Data Flow Select Queries Creating Queries with the Query Wizard Using the Query Design View Field Name Inserting a New Query Column Using the Expression Builder Using Sort to Sort Records Using Criteria to Filter Records Finding Records That Fit into a Range Finding Records by Performing a Substring Search Using Dynamic Criteria Set Dynamic Criteria Data Type Creating Queries Using Design View Using the Query SQL View Adding Tables to the Query Design Grid Returning All Possible Records from a Query Setting Query Properties Selecting Top Values Selecting Unique Values Total Queries Grouping Records Statistics Summarizing Records Crosstab Queries Setting the Crosstab Column’s Heading Field Order Creating a Column Headings List Action Queries Create Table Queries Delete Queries Append Queries Update Queries Special Queries Special Queries from the Query Wizard Find Duplicate Records Union Queries Summary Chapter 4: Using Forms Creating a Form Using the Form Wizard to Create Forms Creating Simple Forms with the Form Wizard Creating Simple Forms with the More Forms Command Creating a Form/Subform with the Form Wizard Creating a Form with a One-Level Subform Creating a Form with a Two-Level Subform Form view Properties Using Form Design View The Form Design Window Selecting the Form and Its Sections Setting the Form’s Record Source Adding Fields to the Form Detail Section Using Layout View to Set Form Properties Setting Form Data Properties Setting Form Format Properties Setting Form Section Properties Using Form Controls Bound Controls Unbound Controls The Label-Control Association Break and Rebuild a Label-Control Association Naming Conventions Rules for Microsoft Access Controls Common Control Properties Text Box Control Enabled and Locked Properties Specific Text Box Properties Show Date Picker and Input Mask Properties Can Grow and Can Shrink Properties Text Format and Enter Key Behavior Properties Input Mask Property for Password The LEN() Function Control Source for Expressions and Aggregate Functions Combo Box and List Box Controls Using the Combo Box Wizard with a List of Values Creating a One-Column List of Values Combo Box Creating a Two-Column List of Values Combo Box Combo Box or List Box with Hidden Bound Column Using the Combo Box Wizard with a List of Records Creating a Two-Column List of Records Combo Box The Check Box and Option Group Controls Option Group, Toggle Button, and Option Button Controls SubForm/SubReport Control Manually Creating a Form/Subform Interface Using Aggregate Functions in Forms and Subforms Chart Control and Chart Tool Creating a Pie Chart with the Chart Control The IIF() Function Customizing the Pie Chart Creating a Column Chart with the Chart Control The CHOOSE() FUNCTION Customizing the Column Chart Creating a Modern Pie Chart with the Insert Chart Tool Customizing the Modern Pie Chart Creating a Column Clustered Chart with the Insert Chart Tool A Database in a Form Summary Chapter 5: Using Macros The Macro Window Interface Using Macro Actions Referring to Forms and Controls in Action Arguments The Screen Object Dealing with Macro Code Runtime Errors The OnError Action The MacroError Object The MessageBox Action The IF Action Showing the Last Runtime Error Creating an Error Trap Tips for Creating Good Macro Code Microsoft Access Form and Control Events Understanding Form Events Sequence Form Window Events Form Record Events Using Microsoft Access Domain Aggregate Functions The FdGrp_CD Default Value Property Inserting a New Record on the frmFD_GROUP_RecordEvents Form A Note About the New Record Creating a Macro with the Aid of a Wizard Using a Combo Box Control to Find a Form Record The SearchForRecord Action Using the Command Button Control to Execute Actions The CloseWindow Action The Command Button Control Inserting Other Command Buttons Checking the cmdNew and cmdSave Macro Code The GoToRecord Action Checking the cmdDelete Macro Code Critics to the Delete Record Wizard Code Changing Command Button Appearance Creating Your Own Form and Control Events Using the Form On Timer Event The Requery Action Creating a Floating Digital Clock Creating a Splash Screen Form Synchronizing Interface Elements Synchronizing a Searching Combo Box Using Requery to Update a Combo Box List Using the Set Property to Synchronize the Search Combo Box The SetProperty Action The SetValue Action The On Current Event Synchronizing a List Box Control Showing How Many Items a List Box Has Using a Form to Edit Related Records The frmWeightFullName1 Form Inserting Related Records on a Form Calculating the Next Sequence Number for a Common Measure Using Temporary Variables The SetLocalVar Action The SetTempVar Action The RemoveTempVar and RemoveTempVars Actions Global Variable in Action Synchronizing Independent Forms Updating frmPopUpNutrients Information The SelectObject Action The Base Form’s On Current Event The frmPopUpNutrients Window Recalculating Nutrient Values The FORMAT() Function Formatting lstNutrients List Box Formatting the cboAmount Combo Box Selecting an Item on a List Box Control Opening a Form with a Record Selected Summary Chapter 6: Using Reports The Report Object Structure Setting a Report Section’s Width and Height Maximum Allowed Pages on a Section Report and Section Properties Report Page Properties Page Size and Orientation Margin Size Columns Properties Adding Date, Time, Page, and Credit Creating a Report Template Creating Simple Reports Using a Table on the Report’s Record Source Property Report View Properties Using Report View Using Print Preview Mode Sorting Report Records Using a Query Sort Order Grouping and Sorting Records Group and Sort Options Keep Together, Repeat Section, and Force New Page Properties Creating a Multicolumn Report Grouping Options Adding Group Totals Avoiding Group Totals Delays Using Group Sorting Options Numbering Printed Records with the Running Sum Property Using Subreports Creating a Report from an Existing Form Importing Database Objects Converting a Form into a Report Inserting a Subreport on a Main Report Setting the Subreport’s Height Property Printing Columnar Subreports Microsoft Access Report Events Using Macros to Set the Report Records Scope The OpenReport Action Using a Print Form Using Temporary Global Variables Using Full Name Syntax A Note About frmPrintReports Using a Dedicated Form The CancelEvent Action The Echo Action The DisplayHourGlassPointer Action Using a Dedicated Modal Form The Dedicated Form frmDialogSelectCategory Using a Dedicated Pop-Up Form The Dedicated Form frmPopUpSelectCategory Printing Labels Report Section Runtime Properties MoveLayout, NextRecord, and PrintSection Properties Skip the First n Printing Positions Create Copies of Each Printed Label Begin Printing on a Specific Record Putting It All Together Summary Index Learn Microsoft Access by building a powerful database application from start to finish. Microsoft Access ships with every version of Office, from Office 2019 to Office 365 Home and Personal editions. Most people understand the value of having a reliable contact database, but few realize that Access can be an incredibly valuable data tool and an excellent gateway for learning database development. Introducing Microsoft Access Using Macro Programming Techniques approaches database development from a practical and experiential standpoint. You will learn important data concepts as you journey through each step of creating a database using Access. The example you will build takes advantage of a massive amount of data from an external source of nutritional data (USDA). You will leverage this freely available repository of information in multiple ways, putting Access to the test in creating powerful business solutions that you can then apply to your own data sets. The tables and records in this database will be used to demonstrate key relational principles in Access, including how to use the relationship window to understand the relationships between tables and how to create different objects such as queries, forms, reports, and macros. Using this approach, you will learn how desktop database development can be a powerful solution to meet your business needs. What You Will Learn Discover the relational database and how it is different from other databases Create database tables and establish relationships between them to create a solid relational database system Understand the concept and importance of referential integrity (RI) in data and databases Use different types of Access queries to extract the information you need from the database Show database information in individual, customized windows using Access Forms Present insightful information about the database using Access Reports Automate your database solutions with macros This book is for anyone who wants to learn how to build a database using Microsoft Access to create customized solutions. It is also useful for those working in IT managing large contact data sets (healthcare, retail, etc.) who need to learn the basics in order to create a professional database solution. Readers should have access to some version of Microsoft Access in order to perform the exercises in this book. Flavio Morgado is a food engineer with an MSc. degree in food science and technology, a VBA professional developer, and a professor of epidemiology, statistics, and medical informatics at UNIFESO, a health sciences university in Rio de Janeiro, Brazil. Flavio has written more than 30 books, including Programming Excel with VBA (Apress) and Microsoft Word Secrets (Apress), and has translated many technical books. He also loves animals and the exquisite nature of the surrounding rainforest, and when he is not teaching, writing, or developing, he can be found running or riding his mountain bike through the Teresopolis Mountains, followed by his eight dogs, or spending time on the stunningly beautiful beaches of Cabo Frio in Rio de Janeiro