MBA _ sem 3 _ 613_ Information System Management

 A record: Contains specific data, like information about a particular employee or a product.  A field: Contains data about one aspect of the table subject, such as first name or e-mail address.  A field value: Each record has a field value. For example, Contoso, Ltd. or [email protected]. Table and Field Properties Tables and fields also have properties that you can set to control their characteristics or behaviour.  Table properties.  Field properties. In an Access database, table properties are attributes of a table that affect the appearance or behaviour of the table as a whole. Table properties are set in the table's property sheet, in Design view. For example, you can set a table's Default View property to specify how the table is displayed by default. A field property applies to a particular field in a table and defines one of the field's characteristics or an aspect of the field's behaviour. You can set some field properties in Datasheet view. You can also set any field property in Design view by using the Field Properties pane. Data Types. A data type is allocated to each sector. The data type of a field shows the type of data it stores, such as vast volumes of text or attached files. Figure 6.16: Data Type An information type is a field property, yet it varies from other field properties as follows:  You set a field's information type in the table plan matrix, not in the Field Properties sheet.  A field's information type figures out what different properties the field has.  You should set a field's information type when you make the field.  You can make another field in Access by entering information in another segment in Datasheet see. At the point when you make a field by entering information in Datasheet see, Access naturally appoints an information type for the field, in view of the worth that you enter. On the off chance that no other information type is suggested 151 CU IDOL SELF LEARNING MATERIAL (SLM)

by your info, Access sets the information type to Text. If necessary, you can change the information type by utilizing the Strip. The accompanying table records a portion of the information types accessible in work area data sets in Access 2013 and later forms. Type of Data Description Size Text or combinations of text and numbers, Up to 255 characters. Short Text including numbers that do not require calculating (e.g. phone numbers). Long Text Lengthy text or combinations of text and Up to 63, 999 characters. Number numbers. Date/Time Numeric data used in mathematical 1, 2, 4, or 8 bytes (16 bytes if set to calculations. Replication ID). Date and time values for the years 100 through 9999. 8 bytes Currency Currency values and numeric data used in 8 bytes mathematical calculations involving data with one to four decimal places. A unique sequential (incremented by 1) number AutoNumber or random number assigned by Microsoft 4 bytes (16 bytes if set to Access whenever a new record is added to a Replication ID). table. Table 6.2: Types of data available in Desktop Database Table Connections Albeit each table stores information about an alternate subject, tables in an Entrance data set ordinarily store information about subjects that are identified with one another. For instance, a data set may contain:  A clients table that rundowns your organization's clients and their addresses.  An items table that rundowns the items that you sell, including costs and pictures for everything.  An orders table that tracks client orders. Since you store information about various subjects in isolated tables, you need some approach to integrate the information so you can without much of a stretch consolidate related information from those different tables. To associate the information put away in various tables, you make connections. A relationship is a coherent association between two tables that indicates fields that the tables share practically speaking. Keys Fields that are important for a table relationship are called keys. A key as a rule comprises of one field, however, may comprise of more than one field. There are two sorts of keys: 152 CU IDOL SELF LEARNING MATERIAL (SLM)

Essential key A table can have just a single essential key. An essential key comprises of at least one fields that interestingly recognize each record that you store in the table. Frequently, there is a one-of-a-kind distinguishing proof number, for example, an ID number, a chronic number, or a code, that fills in as an essential key. For instance, you may have a Clients table where every client has a novel client ID number. The client ID field is the essential key of the Clients table. At the point when an essential key contains more than one field, it is generally made out of previous fields that, taken together, give novel qualities. For instance, you may utilize a mix of last name, first name, and birth date as the essential key for a table about individuals. For more data, see adding or changing a table's essential key. Unfamiliar key A table can likewise have at least one unfamiliar keys. An unfamiliar key contains values that relate to values in the essential key of another table. For instance, you may have an Orders table in which each request has a client ID number that relates to a record in a Clients table. The client ID field is an unfamiliar key of the Orders table. The correspondence of qualities between key fields frames the premise of a table relationship. You utilize a table relationship to consolidate information from related tables. For instance, assume that you have a Clients table and an Orders table. In your Clients table, each record is recognized by the essential key field, ID. To connect each request with a client, you add an unfamiliar key field to the Orders table that compares to the ID field of the Clients table, and afterward make a connection between the two keys. At the point when you add a record to the Orders table, you utilize an incentive for client ID that comes from the Clients table. At whatever point you need to see any data about a request's client, you utilize the relationship to distinguish which information from the Clients table compares to which records in the Orders table. An essential key distinguished by the vital symbol close to the field name. An unfamiliar key — note the shortfall of the key symbol. Try not to add a field on the off chance that you expect that every remarkable element addressed in the table may require more than an incentive for the field. Proceeding with the former model, in the event that you need to begin following requests set by your clients, you don't add a field to the table, on the grounds that every client will have more than one request. All things considered, you make another table to store requests, and afterward make a connection between the two tables. Make A Table and Add Fields A basic data set, for example, a contact list, may utilize just a solitary table. Numerous data sets, notwithstanding, utilize a few tables. At the point when you make another information 153 CU IDOL SELF LEARNING MATERIAL (SLM)

