Category: ms Access

MS Access – Query Data ”; Previous Next A query is a request for data results, and for action on data. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data. As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table. With a query you can apply a filter to the table”s data, so that you only get the information that you want. Queries that you use to retrieve data from a table or to make calculations are called select queries. Queries that add, change, or delete data are called action queries. You can also use a query to supply data for a form or report. In a well-designed database, the data that you want to present by using a form or report is often located in several different tables. The tricky part of queries is that you must understand how to construct one before you can actually use them. Create Select Query If you want to review data from only certain fields in a table, or review data from multiple tables simultaneously or maybe just see the databased on certain criteria, you can use the Select query. Let us now look into a simple example in which we will create a simple query which will retrieve information from tblEmployees table. Open the database and click on the Create tab. Click Query Design. In the Tables tab, on the Show Table dialog, double-click the tblEmployees table and then Close the dialog box. In the tblEmployees table, double-click all those fields which you want to see as result of the query. Add these fields to the query design grid as shown in the following screenshot. Now click Run on the Design tab, then click Run. The query runs, and displays only data in those field which is specified in the query. Print Page Previous Next Advertisements ”;

MS Access – Action Queries ”; Previous Next In MS Access and other DBMS systems, queries can do a lot more than just displaying data, but they can actually perform various actions on the data in your database. Action queries are queries that can add, change, or delete multiple records at one time. The added benefit is that you can preview the query results in Access before you run it. Microsoft Access provides 4 different types of Action Queries − Append Update Delete Make-table An action query cannot be undone. You should consider making a backup of any tables that you will update by using an update query. Create an Append Query You can use an Append Query to retrieve data from one or more tables and add that data to another table. Let us create a new table in which we will add data from the tblEmployees table. This will be temporary table for demo purpose. Let us call it TempEmployees and this contains the fields as shown in the following screenshot. In the Tables tab, on the Show Table dialog box, double-click on the tblEmployees table and then close the dialog box. Double-click on the field you want to be displayed. Let us run your query to display the data first. Now let us go back to Query design and select the Append button. In the Query Type, select the Append option button. This will display the following dialog box. Select the table name from the drop-down list and click Ok. In the Query grid, you can see that in the Append To row all the field are selected by default except Address1. This because that Address1 field is not available in the TempEmployee table. So, we need to select the field from the drop-down list. Let us look into the Address field. Let us now run your query and you will see the following confirmation message. Click Yes to confirm your action. When you open the TempEmployee table, you will see all the data is added from the tblEmployees to the TempEmployee table. Print Page Previous Next Advertisements ”;

MS Access – Navigation Form ”; Previous Next Access includes a Navigation Control that makes it easy to switch between various forms and reports in your database. A navigation form is simply a form that contains a Navigation Control. Navigation forms are a great addition to any desktop database. Microsoft Access offers several features for controlling how users navigate the database. We have already used navigation pane to navigate through all of the Access objects we create, such as forms, queries, tables, etc. If you want to create your own navigational structure and make it easier for the users to find the specific objects that they really need, you can build navigation form, which is a form that uses a navigation control so users can use or view forms and reports right from within that main navigation form. Example Let us now take a simple example in which we will create the navigation form. For this, go to the Create tab; in the Forms group, you will see this navigation drop-down menu. In this menu, you will see different layouts for how to arrange your forms and reports that you would like to embed on this navigation form. There is one with horizontal tabs, one with vertical tabs — where all the tabs are aligned to the left, a vertical tabs layout where all the tabs are aligned to the right. There is a horizontal tabs layout that has two levels to it, so if you have a lot of objects that you want to display across the top, you can make use of this. You can have one where you have both horizontal tabs and vertical tabs, either aligned to the left or to the right. In the following example, we will be using Horizontal Tabs and Vertical Tabs. To create that layout or that navigation form, simply click on it, and Access will create an unbound form, with a navigation control on it. To add objects to this navigation form, the easiest way to do is through your layout view, by simply dragging and dropping objects to where you want them to appear. Let us now drag frmProjects form from the navigation pane and drop it on the [Add New] on the top. Similarly, drag frmAuthers form from the navigation pane and drop it to the left of the Add New Button. Let us now add other forms related to Projects such as frmSubTasks, frmCurrentProjects etc. Let us now add additional tabs across the top. We will first add the frmEmployees form. Now, you will see that your other project buttons have disappeared from the left and that is because they are attached to Project tab. All the buttons you view on the left are linked to whatever you have selected up the top. Now with the Employee tab selected, let us drag employees-related information to the left. Now we have project information on one tab, employee information on the other. Similarly, you can add more tabs as per your requirements. As you can see that the name of the tabs is not appropriate, so let us start renaming some of these tabs to make them more user-friendly. The easiest way is to double-click on any tab or any button on the left and rename it as shown in the following screenshot. Print Page Previous Next Advertisements ”;

