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 ”;
MS Access – Built-In Functions ”; Previous Next In this chapter, we will be working with Built-in Functions. In Access, there are close to a hundred built-in functions and it is almost impossible to cover every single one of them. In this chapter, we will cover the basic structure, syntax, and use some of the more popular functions, and also the pitfalls, so that you can go exploring some of the other functions on your own. Functions A function is a VBA procedure that performs a task, or calculation, and returns a result. Functions can generally be used in queries, but there are other places that you can use functions. You can use functions in table properties, for example, if you want to specify a default value for a date/time field, you can use the date or the Now function to call up the current date/time information from your system, and input that value automatically. You can also use functions in an expression when you create a calculated field, or use the functions inside form or report controls. You can use functions even in macro arguments. Functions can be quite simple, requiring no other information to be called, or, simply reference one field from a table or query. On the other hand, they can also get quite complicated, with multiple arguments, field references, and even other functions nested inside another function. Let us now look into some examples of using built-in functions. Date & Time Functions Let us now understand the Date and Time functions − The Date() function is designed to return the current system date. This function does not require any function arguments or additional information. All you have to do is write the name of the function and those open and close parentheses. There are two very similar built-in functions Time() and Now(). The Time() Function returns the current system time only and the Now() Function returns both the current system date and time. Depending on the data that you want to track, or store, or query, you have three built-in, easy-to-use functions to help with that task. Let us now open your database and create a new query using query design and add tblProjects and tblTasks. Add ProjectName from tblProjects and TaskTitle, StartDate and DueDate from tblTasks and run your query. You can now see all the different tasks from all projects. If you want to view the project tasks that are in progress as on today’s date, then we have to specify a criterion using a Date() Function to look at projects that start on or after today”s date. Let us now specify the criteria underneath the StartDate. The criteria starts with an operator greater than symbol, followed by an equal to symbol and then Date Function. When we run this query, all the tasks will occur either on today”s date or in the future as in the following screenshot. This was an example of how you can use the Date() function as query criteria. Let us now say this query needs to be more flexible in terms of the dates it is pulling starting this week. We do have a couple of different tasks that began this week, that are not showing up in this current list, because of our criteria. It”s looking at start dates that are equal to today or above. If we want to view the tasks that started this week, that have not yet completed or should complete today, let us go back to the Design View. Here, we will add some additional information to these criteria. In fact, we want it greater than or equal to today”s date minus seven days. If we type minus seven and run the query, you can see the tasks that started this week as well. DateDiff() Function The DateDiff() Function is another very popular date/time function. The DateDiff Function returns a Variant (long), specifying the number of time intervals between two specified dates. In other words, it calculates the difference between two dates, and you get to pick the interval by which the function calculates that difference. Let us now say we want to calculate our authors” age. For this, we first we need to create a new query and add our authors table and then add FirstName, LastName, and the BirthDay fields. We can calculate people”s age by calculating the difference between their date of birth, or birthday and whatever today”s date is. Let us try using the DateDiff Function in a new field. Let us call it Age followed by a colon, and then write DateDiff Function. The first function argument for the DateDiff function is the interval, so type “yyyy”. The next function argument is the first date that we want to calculate by, which, in this case, will be the Birthday field. The third function argument is whatever today”s date is. Now, run your query and you will see the new field which shows the age of each author. Format() Function The Format() Function returns a string, containing an expression formatted according to instructions contained in a format expression. Here is the list of user-defined formats which can be used in Format() function.ss Setting Description yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second Let us now go back to your query and add more fields in the same using the Format() function. Type the Format Function. The first function argument will be an expression, which can be almost anything. Let us now have the birthday field as the first and the next thing is to write our format. In this case, we need month, month, day, day. Write “mmdd” in quotes and then, run your query. It is now taking the date from the birthday field, 4 is the month and 17 is the day. Let us add “mmm” and “mmmm” instead of “mmdd” in the next fields as in the
MS Access – Macros ”; Previous Next In this chapter, we will cover the basics of Macros in Access. A Macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. Macros in Access work a bit different from Macros in Word or Excel, where you essentially record a series of keystrokes and play them back later. Access Macros are built from a set of predefined actions, allowing you to automate common tasks, and add functionality to controls or objects. Macros can be standalone objects viewable from the Navigation pane, or embedded directly into a Form or Report. Once you have created database objects like tables, forms and reports, Macros can provide a quick and easy way to tie all those objects together to create a simple database application that anyone can use or even modify, with relatively little training. Macros provide a way to run commands without the need to write or even know VBA code, and there is a lot that you could achieve just with Macros. Creating a Macro Let us start be creating a very simple Macro that opens a form when a command button is clicked. For this, we need to open your database and frmEmployeeData form in which we have created two tabs. In this form, we can add a button allowing users to open up all of the job information. Let us now go to the Design View of this form and add button form the Controls menu. When you release your mouse, you will see the Command Button Wizard dialog box. There is a couple of ways to build that Macro action, but the simplest way is to simply use the Command Button Wizard. For common actions like opening a form, select Form Operations from the Categories list and then select Open Form from the Actions list and click Next as in the above screenshot. You need to specify which form you would like to open with the command button. For now, let us select frmJobs and click Next. In this screen we have two options, we can open the form and display a very specific record, or we can open the form and show all the records. Let us select the second option and click Next as in the above screenshot. We could have the command button itself display a picture or you can select the Display Text. Here, we want the text View Jobs to display and now click Next. You can now provide a meaningful name to your command button as in the above screenshot. This can be used in other codes or other Macros. Let us call this cmdViewJobs and click Finish. Now go to the Form View. You will now see a View Jobs button on your form. Let us click on it. Now you have a form open, but you will not be viewing any information. Let us go back to the frmEmployeeData form Design view. Make sure that the command button is selected and click on the Event tab on the Property Sheet. Upon clicking, you will see an embedded Macro created by the Wizard. If you now want to modify this Macro, click on the … button to open up the Macro generated by the Wizard. This is the Macro Designer and on the right you will see the Action Catalog. This is where all of your actions will live in folders. You have the Data Entry options, Data Import/Export and so on, and on the left in the main area you have another Macro. It only contains one action, and clicking on that one action you can view other properties for that specific action. You will see the form name and you can hit that drop-down arrow to view the forms available in your database. You can change how that form is viewed, you can have it open to Form view, Design view, Print Preview at your choice. You can apply a filter name or a Where condition. Here we want to change the Data Mode because frmJobs is set to the Add Mode which only allows the addition of new records. We can override this here in this Macro by changing it to the Edit Mode. Now save your Macro, and then close the Macro Designer and go back to the Form View. Let us click on View Jobs again. You can now see that it opens your frmJobs form and allows you to scroll through all of the available jobs in our database. Print Page Previous Next Advertisements ”;
MS Access – Create Relationships ”; Previous Next In this chapter, we will understand the need to create relationships between related tables. One of the goals of good database design is to remove data redundancy. To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once. To do this, all the common fields which are related to each other are placed in one table. To do this step correctly, you must first understand the relationship between your tables, and then specify these relationships in your Access database. Why Create Table Relationships? MS Access uses table relationships to join tables when you need to use them in a database object. There are several reasons why you should create table relationships before you create other database objects, such as forms, queries, macros, and reports. To work with records from more than one table, you often must create a query that joins the tables. The query works by matching the values in the primary key field of the first table with a foreign key field in the second table. When you design a form or report, MS Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values. When you design a database, you divide your information into tables, each of which has a primary key and then add foreign keys to related tables that reference those primary keys. These foreign key-primary key pairings form the basis for table relationships and multi-table queries. Let us now add another table into your database and name it tblHRData using Table Design as shown in the following screenshot. Click on the Save icon as in the above screenshot. Enter tblHRData as table name and click Ok. tblHRData is now created with data in it. Print Page Previous Next Advertisements ”;
MS Access – Summarizing Data ”; Previous Next Aggregate queries are great if you are looking just for one specific number, but if you want summarizing information like a spreadsheet-like summary you might want to consider trying a crosstab query. When you want to restructure summary data to make it easier to read and understand, consider using a Crosstab Query. A crosstab query is a type of Select Query. When you run a crosstab query, the results get displayed in a datasheet. This datasheet has a different structure from the other types of datasheets. The structure of a crosstab query can make it easier to read than a simple select query that displays the same data, as shown in the following screenshot. By far, the simplest way to create a crosstab query in Access is to simply use the wizard that comes with Access to create your crosstab query. Let us now go to the Create tab, in the queries groups and click on query wizard. In the above dialog box, you can see all kinds of special queries. You can create a simple query wizard like how we have been doing so far from the Design View. The second one is the one we want — the crosstab query that displays data in a compact spreadsheet like format. Now, select the crosstab query and click Ok. The first screen in the Wizard is going to prompt what table or query contains the fields that you want for your Crosstab Query results. Click on the Queries radio button and select qryOrdersInformation — this is the one that we created earlier which contain the subtotal, sales tax etc. Click Next. We will now look into the available fields from that query. It will prompt you to enter the field values that you want as row headings. Let us say we want a simple list of all of our different book titles. Now select the book title fields and send that over to your selected field area and click Next. In the above dialog box, the question is what you want to use as column headings and that really depends on what you want to evaluate. Let us assume that you want to view our sales by date, choose TransactionDate and click Next. The question in the above dialog box is a specialized one based on the field we previously selected since we chose a date time column. It is asking if we want to group our date time column information by a specific interval. Select the year option and click Next. The next question in the above dialog screen asks what number do you want calculated for each column in the row intersection. In this case, we can go by quantity of the books sold by selecting quantity (QTY) and Sum from the functions and click Next. The very last screen of your wizard is going to ask what do you want to name your query and further, click Finish to View that query. We now have book by book information and also information on the total sales like when each one of those sales occurred. Print Page Previous Next Advertisements ”;
MS Access – Duplicates Query Wizard ”; Previous Next In this tutorial, we have created a variety of select queries, mainly from the Design View. But in Access, we can make use of some special query wizards to create a couple of specific select queries. The first one is Find Duplicates Query wizard. The Find Duplicates Query Wizard will create a query that locates records with duplicate field values in a single table or query. As we have discussed that relational databases are designed to avoid storing duplicate information. But despite that design, sometimes users accidentally enter duplicate information. Example In a customer”s table, you can have the same customer accidentally added twice. In such cases, the customer will have the same address, but different customer IDs, which can create problems with reporting. In this situation, you can make use of the duplicates query wizard to quickly locate possible duplicate entries. Let us now open our Access database which contains tblAuthers table and go the Create tab and, in the queries group, select query wizard. If you see the following screenshot, you have four different wizards to choose from. Select the find duplicates query wizard and click Ok. The very first screen of the find duplicates query wizard will ask what table or what query you want to search for possible duplicates. Let us say we want to check our author”s table to make sure that the same author hasn”t accidentally been entered twice. So, select tblAuthors and click Next. The second screen in the wizard will ask what fields might contain duplicate information. Typically, you will not be using your primary key field, because, again, when you designate a field in Access as a primary key, Access will not allow duplicates to be entered. We will look at any other field or a combination of fields. We can search by the last name or the first name and the last name, or you can search by their street address, or to be more specific with their telephone number or birthday. Let us now search by the first name, the last name, and birthday and click Next. The following screen in this wizard will ask for the fields we want to be displayed in our query. For this, hit the double arrow, all of the fields will move over to the additional query fields area, and will be added to our query results. Let us now click Next. It will take us to the last screen in this query wizard. In the following screen, enter how do you want to name your query. By default, it”s going to name it find duplicates for plus whatever the name of the object that you”re querying. In this case, tblAuthors, but you can give it any other name too and click finish. Here, Access has found a possible duplicate, and that”s going to be author Jose Caline which has same birthday, same address, same telephone number but different AuthorIDs. This one has definitely been entered twice by accident. We have now added all of the fields to our query, we could just go and delete the record. We also have to make sure that we don”t have any related records in another table. Select any record and choose Delete as in the following dialog box. Access gives you a prompt, “You are about to delete one record.” Click Yes if you want to continue. If you”d like to see how that wizard has created this query, go into the Design View and see what all has been added to this query. As you can see in the above screenshot, we have our fields and some specific criteria underneath the first name field. This is how this wizard is looking for that duplicate information. It is by far the easiest method to find duplicates. Print Page Previous Next Advertisements ”;
MS Access – Grouping Data ”; Previous Next In this chapter, we will be covering how to calculate on a group of records in Access. We have created a field that calculates row by row or record by record to create a line total or subtotal field, but what if we wanted to calculate down by a group of records rather than by individual ones. We can do this by creating what”s known as an Aggregate Query. Aggregate Query An aggregate query also known as a totals or summary query is a sum, mass or group particulars. It can be a total or gross amount or a group or subset of records. Aggregate queries can perform a number of operations. Here”s a simple table listing some of the ways to total on a group of records. S.No Aggregate Functions & Description 1. Sum Adds the field values 2. Avg Average of the field values 3. Min Lowest (minimum) field value 4. Max Highest (maximum) field value 5. Count Count of the values (records) 6. StDev Standard deviation of the field values including date/time fields 7. Var Variance of the field values including date/time Let us open your database and go to the Query Design and include the following tables − Close this dialog box and the following two fields will be displayed as shown in the query grid in the following screenshot. This is a simple query and we are displaying only two fields — book title and quantity and when we run it we”re seeing every single individual order in our database. Let us now run this query and you will see the following results. This is why book titles are repeating. Separate orders have been placed for each of these books here and they were ordered in different quantities. Let us assume we want to see a summary of only that book title that has been listed only once. And then the sum of the quantity listed beside it. Let us now go to the Design View and in the Design tab, you will see a Sigma symbol. This is your totals button. Click the sigma symbol which will open another row underneath your field in the table row and from here, you can specify how you are grouping this query. We will now group by book title and also sum our quantity field. If we click on group by area and further click on the drop-down menu, all the options will be listed down. In this case, we will choose the option Sum and then run your query. You can now see each individual book and also all the individual orders displayed beside the book name. Concatenation in Access We learned the process of normalization, storing information in separate fields. Sometimes you want to see or view data together like combining the first name and last name fields as a single field. You can display that information together by creating a calculated field that concatenates one or more strings. You can also add other characters like a comma or period that you may want. To concatenate in Access, there are two different operators you can use the ampersand (&) and the plus (+) sign. The ampersand will combine two strings into one whereas the plus sign will combine two strings and propagate NULL values, for example, if one value is NULL the entire expression evaluates to null. Example Let us take a simple example to understand the process of creating a new query using query design. This is going to be a very simple query that is pulling information from our customers’ table. Let us now add tblCustomers table close that show table dialog box. We will try some concatenation with some simple named fields. Let us now add the first name and last name fields and run the query. As you can see, the first name and the last name are separated into two different fields. We need to display this information together. Go back to the Design View and in the third field create a new field called full name. Now, add the full name and type the expression that concatenates those two fields together. Let us run the query and you will see a new calculated field. It can concatenate the information from these two fields together, but it doesn”t exactly appear the way we want it to. It runs all of that text together because it”s performing the way we have asked it to. We will now have to ask Access to add space in between the information from these two fields. Let us now go back to the Design View and add another section to this expression. Add a Space inside quotes and another ampersand. This makes Access take the information from that first name field; add it with a space and then add the information from the last name field at the very end. Now, run the query again and you will see the following results. Print Page Previous Next Advertisements ”;
MS Access – Many-To-Many Relationship ”; Previous Next In this chapter, let us understand Many-to-Many Relationship. To represent a many-tomany relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. To do so, we also need to add a junction table. Let us first add another table tblAuthers. Let us now create a many-to-many relationship. We have more than one author working on more than one project and vice versa. As you know, we have an Author field in tblProjects so, we have created a table for it. We do not need this field any more. Select the Author field and press the delete button and you will see the following message. Click Yes. We will now have to create a junction table. This junction table have two foreign keys in it as shown in the following screenshot. These foreign key fields will be the primary keys from the two tables that were linked together — tblAuthers and tblProjects. To create a composite key in Access, select both these fields and from the table tools design tab, you can click directly on that primary key and that will mark not one but both of these fields. The combination of these two fields is the tables’ unique identifier. Let us now save this table as tblAuthorJunction. The last step in bringing the many-to-many relationships together is to go back to that relationships view and create those relationships by clicking on Show Table. Select the above three highlighted tables and click on the Add button and then close this dialog box. Click and drag the AuthorID field from tblAuthors and place it on top of the tblAuthorJunction table AuthorID. The relationship you’re creating is the one that Access will consider as a one-to-many relationship. We will also enforce referential integrity. Let us now turn on Cascade Update and click on the Create button as in the above screenshot. Let us now hold the ProjectID, drag and drop it right on top of ProjectID from tblAuthorJunction. We will Enforce Referential Integrity and Cascade Update Related Fields. The following are the many-to-many relationships. Print Page Previous Next Advertisements ”;
MS Access – Modify Form ”; Previous Next We have learnt several ways to create simple data entry forms. Although the forms ease the process of data entry; these may not serve other purposes that you would want. They may not be catchy or user-friendly for what you have intended. Upon creating a form, the first thing you will probably want to do is resize or move the controls around. You might also need to add a control or remove a control. We will now discuss how to modify your form in an easy way. Controls It is merely a generic term used to describe any object on a form or report that displays data, performs actions or items used for decorations such as a line. In other words, a control is just about anything that is placed on a form or report. The following can be considered as controls − Objects such as labels. Unbound or bound text boxes that you can use to add or edit or even calculate an expression. Command buttons that perform actions like Save, Open an e-mail or Print and these buttons are also known as controls. It is just a generic term for any object on a form or report. We will now look at the different aspects of making a form presentable and understand how to edit and modify a form. Let us now open our Multiple Items form which lists out all employees from tnlEmployees. As you can see a list of employees, but doesn”t really look like a very user friendly list. Our controls are oversized. They are too big, spaced apart and do not provide a very useful list view. To edit the appearance of your controls on this form, you have two form views that you can use. In the Home tab, click the View drop-down. You have the Layout View or the Design View, and both of these views are used to edit your form. The Form View opens up by default; this is the view you will use to interact with or edit the underlying data source. To edit the appearance of the form itself, we need to go to the Layout View first. When you switch to the Layout View, you will see a series of contextual tabs appear. At the top of Access, you will see an area marked Form Layout Tools with three tabs — Design tab, an Arrange tab, and a Format tab and each of these tabs have different options for formatting the look or appearance of the form. When you take your mouse and click on any one of these controls, you will notice that Access will highlight a given area of that form and all controls within that area are shaded a light orange whereas the actual control that you select will be shaded darker than the previous one or have a darker orange border around where you click. Here you can resize your controls as you want by clicking and dragging your mouse to resize the height or width or both of that one control. On this particular form, when you resize any single control, you also change the size of the rest of your controls on your form, this is because of how these controls are grouped. Let us now adjust all the fields the way you want by using the click and drag function of the mouse. That is one quick way of editing the height and width of controls in your form from this Layout View. Themes In Access, there are some basic ways to format your forms by using built-in themes, colors, and font styles, customizing fill colors and shading alternate rows. Let us now open frmEmployees. The forms that Access creates are plain and simple. They have a blue bar on the top and a white background. If you want to see how else you can stylize these forms, you can go to the Design View or Layout View and explore some of the options you have on the Design tab in the Themes area. If you click on the Themes” drop-down gallery, you have many pre-created themes to try out from. Hovering your mouse over any one of them will give you a preview of changing things like colors and font sizes and the actual font used. To apply a particular style, simply click your mouse on it and you can see what that looks like. If you like the theme but you want to change the colors, you can adjust the colors by going back to the Themes group on the Design tab and choosing the color you like. You can also create custom colors to match your company”s colors. Similarly, you also have a series of font styles to choose from. You can choose one from the many that come prebuilt with the Office Suite or you can customize those fonts, choosing a specific heading font, a body font and even creating a custom name for that font group and saving. Let us go back to frmEmployees. In this form, you will see that every alternate row is shaded light gray. The formatting option is referred to as Alternate Row Color and if you want to adjust that in a multiple form, go to the Design View. Select that detail section and then go to the Format tab and in background group you should see an option for Alternate Row Color. You can change the colors for alternate rows. To see what that looks like, simply go to the Form View or the Layout View. If you don”t want any shading at all, you can choose No Color as your Alternate Row Color and that is more the traditional look from earlier versions of Access. Print Page Previous Next Advertisements ”;
MS Access – Indexing ”; Previous Next An index is a data structure, a special data structure designed to improve the speed of data retrieval. If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Access uses indexes in a table as you use an index in a book to find data. In some instances, such as for a primary key, Access automatically creates an index for you. At other times, you might want to create an index yourself. An index stores the location of records based on the field or fields that you choose to index. After Access obtains the location from the index, it can then retrieve the data by moving directly to the correct location. In this way, using an index can be considerably faster than scanning through all of the records to find the data. Indexes can speed up searches in queries, but they can also slow down performance when adding or updating records. Let us now look into what indexes Microsoft Access creates by default and how to create them ourselves and learn how to delete any unnecessary indexes. Open the tblEmployees table in Access database which we have created. We haven”t really played with indexes in this database but that doesn”t mean we don”t have any. In fact, any field that is set as a primary key in Access is automatically indexed. Access creates additional secondary indexes depending on the names of your fields. Let us now go to the File menu and select Options. You will see the Access Options window. Go to the Object Designers and you will see a section labeled AutoIndex on Import/Create and in the textbox you will see ID;key;code;num. By default, access automatically adds a secondary index to fields that start or end with these names and that goes for fields you have imported as well as ones you have manually created. If you want to make any field indexed you can go to the Field tab. Select any field that you want indexed and check the Indexed checkbox in Field Validation section. You also have alternate options for creating or removing an index. You can go back to the Design View. You can adjust indexes by selecting any field. You can also see how they are indexed in the field properties area below. Any field that has No selected next to indexed, means there is no index for that given field. You can change that by clicking on the drop-down menu and choosing the other two options — Yes (Duplicate OK) and Yes (No duplicates). The last option Yes (No Duplicates) means that Access will automatically prohibit duplicate values in that field. Let us now create an index for our last name field. Let us select LastName to index and say Yes (Duplicates OK). As we save, Access will create that index. Another area where you can view and adjust your indexes for a table is the tables design area in the Show/Hide group. If you click on this Indexes button that will bring up a special view displaying all the indexes created for this table. We now have two indexes for tblEmployees —one that was created automatically based on the primary key field and one that we just created for the LastName field. These are the different ways to deal with indexes in Microsoft Access tables. Print Page Previous Next Advertisements ”;