base, you make another document on your PC that goes about as a compartment for the entirety of the articles in your data set, including your tables. You can make a table by making another information base, by embeddings a table into a current data set, or by bringing in or connecting to a table from another information source — , for example, a Microsoft Dominate exercise manual, a Microsoft Word report, a book document, or another data set. At the point when you make another, clear data set, another, unfilled table is naturally embedded for you. You would then be able to enter information in the table to begin characterizing your fields. At the point when you make an information base, you store your information in tables. Since other data set items depend so intensely on tables, you ought to consistently begin your plan of an information base by making the entirety of its tables and afterward making some other article. Before you make tables, cautiously think about your prerequisites and decide every one of the tables that you need. Let's try to make the first table, which will store the employees' basic contact information, as shown in the table below. – Field Name Data Type EmployeelD AutoNumber FirstName Short Text LastName Short Text Address1 Short Text Address2 Short Text City Short Text State Short Text Zip Short Text Phone Short Text Phone Type Short Text Table 6.3: Sample Table Creation Let's set the data type for all of these fields to short text and create a new database in Access. We created the database, and Access opened this table-one-datasheet view for a table automatically. 154 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.17: Table-One-Datasheet View of a Table Let's move on to the Field tab, where you'll notice that it's also created automatically (Figure 6.17). Our unique identifier and primary key for this table is the ID, which is an AutoNumber field. We've already created the ID field, but we'd like to rename it to better suit our needs. This is an Employee table, and this will be our employees' unique identifier. (Figure 6.18). 155 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.18: ID field Click on the Name & Caption option in the Ribbon and you will see the following dialog box. Figure 6.19: Name and Caption Option To make this field more specific to this table, rename it to Employee ID. If you want, you can add more optional information and then click. Ok. 156 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.20: Caption changed to Employee ID With the caption Employee ID, we now have our employee ID field. We don't need to change the data type because it's already set to auto number. (Figure 6.20). Let us now add some more fields by clicking on click to add (Figure 6.21). FIIFI Figure 6.21: Addition of Fields 157 CU IDOL SELF LEARNING MATERIAL (SLM)

Select Short Text as the field type. When you select short text, Access will automatically highlight the field name, and all you have to do now is type it. (Fig 22). Figure 6.22: Short Text As the field name, type FirstName. Similarly, as shown in the screenshot, fill in all of the required fields. (Fig 23). Figure 6.23: Changes to The Field Name 158 After you've completed all of the fields, click the Save icon. Now you'll see the Save As dialogue box, where you can give the table a name. CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.24: Save As Dialog Box In the Table Name field, type the name of your table. The table prefix stands for table in this case. Let's click OK, and your table will appear in the navigation pane. Figure 6.25: Table Field Changes Table Design View We've already used Datasheet View to create one table. We'll now use the Table Design View to create a new table. We will be creating the following fields in this table. Some of the information for various book projects will be stored in these tables. 159 CU IDOL SELF LEARNING MATERIAL (SLM)

Field Name Data Type Project ID AutoNumber ProjectName ManagingEditor Short Text Short Text Author Short Text PStatus Short Text Contracts Attachment ProjectStart Date/Time ProjectEnd Date/Time Budget Currency ProjectNotes Long Text Table 6.4:Table Design View Let us now go to the Create tab. Figure 6.26: Create Tab Click Table in the tables group, and you'll notice that it's not the same as the Datasheet View. The field name and data form are shown side by side in this view. 160 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.27: Datasheet View We now need to make Project ID the primary key for this table, so select Project ID and use the Primary Key choice in the ribbon to do so. Figure 6.28: Datasheet View 161 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.29: Datasheet View You'll see a small key icon appear next to that field now. This indicates that the area is part of the primary key of the table. Allow us to save this table and give it a name. Figure 6.30: Save As Dialog Box When you click Ok, you'll be able to see how this table appears in the Datasheet View. 162 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 6.31: Datasheet View Let's go to the top left corner of the ribbon and pick the datasheet view button. Figure 6.32: Datasheet View 163 CU IDOL SELF LEARNING MATERIAL (SLM)