MS Access – Combo Box ”; Previous Next When you enter data in any form, it can be quicker and easier to select a value from a list than to remember a value to type. A list of choices also helps ensure that the value entered in a field is appropriate. A list control can connect to existing data, or it can display fixed values that you enter when you create the control. In this chapter, we will cover how to create a combo box in Access. Combo Box A combo box is an object or control which contains a drop-down list of values that the user can select from. The combo box control provides a more compact way to present a list of choices. The list is hidden until you click the drop-down arrow. A combo box also gives you the ability to enter a value that is not in the list. In this way, the combo box control combines the features of a text box and a list box. Example Let us now take a simple example of creating a combo box. We have created a form for an employee as shown in the following screenshot. We now want to create a combo box for Phone type because we know that phone type should be either Home, Cell or Work. This information should be available in the dropdown list and the user need not type this information. Let us now go to the Design View for this form. Select the Phone Type field and press delete. Once the Phone Type field is deleted, go to the Design tab. Let us now select Use Control Wizards option from the Controls menu and then Select the Combo Box Control from the menu as shown in the following screenshot. Now, draw the combo box where you want and when you release your mouse then you will see the Combo Box Wizard dialog box. Here you have different option for data; let us select the 2nd option wherein, we will add the values and click Next. Enter the values you want to be displayed in the drop-down list and click Next. Click Next again. Enter the label for your combo box and click Finish. You can see that combo box is created but it is not aligned to other field. Let us do that first by selecting all fields and then go to the Arrange tab. To the left, you will see the Stacked option. Click this button. You can now see the fields are aligned. To make the size of each field same we have different options, let us click the Size/Space button. From the drop-down list, click To Shortest. Now go to the Form view. A user can now easily select any option for the Phone type. Print Page Previous Next Advertisements ”;

MS Access – Create Form ”; Previous Next Forms in Access are like display cases in stores that make it easier to view or get the items that you want. Since forms are objects through which you or other users can add, edit, or display the data stored in your Access desktop database, the design of your form is an important aspect. There”s a lot you can do design-wise with forms in Microsoft Access. You can create two basic types of forms − Bound forms Unbound forms Bound Forms Let us now understand what Bound Forms are − Bound forms are connected to some underlying data source such as a table, query, or SQL statement. Bound forms are what people typically think of when they think of the purpose of a form. Forms are to be filled out or used to enter or edit data in a database. Examples of bound forms will typically be what users use to enter, view or edit data in a database. Unbound Forms Let us look into Unbound Forms − These forms are not connected to an underlying record or data source. Unbound forms could be dialog boxes, switch boards, or navigation forms. In other words, unbound forms are typically used to navigate or interact with the database at large, as opposed to the data itself. Types of Bound Forms There are many types of bound forms you can create in Access. Let us understand the types − Single Item Form This is the most popular one and this is where the records are displayed — one record at a time. Multiple Item Form This displays multiple records at a time from that bound data source. Split Form The form is divided into halves, either vertically or horizontally. One half displays a single item or record, and the other half displays a list or provides a datasheet view of multiple records from the underlying data source. Creating Forms There are a few methods you can use to create forms in Access. For this, open your Database and go to the Create tab. In the Forms group, in the upper right-hand corner you will see the Form Wizard button. Click on that button to launch the Form Wizard. On this first screen in the wizard, you can select fields that you want to display on your form, and you can choose from fields from more than one table or a query. Let us assume we want to simply have a quick form that we are going to use for data entry for our employee information. From Tables/Queries drop-down list, select tblEmployees table. Click on the double arrow to move all the fields at once. Let us just leave it with that one table, and click Next. The following screen in the Form Wizard will ask for the layout that we would like for our form. We have columnar, tabular, datasheet and justified layouts. We will choose the columnar layout here and then click Next. In the following screen, we need to give a title for our form. Let us call it frmEmployees. Once you have given your form a title, you can open the form to see what that form looks like, or you can begin entering information into your table. Or you can choose the option to modify the form”s design. Let us choose the first option to open the form to view or enter information and click Finish. Now, take a look at the following screenshot. This is what your form looks like. This is a single item form, meaning one record is displayed at a time and further down you can see the navigation buttons, which is telling us that this is displaying the record 1 of 9. If you click on that button then, it will move to the next record. If you want to jump to the very last record in that form or that table, you can use the button right beside that right arrow, the arrow with a line after it, that”s the last record button. If you want to add new employee information, go to the end of this records and then after 9 records you will see a blank form where you can begin entering out the new employee”s information. This is one example of how you can create a form using the Form Wizard. Let us now close this form and go to the Create tab. Now we will create a slightly more complicated form using Wizard. Click the Form Wizard and this time, we will choose fields from a couple of different tables. In this Form Wizard, let us choose tblProjects for Tables/Queries, and select a few Available Fields such as ProjectID, ProjectName, ProjectStart, and ProjectEnd. These fields will now move to Selected Fields. Now select tblTasks for Tables/Queries and send over the TaskID, ProjectID, TaskTitle, StartDate, DueDate and PercentComplete. Click Next. Here, we want to retrieve data from a couple of different objects. We can also choose from options on how we want to arrange our form. If we want to create a flat form, we can choose to arrange by tblTasks, which will create that single form, with all the fields laid out in flat view as shown above. However, if we want to create a hierarchical form based on that one-to-many relationship, we can choose to arrange our data by tblProjects. In the above window, we have the option to include a subform for tblTasks, or we can make that a linked form. This linked form is where tblProjects will have a button that will launch that second form filtered to the project that we have selected in that underlying projects form. Let us now select the Form with subform(s), and then click Next. In the following screen, you can choose a layout for your subform. The Datasheet View gets selected by default. The Datasheet View is similar to Table View. Now, click Next. In the following

