Category: ms Access

MS Access – Objects ”; Previous Next MS Access uses “objects” to help the user list and organize information, as well as prepare specially designed reports. When you create a database, Access offers you Tables, Queries, Forms, Reports, Macros, and Modules. Databases in Access are composed of many objects but the following are the major objects − Tables Queries Forms Reports Together, these objects allow you to enter, store, analyze, and compile your data. Here is a summary of the major objects in an Access database; Table Table is an object that is used to define and store data. When you create a new table, Access asks you to define fields which is also known as column headings. Each field must have a unique name, and data type. Tables contain fields or columns that store different kinds of data, such as a name or an address, and records or rows that collect all the information about a particular instance of the subject, such as all the information about a customer or employee etc. You can define a primary key, one or more fields that have a unique value for each record, and one or more indexes on each table to help retrieve your data more quickly. Query An object that provides a custom view of data from one or more tables. Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want. In Access, you can use the graphical query by example facility or you can write Structured Query Language (SQL) statements to create your queries. You can define queries to Select, Update, Insert, or Delete data. You can also define queries that create new tables from data in one or more existing tables. Form Form is an object in a desktop database designed primarily for data input or display or for control of application execution. You use forms to customize the presentation of data that your application extracts from queries or tables. Forms are used for entering, modifying, and viewing records. The reason forms are used so often is that they are an easy way to guide people toward entering data correctly. When you enter information into a form in Access, the data goes exactly where the database designer wants it to go in one or more related tables. Report Report is an object in desktop databases designed for formatting, calculating, printing, and summarizing selected data. You can view a report on your screen before you print it. If forms are for input purposes, then reports are for output. Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels. Reports are useful because they allow you to present components of your database in an easy-to-read format. You can even customize a report”s appearance to make it visually appealing. Access offers you the ability to create a report from any table or query. Other MS Access Objects Let us now take a look at other MS Access objects. Macro This object is a structured definition of one or more actions that you want Access to perform in response to a defined event. An Access Macro is a script for doing some job. For example, to create a button which opens a report, you could use a macro which will fire OpenReport action. You can include simple conditions in macros to specify when one or more actions in the macro should be performed or skipped. You can use macros to open and execute queries, to open tables, or to print or view reports. You can also run other macros or Visual Basic procedures from within a macro. Data macros can be attached directly to table events such as inserting new records, editing existing records, or deleting records. Data macros in web apps can also be stand-alone objects that can be called from other data macros or macro objects. Module Module is an object in desktop databases containing custom procedures that you code using Visual Basic. Modules provide a more discrete flow of actions and allow you to trap errors. Everything that can be done in a macro can also be done in a module, but you don”t get the macro interface that prompts you what is needed for each action. Modules are far more powerful, and are essential if you plan to write code for a multi-user environment, because macros cannot include error handling. Modules can be standalone objects containing functions that can be called from anywhere in your application, or they can be directly associated with a form or a report to respond to events on the associated form or report. Print Page Previous Next Advertisements ”;

MS Access – Create Tables ”; Previous Next When you create a database, you store your data in tables. Because other database objects depend so heavily on tables, you should always start your design of a database by creating all of its tables and then creating any other object. Before you create tables, carefully consider your requirements and determine all the tables that you need. Let us try and create the first table that will store the basic contact information concerning the employees as shown in the following table − 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 Let us now have short text as the data type for all these fields and open a blank database in Access. This is where we left things off. We created the database and then Access automatically opened up this table-one-datasheet view for a table. Let us now go to the Field tab and you will see that it is also automatically created. The ID which is an AutoNumber field acts as our unique identifier and is the primary key for this table. The ID field has already been created and we now want to rename it to suit our conditions. This is an Employee table and this will be the unique identifier for our employees. Click on the Name & Caption option in the Ribbon and you will see the following dialog box. Change the name of this field to EmployeeID to make it more specific to this table. Enter the other optional information if you want and click Ok. We now have our employee ID field with the caption Employee ID. This is automatically set to auto number so we don”t really need to change the data type. Let us now add some more fields by clicking on click to add. Choose Short Text as the field. When you choose short text, Access will then highlight that field name automatically and all you have to do is type the field name. Type FirstName as the field name. Similarly, add all the required fields as shown in the following screenshot. Once all the fields are added, click the Save icon. You will now see the Save As dialog box, where you can enter a table name for the table. Enter the name of your table in the Table Name field. Here the tbl prefix stands for table. Let us click Ok and you will see your table in the navigation pane. Table Design View As we have already created one table using Datasheet View. We will now create another table using the Table Design View. We will be creating the following fields in this table. These tables will store some of the information for various book projects. Field Name Data Type Project ID AutoNumber ProjectName Short Text ManagingEditor Short Text Author Short Text PStatus Short Text Contracts Attachment ProjectStart Date/Time ProjectEnd Date/Time Budget Currency ProjectNotes Long Text Let us now go to the Create tab. In the tables group, click on Table and you can see this looks completely different from the Datasheet View. In this view, you can see the field name and data type side by side. We now need to make ProjectID a primary key for this table, so let us select ProjectID and click on Primary Key option in the ribbon. You can now see a little key icon that will show up next to that field. This shows that the field is part of the table’s primary key. Let us save this table and give this table a name. Click Ok and you can now see what this table looks like in the Datasheet View. Let us click the datasheet view button on the top left corner of the ribbon. If you ever want to make changes to this table or any specific field, you don”t always have to go back to the Design View to change it. You can also change it from the Datasheet View. Let us update the PStatus field as shown in the following screenshot. Click Ok and you will see the changes. Print Page Previous Next Advertisements ”;