You don't always have to go back to the Design View to adjust this table or some other area. It's even possible to adjust it from the Datasheet View. Let's make a change to the Status area, as seen in the image below. Figure 6.33: Field Properties Dialog Box When you click OK, the changes will be clear. Figure 6.34: Changed Datasheet View 6.4 SUMMARY  Database Administration, permits an individual to sort out, store and recover information from a PC. Data set Administration can likewise portray, the information stockpiling, activities and security practices of a Data set Head (DBA), for the duration of the existence pattern of the information 164 CU IDOL SELF LEARNING MATERIAL (SLM)

 Data is only realities and measurements put away or free streaming over an organization, by and large it's crude and natural. For instance: When you visit any site, they may store you IP address, that is information, consequently they may add a treat in your program, stamping you that you visited the site, that is information, your name, it's information, your age, it's information.  Data becomes data when it is prepared, transforming it into something significant. Like, in view of the treat information saved money on client's program, if a site can investigate that for the most part men old enough 20-25 visit us more, that is data, gotten from the information gathered.  A Data set is an assortment of related information coordinated such that information can be effortlessly gotten to, oversaw and refreshed. Information base can be programming based or equipment based, with one sole reason, putting away information.  Data is never straightforwardly put away into the data set. Information is put away into tables, made inside the data set. DBMS additionally permits to have connections between tables which makes the information more significant and associated. You can without much of a stretch comprehend what sort of information is put away whereby taking a gander at all the tables made in a data set.  Microsoft Access is an information base administration framework (DBMS) from Microsoft that joins the social Microsoft Fly Data set Motor with a graphical UI and programming improvement devices.  Microsoft Access is a data set administration framework (DBMS) from Microsoft that consolidates the social Microsoft Fly Data set Motor with a graphical UI and programming improvement instruments. ... It can likewise import or connection straightforwardly to information put away in different applications and data sets.  Access calls anything which can have a name as an article. Inside an Entrance work area information base, principal objects are tables, inquiries, structures, reports, macros, information macros, and modules.  But in Access, a work area information base (.accdb) will likewise incorporate every one of the significant articles identified with the put away information, including objects you characterize to robotize the utilization of the information. 6.5 KEYWORDS ACCDB: It stands for Access 2007 Database File. An ACCDB file is a database created with Microsoft Access 2007 or later. It typically contains data organized into tables and fields and may also include custom forms, SQL queries, and other data. ACCDB files replaced .MDB files. DBMS: A database management system (DBMS) is software that stores and retrieves data for users while taking adequate security precautions. It is made up of a series of programmes that manipulate the database. 165 CU IDOL SELF LEARNING MATERIAL (SLM)

SQL: Structured Query Language (SQL) is an acronym for Structured Query Language. It is intended for use in relational database management systems to handle data (RDBMS). It's pronounced S-Q-L or S-Q-L-L-L-L-L-L-L-L- Well, see-Well. SQL is a database language that is used to construct, delete, retrieve, and modify rows in databases. NoSQL: This approach to database architecture, which stands for \"not just SQL,\" offers versatile schemas for data storage and retrieval beyond the conventional table structures found in relational databases. Normalization: It is a method for decomposing tables in order to remove data redundancy (repetition) and undesirable features such as Insertion, Update, and Deletion Anomalies. It's a multi-step procedure for converting data into tabular format and extracting duplicate data from relation tables. 6.6 LEARNING ACTIVITY 1. Download the Microsoft Sample database in.txt format from GitHub and use the sample text data to generate a database and tables. https://github.com/ 2.Create a database Human Resources and table EmployeeDtl with following Details: EmpId, EmpName, EmpDOB, EmpEmailId, EmpAddress(Use appropriate datatypes) ---------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------- 6.7 UNIT END QUESTIONS A. Descriptive Questions Short Questions 166 1. Explain steps to create database in MS access. 2. Explain different elements in Access database. 3. Explain procedure to create tables and records. 4. Explain details of early history and development of databases. 5. Write a short note on Cloud databases. Long Questions 1. Explain elements of MS Access database. 2. Describe database management process. CU IDOL SELF LEARNING MATERIAL (SLM)

3. Explain characteristics of database. 4. Distinguish between data processing and database management. 5. Explain different types of database. B. Multiple Choice Questions 1) __________is a database language used to define data structures in SQL? a. Sybase b. Oracle c. DDL d. MySQL 2) In the context of data warehousing, data map or ‘data about data’ is known as ____________. a. Tuple b. Attribute c. Metadata d. Teradata 3) Which of the following trains are important to select a primary key? a. Unique b. Not Null c. Fully functional dependency d. Indexed e. All of these 4) Which of the following is not a database object? a. Tables b. Queries c. Relationships d. Reports 5) The size of a field with Number data type cannot be a. 2 b. 4 c. 8 d. 24 Answers: 1. (c), 2. (c), 3. (e), 4. (c), 5. (d) 167 CU IDOL SELF LEARNING MATERIAL (SLM)