MS Access – Overview ”; Previous Next Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and softwaredevelopment tools. It is a member of the Microsoft Office suite of applications, included in the professional and higher editions. Microsoft Access is just one part of Microsoft’s overall data management product strategy. It stores data in its own format based on the Access Jet Database Engine. Like relational databases, Microsoft Access also allows you to link related information easily. For example, customer and order data. However, Access 2013 also complements other database products because it has several powerful connectivity features. It can also import or link directly to data stored in other applications and databases. As its name implies, Access can work directly with data from other sources, including many popular PC database programs, with many SQL (Structured Query Language) databases on the desktop, on servers, on minicomputers, or on mainframes, and with data stored on Internet or intranet web servers. Access can also understand and use a wide variety of other data formats, including many other database file structures. You can export data to and import data from word processing files, spreadsheets, or database files directly. Access can work with most popular databases that support the Open Database Connectivity (ODBC) standard, including SQL Server, Oracle, and DB2. Software developers can use Microsoft Access to develop application software. Microsoft Access stores information which is called a database. To use MS Access, you will need to follow these four steps − Database Creation − Create your Microsoft Access database and specify what kind of data you will be storing. Data Input − After your database is created, the data of every business day can be entered into the Access database. Query − This is a fancy term to basically describe the process of retrieving information from the database. Report (optional) − Information from the database is organized in a nice presentation that can be printed in an Access Report. Architecture Access calls anything that can have a name an object. Within an Access desktop database, the main objects are tables, queries, forms, reports, macros, data macros, and modules. If you have worked with other database systems on desktop computers, you might have seen the term database used to refer to only those files in which you store data. But, in Access, a desktop database (.accdb) also includes all the major objects related to the stored data, including objects you define to automate the use of your data. Print Page Previous Next Advertisements ”;

MS Access – Data Types ”; Previous Next Every field in a table has properties and these properties define the field”s characteristics and behavior. The most important property for a field is its data type. A field”s data type determines what kind of data it can store. MS Access supports different types of data, each with a specific purpose. The data type determines the kind of the values that users can store in any given field. Each field can store data consisting of only a single data type. Here are some of the most common data types you will find used in a typical Microsoft Access database. Type of Data Description Size Short Text Text or combinations of text and numbers, including numbers that do not require calculating (e.g. phone numbers). Up to 255 characters. Long Text Lengthy text or combinations of text and numbers. Up to 63, 999 characters. Number Numeric data used in mathematical calculations. 1, 2, 4, or 8 bytes (16 bytes if set to Replication ID). Date/Time Date and time values for the years 100 through 9999. 8 bytes Currency Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. 8 bytes AutoNumber A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. 4 bytes (16 bytes if set to Replication ID). Yes/No Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off). 1 bit. If you use previous versions of Access, you will notice a difference for two of those data types. In Access 2013, we now have two data types — short text and long text. In previous versions of Access these data types were called text and memo. The text field is referred to as short text and your memo field is now called long text. Here are some of the other more specialized data types, you can choose from in Access. Data Types Description Size Attachment Files, such as digital photos. Multiple files can be attached per record. This data type is not available in earlier versions of Access. Up to about 2 GB. OLE objects OLE objects can store pictures, audio, video, or other BLOBs (Binary Large Objects) Up to about 2 GB. Hyperlink Text or combinations of text and numbers stored as text and used as a hyperlink address. Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters). Lookup Wizard The Lookup Wizard entry in the Data Type column in the Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. Dependent on the data type of the lookup field. Calculated You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. These are all the different data types that you can choose from when creating fields in a Microsoft Access table. Print Page Previous Next Advertisements ”;