MS Access – Relating Data ”; Previous Next In this chapter, we will understand the basics of relating data. Before talking about and creating relationships between different data, let us review why we need it. It all goes back to normalization. Normalization Database normalization, or simply normalization, is the process of organizing columns (attributes) and tables (relations) of a relational database to minimize data redundancy. It is the process of splitting data across multiple tables to improve overall performance, integrity and longevity. Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. Let us now look into the following table which contains data, but the problem is that this data is quite redundant which increases the chances of typo and inconsistent phrasing during data entry. CustID Name Address Cookie Quantity Price Total 1 Ethel Smith 12 Main St, Arlington, VA 22201 S Chocolate Chip 5 $2.00 $10.00 2 Tom Wilber 1234 Oak Dr., Pekin, IL 61555 Choc Chip 3 $2.00 $6.00 3 Ethil Smithy 12 Main St., Arlington, VA 22201 Chocolate Chip 5 $2.00 $10.00 To solve this problem, we need to restructure our data and break it down into multiple tables to eliminate some of those redundancy as shown in the following three tables. Here, we have one table for Customers, the 2nd one is for Orders and the 3rd one is for Cookies. The problem here is that just by splitting the data in multiple tables will not help to tell how data from one table relates to data in another table. To connect data in multiple tables, we have to add foreign keys to the Orders table. Defining Relationships A relationship works by matching data in key columns usually columns with the same name in both the tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined. Let us now look into the three types of relationships − One-to-Many Relationships A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the Customers and Orders tables have a one-to-many relationship: each customer can place many orders, but each order comes from only one customer. Many-to-Many Relationships In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Customers table and the Cookies table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the Orders table. One-to-One Relationships In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both the related columns are primary keys or have unique constraints. This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to − Divide a table into many columns. Isolate part of a table for security reasons. Store data that is short-lived and could be easily deleted by simply deleting the table. Store information that applies only to a subset of the main table. Print Page Previous Next Advertisements ”;

MS Access – Parameter Queries ”; Previous Next The best part about queries is that you can save and run the same query again and again, but when you run the same query again and again by only changing the criteria then you might consider the query to accept parameters. If you frequently want to run variations of a particular query, consider using a parameter query Parameter query retrieves information in an interactive manner prompting the end user to supply criteria before the query is run. You can also specify what type of data a parameter should accept. You can set the data type for any parameter, but it is especially important to set the data type for numeric, currency, or date/time data. When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected. If a parameter is set to accept text data, any input is interpreted as text, and no error message is displayed. Example Let us now take a look at a simple example by creating a parameter query. Let us open your database and select Query Design in the Create table tab. Double-click on the tblProjects and close the Show dialog box. Select the field you want to see as a query result as shown in the following screenshot. In the query design grid, in the Criteria row of the ProjectStart column, type [Enter a project start data]. The string [Enter a project start data] is your parameter prompt. The square brackets indicate that you want the query to ask for input, and the text is Enter a project start data is the parameter prompt displays. Let us now run your query and you will see the following prompt. Let us now enter the following date. Click OK to confirm. As a result, you will see the details of the project which started on 2/1/2007. Let us go to the Design View and run the query again. Enter the date as in the above screenshot and click Ok. You will now see the details of the project which started on 5/1/2008. Print Page Previous Next Advertisements ”;