6.8 REFERENCES Textbooks  Laudon, K. C. & Laudon, J. P. Management Information, Systems. 2006: Managing the Digital Firm. 10th ed. Prentice Hall and Pearson Education.  Davis, Gordon B. and Margrethe H. Olsen, 1985, Management Information Systems, McGraw-Hill Book Company, Singapore.  Scott, George M., Principles of Management Information Systems, 2003, McGraw-Hill Book Company, Singapore.  Harry Katzan, 1984, Management Support Systems, Van Nostrand Reinhold Company. Reference Books  Rahul De,2018, MIS: Managing Information Systems in Business, Government and Society, 2ed,Kindle Edition.  Heinz Weihrich (Author), Mark V Cannice (Author), Harold Koontz(Author),2019,Management: A Global, Innovative and Entrepreneurial Perspective (15th Edition)Kindle Edition.  Seema Acharya R N Prasad,2016, Fundamentals of Business Analytics, 2ed 2nd Edition, Kindle Edition. Websites  https://www.includehelp.com/  https://www.sigc.edu/department/mba/studymet/  https://www.dphu.org/uploads/attachements/books/books_5828_0.pdf 168 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT 7: QUERYING Structure 7.0 Learning Objectives 7.1 Introduction 7.2 Creating Data Entry Forms 7.2.1 User Forms 7.2.2 Formatting Forms 7.3 Queries 7.3.1 Understand Relationships 7.4 Reports 7.4.1 Create Basic Report 7.4.2 Print Report 7.5 Summary 7.6 Keywords 7.7 Learning Activity 7.8 Unit End Questions (Descriptive and MCQs) 7.9 References 7.0 LEARNING OBJECTIVES After studying this unit, student will be able to:  Work on advanced features in MS Access.  Create simple data entry forms in Access.  Explain queries and relationships.  Create basic reports and print reports after completing this unit. 7.1 INTRODUCTION Compelling utilization of data relies upon how information is put away, coordinated and got to in an association. Appropriate conveyance of data not just relies on the capacities of PC 169 CU IDOL SELF LEARNING MATERIAL (SLM)

equipment and programming yet in addition on the association's capacity to oversee information as a significant asset. Subsequent to seeing the different techniques and approaches of framework improvement, presently, let us have in the wake of examining this unit, you will be capable to understanding of how significant the information assets in an association are and how the data framework we created will deal with them. It has been extremely hard for associations to deal with their information adequately. In attempting to do as such, we need to address two exceptionally large difficulties which are sticking out. Executing a data set requires a far-reaching authoritative change in the job of data and data chiefs, the assignment of force at senior levels, the possession and sharing of data, and examples of hierarchical arrangement. A data set administration framework (DBMS) challenges the current force plans in an association and thus frequently produce political opposition. In a customary document climate, every division developed records and projects to satisfy its particular necessities. Presently, with an information base, documents and projects should be incorporated that consider the full association's premium in information. Albeit the association has spent the cash on equipment and programming for an information base climate, it may not receive the rewards it ought to in the event that it is reluctant to make the essential hierarchical changes. An information base is an assortment of information, which is coordinated in a way that takes into consideration simple information recovery and control. While an envelope with a few documents in it very well might be seen as a basic information base, data set experts for the most part necessitate that a data set have a data set administration framework (DBMS). A DBMS is a product apparatus, which stores information in a predefined structure and gives admittance to this information to a client or an application. In particular, a DBMS gives a few or the entirety of the accompanying usefulness: Data Definition: A DBMS should characterize a construction for put away information and give a way to a client to characterize and arrange their information inside that structure. Data Recovery: A DBMS should give a toolset that permits a client to recover information put away in the data set (for example, inquiry instruments). Access Control: The information base overseer ought to have the option to characterize information access for an individual or a gathering. Data Sharing: More than one client ought to have the option to utilize the data set simultaneously without a risk of overwriting each other's information changes. Data Honesty: A DBMS ought to give components to keeping up information trustworthiness through framework disappointments and conflicting, or fragmented, refreshes. 170 CU IDOL SELF LEARNING MATERIAL (SLM)