MS Access – Wildcards ”; Previous Next Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting databased on a specified pattern match. Access supports two sets of wildcard characters because it supports two standards for Structured Query Language. ANSI-89 ANSI-92 As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases such as *.mdb and *.accdb files. You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases. Access projects use the ANSI-92 standard because SQL Server uses that standard. ANSI-89 Wildcard Characters The following table lists out characters supported by ANSI-89 − Character Description Example * Matches any number of characters. You can use the asterisk (*) anywhere in a character string. wh* finds what, white, and why, but not awhile or watch. ? Matches any single alphabetic character. B?ll finds ball, bell, and bill. [ ] Matches any single character within the brackets. B[ae]ll finds ball and bell, but not bill. ! Matches any character not in the brackets. b[!ae]ll finds bill and bull, but not ball or bell. – Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd. # Matches any single numeric character. 1#3S finds 103, 113, and 123. ANSI-92 wildcard characters The following table lists out characters supported by ANSI-92 − Character Description Example % Matches any number of characters. It can be used as the first or last character in the character string. wh% finds what, white, and why, but not awhile or watch. _ Matches any single alphabetic character. B_ll finds ball, bell, and bill. [ ] Matches any single character within the brackets. B[ae]ll finds ball and bell, but not bill. ^ Matches any character not in the brackets. b[^ae]ll finds bill and bull, but not ball or bell. – Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd. Example Let us now look at a simple example of using these wildcard characters by opening the query design. Add the highlighted tables and close the Show Table dialog box. Add the fields in the query grid which you want to see as a query result. Run your query. Let us again go to the query design and add prompt for project name. Now run your query and let us assume that you don’t know the exact project name, but you know that the project name contains the words “potion”. Click Ok. The above step does not generate any result. That is because Access is looking for an exact match in the project name field. It is looking for that project which has the word potion in its name. If you want it so that the users can enter wildcards to replace unknown characters, then you need to adjust the criteria and include the operator like. When you run the query, users can use wildcards to replace any number of characters. Let us assume that you know that the word potion appears somewhere in the title but you are not exactly sure where. You can add an * to replace any number of characters before the word potion and then add another * after the. Click Ok. It is always good for the users to know the wildcards they can enter. But there are users who may not know the wildcards they can and cannot enter. In that case, you can enter the wildcards yourself. In this situation, in-between the Like operator and our parameter prompt, we can add those wildcards and now there”s a very specific way we have to write this. After the word like in quotation marks, enter the wildcard that we are using. In this case, we have used “*” to replace any number of characters. We will now add this to the parameter. To do so, we need ampersand (&) symbol and a space. We will now repeat this step and add another ampersand (&) because we”re joining that wildcard to whatever criteria is entered in for that project name by the user and then “*” in quotes. Let us now run our query again. Enter the word potion in the prompt without any wildcards. The query will now track those wildcards on either side of whatever is entered here. We need to simply type potion and press enter. We now get the results we were looking for and the results will be the same regardless of what the users enter. Let us say we want to find every project with the word “the” in the title. Then, you need to just type the word and Enter or click Ok. With this query, it becomes easier to search for projects with the word “the” in their names. The 2nd level of results may also include the project names with the word “mother” where “the” is part of the word. Print Page Previous Next Advertisements ”;

MS Access – Adding Data ”; Previous Next An Access database is not a file in the same sense as a Microsoft Office Word document or a Microsoft Office PowerPoint are. Instead, an Access database is a collection of objects like tables, forms, reports, queries etc. that must work together for a database to function properly. We have now created two tables with all of the fields and field properties necessary in our database. To view, change, insert, or delete data in a table within Access, you can use the table’s Datasheet View. A datasheet is a simple way to look at your data in rows and columns without any special formatting. Whenever you create a new web table, Access automatically creates two views that you can start using immediately for data entry. A table open in Datasheet View resembles an Excel worksheet, and you can type or paste data into one or more fields. You do not need to explicitly save your data. Access commits your changes to the table when you move the cursor to a new field in the same row, or when you move the cursor to another row. By default, the fields in an Access database are set to accept a specific type of data, such as text or numbers. You must enter the type of data that the field is set to accept. If you don”t, Access displays an error message − Let us add some data into your tables by opening the Access database we have created. Select the Views → Datasheet View option in the ribbon and add some data as shown in the following screenshot. Similarly, add some data in the second table as well as shown in the following screenshot. You can now see that inserting a new data and updating the existing data is very simple in Datasheet View as working in spreadsheet. But if you want to delete any data you need to select the entire row first as shown in the following screenshot. Now press the delete button. This will display the confirmation message. Click Yes and you will see that the selected record is deleted now. Print Page Previous Next Advertisements ”;

Discuss MS Access ”; Previous Next Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and softwaredevelopment tools. It is a part of the Microsoft Office suite of applications, included in the professional and higher editions. This is an introductory tutorial that covers the basics of MS Access. Print Page Previous Next Advertisements ”;