MS Access – Create Database ”; Previous Next In this chapter, we will be covering the basic process of starting Access and creating a database. This chapter will also explain how to create a desktop database by using a template and how to build a database from scratch. To create a database from a template, we first need to open MS Access and you will see the following screen in which different Access database templates are displayed. To view the all the possible databases, you can scroll down or you can also use the search box. Let us enter project in the search box and press Enter. You will see the database templates related to project management. Select the first template. You will see more information related to this template. After selecting a template related to your requirements, enter a name in the File name field and you can also specify another location for your file if you want. Now, press the Create option. Access will download that database template and open a new blank database as shown in the following screenshot. Now, click the Navigation pane on the left side and you will see all the other objects that come with this database. Click the Projects Navigation and select the Object Type in the menu. You will now see all the objects types — tables, queries, etc. Create Blank Database Sometimes database requirements can be so specific that using and modifying the existing templates requires more work than just creating a database from scratch. In such case, we make use of blank database. Step 1 − Let us now start by opening MS Access. Step 2 − Select Blank desktop database. Enter the name and click the Create button. Step 3 − Access will create a new blank database and will open up the table which is also completely blank. Print Page Previous Next Advertisements ”;

MS Access – RDBMS ”; Previous Next Microsoft Access has the look and feel of other Microsoft Office products as far as its layout and navigational aspects are concerned, but MS Access is a database and, more specifically, a relational database. Before MS Access 2007, the file extension was *.mdb, but in MS Access 2007 the extension has been changed to *.accdb extension. Early versions of Access cannot read accdb extensions but MS Access 2007 and later versions can read and change earlier versions of Access. An Access desktop database (.accdb or .mdb) is a fully functional RDBMS. It provides all the data definition, data manipulation, and data control features that you need to manage large volumes of data. You can use an Access desktop database (.accdb or .mdb) either as a standalone RDBMS on a single workstation or in a shared client/server mode across a network. A desktop database can also act as the data source for data displayed on webpages on your company intranet. When you build an application with an Access desktop database, Access is the RDBMS. Data Definition Let us now understand what Data Definition is − In document or a spreadsheet, you generally have complete freedom to define the contents of the document or each cell in the spreadsheet. In a document, you can include paragraphs of text, a table, a chart, or multiple columns of data displayed with multiple fonts. In spreadsheet, you can have text data at the top to define a column header for printing or display, and you might have various numeric formats within the same column, depending on the function of the row. An RDBMS allows you to define the kind of data you have and how the data should be stored. You can also usually define rules that the RDBMS can use to ensure the integrity of your data. For example, a validation rule might ensure that the user can’t accidentally store alphabetic characters in a field that should contain a number. Data Manipulation Working with data in RDBMS is very different from working with data in a word processing or spreadsheet program. In a word processing document, you can include tabular data and perform a limited set of functions on the data in the document. You can also search for text strings in the original document and, with ActiveX controls, include tables, charts, or pictures from other applications. In a spreadsheet, some cells contain functions that determine the result you want, and in other cells, you enter the data that provides the source information for the functions. An RDBMS provides you many ways to work with your data. For example, You can search a single table for information or request a complex search across several related tables. You can update a single field or many records with a single command. You can write programs that use RDBMS commands to fetch data that you want to display and allow the user to update the data. Access uses the powerful SQL database language to process data in your tables. Using SQL, you can define the set of information that you need to solve a particular problem, including data from perhaps many tables. Data Control Spreadsheets and word processing documents are great for solving single-user problems, but they are difficult to use when more than one person needs to share the data. When you need to share your information with others, RDBMS gives you the flexibility to allow multiple users to read or update your data. An RDBMS that is designed to allow data sharing also provides features to ensure that no two people can change the same data at the same time. The best systems also allow you to group changes (which is also known as transaction) so that either all the changes or none of the changes appear in your data. You might also want to be sure that no one else can view any part of the order until you have entered all of it. Because you can share your Access data with other users, you might need to set some restrictions on what various users are allowed to see or update. Print Page Previous Next Advertisements ”;