Customarily, information documents were created and kept up independently for singular applications. Hence, the document preparing framework depended on the piecemeal methodology of information across the association where each practical unit like promoting, account, creation, and so on used to keep up their own arrangement of use projects and information records. Presumably such an association was easy to work and would be advised to neighbourhood control however the information of the association is scattered all through the practical sub-frameworks. An elective way to deal with the record handling framework in the advanced methodology known as the data set methodology. An information base is a coordinated assortment of records and documents which are identified with one another. In an information base framework, a typical pool of information can be shared by various applications as it is information and program autonomous. Accordingly, not at all like a record handling framework, information excess and information irregularity in the data set framework approach are limited. The client is liberated from the nitty gritty and confounded undertaking of staying aware of the actual construction of the information. Relational Operations SQL Structured Query Language, popularly known as SQL is the language that is used in most relational database systems. It is called structured query language because it follows a rigorous set of rules and procedures in answering queries. SQL is also termed as 4GL to distinguish it from other 3GL programming languages like, PASCAL, COBOL or C. SQL is a simple and powerful query language that is capable of answering simple to most complex queries. Any query on a single table can be performed by using only two basic operators, SELECT and PROJECT. Working with your Access Environment If you've used Access 2010 or 2007, you'll be at ease with Access 2013. Backstage view, the Ribbon, and the Quick Access toolbar (where you'll find commands to perform basic tasks in Access) are all still present. The Ribbon Instead of standard menus, Access 2013 employs a tabbed Ribbon scheme. The Ribbon has several tabs, each with a different set of commands. These tabs can help you perform the most popular tasks in Access. 171 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.1: Access 2013 No matter which tab you are on, the Quick Access toolbar, which is located above the Ribbon, allows you to access popular commands. It displays the Save, Undo, and Repeat commands by default. You can customize it by adding additional commands if you like. Figure 7.2: Quick Access Toolbar 7.2 CREATING DATA ENTRY FORMS Creating forms for your database will make data entry much easier. You should build a form in a way that fits for your database and makes sense to you when you develop it. 7.2.1 User Forms To create a Form Access makes it simple to make a structure from any table in your data set. Any structure you make from a table will allow you to see the information that is now in that table and add new information. Whenever you've made a structure, you can change it by adding extra fields and configuration controls like combo boxes.  In the Route sheet, select the table you need to use to make a structure. You don't have to open the table. 172 CU IDOL SELF LEARNING MATERIAL (SLM)

 Select the Make tab, find the Structures gathering, and snap the Structure order. Figure 7.3: Create Tab  Your form will be created and opened in Layout view Figure 7.4: Layout View  On the Easy Access toolbar, press the Save command to save the form. When prompted, give the form a name, then click OK. 173 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.5: Save As Dialog Box Subforms A subform is included in a form formed from a table whose records are connected to another table. A subform is a datasheet form that uses a table-like format to view related documents. For example, the linked customer orders subform included in the Customers form we just generated is displayed. Adding Additional Fields to A Form When you use the Form command on an existing table, the form includes all of the table's fields. However, if you later add more fields to the table, these new fields will not appear in existing types. You can add additional fields to a form in circumstances like this. To Add A Field to A Form  Go to the Design tab, then to the right side of the Ribbon, to the Tools group.  Select Add Existing Fields from the drop-down menu. Figure 7.6: Design Tab  The Field List pane will appear. Double-click the desired field(s). Figure 7.7: Fields List 174 CU IDOL SELF LEARNING MATERIAL (SLM)

 The field will be added Figure 7.8: Fields Tab You may also use the steps above to add fields to a form that is completely empty. Simply click the Blank Form command on the Build tab to start building a form, then follow the steps above to add the fields you like. Figure 7.9: Create Tab Adding Design Controls Fields in your forms are limited by design controls. This gives you more control about how data is inserted into your forms, resulting in a more consistent database. Combo Boxes A combo box is a drop-down list you can use in your form in place of a field. Combo boxes limit the information users can enter by forcing them to select only the options you have specified. Combo boxes are useful for fields that have a limited number of possible valid responses. For instance, you might use a combo box to make sure people only enter a valid U.S. state while entering an address, or that they only choose products that already exist in your database while placing an order. To Create A Combo Box  Select the Design tab in Form Layout view, then the Controls group.  Select the Combo Box command, which appears as a drop-down menu. 175 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.10: Combo Box Command  Choose the combo box's desired spot. The position where your combo box will be produced will be indicated by a line. We'll put it between the City field and the Add to Mailing List? field in our example. a field. Figure 7.11: Combo Box Command The dialogue box for the Combo Box Wizard will appear. Then click Next after selecting I'll type in the values I want. 176 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.12: Combo Box Wizard  Type the options you'd like to see in your drop-down menu. Each option should be assigned to its own row. We’ll create a combo box for the Add to Mailing List? field in our form in this example, so we'll enter all of the valid responses for this field. From our finished combo box, users will be able to choose one of three options: Figure 7.13: Combo Box Wizard 177 CU IDOL SELF LEARNING MATERIAL (SLM)

 If necessary, resize the column so all of your text is visible. Once you are satisfied with your list, click Next. Figure 7.14: Combo Box Wizard  Select Store that value in this field, then click the drop-down arrow and select the field where you want selections from your combo box to be recorded. After making your selection, click Next. Figure 7.15: Combo Box Wizard 178 CU IDOL SELF LEARNING MATERIAL (SLM)

 Enter the label—or name—that will appear next to your combo box. Generally, it's a good idea to use the name of the field you chose in the previous step. Figure 7.16: Combo Box Wizard  Finish by clicking the Finish button. On the form, your combo box will appear. If you're using a combo box to replace an existing field, the first field should be removed. You'll note that we now have two fields with the same name in our example. We don't need any of these fields because they send data to the same place. We'll get rid of the one that doesn't have a combo package. Figure 7.17: Combo Box Result 179 CU IDOL SELF LEARNING MATERIAL (SLM)

 Switch to Form view to test your combo box. Simply click the drop-down arrow and verify that the list contains the correct choices. The combo box can now be used to enter data. Figure 7.18: Form View Customizing Form Settings with the Property Sheet The Property Sheet is a sheet containing nitty gritty data on your structure and every one of its parts. From the Property Sheet, you can make changes to all aspects of your structure, both as far as capacity and appearance. The most ideal approach to acclimate yourself with the Property Sheet is to open it and select different choices. At the point when you select an alternative, Access will show a short depiction of the choice on the base left line of the program window. Figure 7.19: Property Sheet Modifying Form Settings There are far too many options in the Property Sheet to discuss them all in detail. We'll review two useful ones here: hiding fields and setting fields with dates to automatically fill in 180 CU IDOL SELF LEARNING MATERIAL (SLM)

the current date. Practicing these procedures should also give you a sense of how to work with other Property Sheet settings. To Hide A Field Select the Design tab in either Layout or Design view, then the Tools category. Select the Property Sheet option. Figure 7.20: Design Tab The Property Sheet will appear in the right-hand window. Pick the field you want to hide on the form. We'll hide the Customer ID field in our example because we don't want any of our users to try to edit it. Figure 7.21: Property Sheet Tab • Select the All tab in the Property Sheet, then the Visible choice in the fifth row. • Pick No from the drop-down arrow in the right-hand column. 181 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.22: Property Sheet Change to Shape view to check that the field is covered up.  To set a field to autofill with the current date:  In one or the other Format or Configuration see, select the Plan tab, at that point find the Apparatuses gathering. Snap the Property Sheet order.  The Property Sheet will show up in a sheet on the right. On the structure, select the field you need to consequently fill in the current date. This should be a field with the date information type. For our model, we'll select the Pickup Date field on our Orders structure. Figure 7.23 : Property Sheet 182 CU IDOL SELF LEARNING MATERIAL (SLM)

 In the Property Sheet, click the Information tab, at that point select the Default Worth field in the fourth column. Snap the Articulation Manufacturer button that shows up in the segment to one side. Figure 7.24 : Property Sheet  A dialogue box called Expression Builder will appear. Select Common Expressions from the Expression Elements list.  Double-click Current Date in the Expression Categories tab. Figure 7.25 : Expression Builder 183 CU IDOL SELF LEARNING MATERIAL (SLM)

 The Current Date term will be added. Select OK. Figure 7.26 : Expression Builder To make sure the expression functions, switch to Form view. The date field you changed will automatically fill in the current date when you build a new record with that type. Figure 7.27 : Form View 184 CU IDOL SELF LEARNING MATERIAL (SLM)

7.2.2. Formatting Forms In the wake of making a structure, you should alter its appearance. Designing your structures can help make your data set look predictable and proficient. Some designing changes can even make your structures simpler to utilize. With the organizing instruments in Access, you can alter your structures to look precisely the manner in which you need. Access offers a few choices that let you make your structures look precisely the manner in which you need. While a portion of these choices—like order catches—are one of a kind to structures, others might be natural to you. Order catches  On the off chance that you need to make a path for clients of your structure to rapidly perform explicit activities and errands, consider adding order catches. At the point when you make an order button, you determine an activity for it to complete when clicked. By including orders for basic errands directly in your structure, you're making the structure simpler to utilize.  Access offers a wide range of kinds of order catches, yet they can be separated into a couple of principle classes:  Record Route order catches, which permit clients to move among the records in your information base  Record Activity order catches, which let clients do things like save and print a record  Form Activity order catches, which permit clients to immediately open or close a structure, print the current structure, and perform different activities  Report Activity order catches, which offer clients a snappy method to do things like review or mail a report from the current record  To add an order, catch to a structure:  In Structure Format see, select the Plan tab, at that point find the Controls gathering.  Click the Catch order. Figure 7.28 : Design Tab 185 CU IDOL SELF LEARNING MATERIAL (SLM)

 Choose the ideal area for the order button, at that point click the mouse. Figure 7.29 : Location for Command Button  The Order Catch Wizard will show up. In the Classifications sheet, select the classification of catch you need to add. We need to figure out how to move all the more rapidly to explicit records, so we'll pick the Record Route classification.  The list in the Activities sheet will refresh to mirror your picked classification. Select the activity you need the catch to perform, at that point click Straightaway. In our model, we'll pick Discover Record. Figure 7.30 : Command Button Wizard  You now have the option of using text or an image in your button. On the left, there is a live preview of your button.  Pick Text from the drop-down menu, then type the desired word or phrase into the text box. 186 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.31 : Command Button Wizard  Pick the Photo choice to include a picture. You have the option of having the default picture for that command button or changing it. To pick another command button icon, press Display All Images. To choose a photo from your phone, use the Browse button. Figure 7.32 : Command Button Wizard  When you are satisfied with the appearance of your command button, click Next. 187 CU IDOL SELF LEARNING MATERIAL (SLM)

 Type a name for the button. This name won't appear on the button but knowing the name will help you quickly identify the button if you ever want to modify it with the Property Sheet. After adding the button name, click Finish. Figure 7.33 :Command Button Wizard  Switch to Form view to test the new button. Our Search button opens the Find and Replace dialog box. Figure 7.34 : Form View 188 CU IDOL SELF LEARNING MATERIAL (SLM)

Modifying Form Layout When you build a form in Access, the fields are neatly stacked on top of each other, all the same width. Although this layout is usable, it may not be the best fit for your data. Since the data stored there doesn't take up much space, most of the fields in the form below are almost completely empty. Figure 7.35 : Form Layout Making the fields and command buttons smaller and even putting some of them side by side will help the form suit the data better. You won't be able to place two fields next to each other or resize one field or button without resizing both of them if you use the default layout. Making the fields and command buttons smaller and even putting some of them side by side will help the form suit the data better. You won't be able to place two fields next to each other or resize one field or button without resizing both of them if you use the default layout. This is due to the fact that Access arranges type components in rows and columns. You're actually resizing the column that holds the field when you resize it. 189 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.36 : Form Layout To resize and revamp our fields the manner in which we need, we'll need to change the structure design. For example, in light of the fact that the default format for our structure contains just two sections—one for the field names and another for the fields—we would need to make another segment to put two fields next to each other. We can do this utilizing the order on the Mastermind tab, which contains the entirety of the apparatuses we'll have to redo a structure's design. In the event that you've at any point constructed and adjusted tables in Microsoft Word, you definitely realize how to utilize a large portion of these apparatuses. Figure 7.37 : Arrange Tab To resize form components: i. Switch ahead to Layout view. 190 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.38 : Layout View ii. Select the field or button you want to resize, then hover your mouse over the edge. Your cursor will become a double-sided arrow. iii. Click and drag the mouse to resize the selected object. Figure 7.39 : Resize Field  The field or button will be resized, as well as every other item aligned with it. 191 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.40 : Resized Field 7.3 QUERIES The genuine force of a social data set lies in its capacity to rapidly recover and dissect your information by running a question. Questions permit you to pull data from at least one tables dependent on a bunch of search conditions you characterize. In this exercise, you will figure out how to make a straightforward one-table question. Inquiries are a method of looking for and incorporating information from at least one tables. Running an inquiry resembles posing an itemized inquiry of your data set. At the point when you fabricate a question in Access, you are characterizing explicit hunt conditions to discover precisely the information you need. Inquiries are undeniably more remarkable than the basic hunts or channels you may use to discover information inside a table. This is on the grounds that questions can draw their data from numerous tables. For instance, while you could utilize a hunt in the clients table to discover the name of one client at your business or a channel on the orders table to see just requests set inside the previous week, neither would allow you to see the two clients and orders immediately. Be that as it may, you could without much of a stretch run an inquiry to discover the name and telephone number of each client who's made a buy inside the previous week. An all-around planned inquiry can give data you probably won't have the option to discover just by analysing the information in your tables. At the point when you run an inquiry, the outcomes are introduced to you in a table, however when you plan one you utilize an alternate view. This is called Inquiry Configuration view, and it allows you to perceive how your question is assembled. 192 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.41 : Query Design View One-table Queries We should comprehend with the question building measure by building the easiest inquiry conceivable: a one-table inquiry. We will run an inquiry on the Clients table of our pastry kitchen information base. Suppose our bread shop is having an extraordinary occasion, and we need to welcome our clients who live close by on the grounds that they are the well on the way to come. This implies we need to see a rundown of all clients who live nearby, and just those clients. In the event that you think this sound similar to applying a channel, you're correct. A one- table question is in reality a high-level channel applied to a table.  To make a straightforward one-table inquiry:  Select the Make tab on the Strip, and find the Inquiries gathering.  Click the Inquiry Configuration order. 193 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.42 : Query Design Command  Access will change to the Query Design mode. Pick the table you want to run a question on in the Show Table dialogue box that appears. We'll pick the Customers table because we're running a question about our customers. Figure 7.43 : Customers Table  Click Add, at that point click Close.  The chose table will show up as a little window in the Item Relationship sheet. In the table window, double tap the field names you need to remember for your question. They will be added to the plan lattice in the base piece of the screen.  In our model, we need to mail solicitations to clients who live in a specific territory, so we'll incorporate the Main Name, Last Name, Road Address, City, and Postal district fields. We aren't anticipating calling or messaging our clients, so we don't need to incorporate the Telephone Number or Email fields. 194 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.44 : Query 1  Set the hunt measures by tapping the cell in the Rules: line of each field you need to channel. Composing measures into more than one field in the Rules: column will set your inquiry to incorporate just outcomes that meet all standards. In the event that you need to set different models yet needn't bother with the records appeared in your outcomes to meet every one of them, type the first measures in quite a while: line and extra standards in the or: column and the lines underneath it. For this one-table inquiry, we'll utilize straightforward pursuit measures. We need to discover our clients who live in the city of Raleigh, so in our City field we'll type \"Raleigh\". Composing Raleigh in quotes will recover all records with a definite counterpart for Raleigh in the City field. A few clients who live in suburbia live genuinely nearby, and we'd prefer to welcome them also. We'll add their postal district, 27513, as another measure. Since we need to discover clients who either live in Raleigh or in the 27513 postal districts, we'll type \"27513\" into the or: column of the Postal division field. 195 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 7.45 : Field Changes  After you have set your criteria, run the query by clicking the Run command on the Design tab. Figure 7.46 : Run Command  The query results will be displayed in the query's Datasheet view, which looks like a table. If you want, save your query by clicking the Save command in the Quick Access toolbar. When prompted to name it, type the desired name, then click OK. Figure 7.47 : Save As dialog box 196 CU IDOL SELF LEARNING MATERIAL (SLM)

7.3.1 Understand Relationships One of the tremendous benefits of a social data set is that, when you have your information held in unmistakably characterized, minimal tables, you can interface or relate the information held in various tables. There are three sorts of connections between the information you are probably going to experience at this stage in the plan: balanced, one-to- many, and many-to-many. To have the option to distinguish these connections, you need to analyse the information and have a comprehension of what business rules apply to the information and tables. In case you don't know, it very well may be useful to meet with somebody who has an intensive information on the information. When investigating table connections, you need to take a gander at the relationship from the two sides When making table connections, you generally work with two tables all at once. One table is known as the essential or parent table and the other is the related or youngster table. Balanced Relationship A balanced (1:1) relationship implies that each record in Table An identifies with one, and just one, record in Table B, and each record in Table B identifies with one, and just one, record in Table A. Take a gander at the accompanying illustration of tables from an organization's Workers information base: Table 7.1 Employee Database Table 197 CU IDOL SELF LEARNING MATERIAL (SLM)

Table 7.2 : Employee Database Table Above, tables with a coordinated relationship from a data set of data about workers. Each record in the Individual table is around one worker. That record identifies with one, and just one, record in the Finance table. Each record in the Finance table identifies with one, and just one, record in the Individual table. (This is the thing that taking a gander at it from the two bearings implies.). In a coordinated relationship, either table can be viewed as the essential or parent table.  One-To-Many Relationship A one-to-many (1:N) relationship implies a record in Table A can identify with nothing, one, or numerous records in Table B. Numerous records in Table B can identify with one record in Table A. The potential relationship is the thing that's significant; for a solitary record in Table A, there may be no connected records in Table B, or there may be just one related record, yet there could be many. Take a gander at the accompanying tables about an organization's Clients and Orders. 198 CU IDOL SELF LEARNING MATERIAL (SLM)

Table 7.3 : Customers and Orders Table Table 7.4 : Customer Orders Table Above, tables with information about clients and orders that have a one-to-numerous relationships. The Clients table holds an interesting record for every client. Every client can (and, we trust, does) put in numerous requests. Numerous records in the Orders table can identify with just one record in the Clients table. This is a one-to-numerous relationship (1:N) between the Clients table and the Orders table. In a one-to- numerous relationships, the table on the one side of the relationship is the essential table and the table on the many side is the connected table. A one-to-numerous relationships is the most well-known relationship found between tables in a social data set. Coming up next is an illustration of a structure utilized in a data set to show the information from two tables with a one-to-numerous relationships. 199 CU IDOL SELF LEARNING MATERIAL (SLM)

 Assessing Your Tables Given the kinds of connections that exist between tables, you can figure out how to assess the tables in your information distribution centre. As these connections shape how multi-table determined segments are characterized, it is significant that you see how to recognize table connections and what 'side' - one or many - the table has a place with. There are two strategies you can use to assess the connections of a given pair of tables inside your Information Stockroom. The primary technique utilizes an applied structure that thinks about how the table's elements collaborate with one another. The subsequent strategy uses the table's pattern.  Utilizing the Applied Structure This technique employments of a calculated system to portray how substances in the two tables are equipped for collaborating with one another. Comprehend that this structure surveys what is conceivable, given the relationship. For instance, when contemplating clients and orders consider all that is conceivable in the relationship. An enlisted client may put in no requests, just one request, or numerous orders inside their lifetime. On the off chance that you have quite recently dispatched your business and no orders have been set at this point, it is as yet conceivable that a given client can put in numerous requests in the course of their life and the tables are worked to oblige this.  To utilize This Technique Identify the element being depicted in each table. Clue: it's generally a thing. For instance, the tables clients and orders are expressly depicting clients and orders. Identify the verb(s) that depicts how these elements interface. For instance, when contrasting clients with orders, clients \"place\" orders. Going the other course, orders \"have a place\" to clients. This kind of structure can be applied to any blending of tables in your Information Stockroom, permitting you to effectively recognize the sort of relationship just as which table is a one side and which table is a many side. Whenever you have recognized the wording that depicts how the two tables interface, outline the collaboration in the two ways by thinking about how one given example of the principal element identifies with the second. Here are a few instances of every relationship:  Coordinated 200 CU IDOL SELF LEARNING MATERIAL (SLM)