Category: ms Access

MS Access – Useful Resources ”; Previous Next The following resources contain additional information on MS Access. Please use them to get more in-depth knowledge on this. Useful Video Courses MS Access 2016 Online Training Most Popular 54 Lectures 5 hours Tutorialspoint More Detail Access Masterclass – Level 1 & 2 – Beginner to Intermediate 92 Lectures 6.5 hours Ermin Dedic More Detail 2021 Professional MS EXCEL Course with Real Applications 32 Lectures 3.5 hours Smarter Academy More Detail Learn MS Excel, Data Analysis, and Financial Analysis 50 Lectures 3 hours Tayana Grubisic More Detail Microsoft Access Training – Practice to Perfect Skills Most Popular 45 Lectures 3 hours Rada Asghar More Detail MS Access Course Beginner for Arab 18 Lectures 6.5 hours Narmeen AL-faily More Detail Print Page Previous Next Advertisements ”;

MS Access – Calculated Expression ”; Previous Next In MS Access, an expression is like a formula in Excel. This consists of a number of elements that can be used alone or in a combination to produce a result. Expressions can include operators’, constants, functions and identifiers. Expressions can be used to perform a variety of tasks from retrieving the value of a control or supplying criteria to a query to creating calculated controls and fields or defining a group level for report. In this chapter, we will be focusing on creating a very specific kind of expression called a calculated expression. We will create several calculated fields that will calculate and display data that is not stored anywhere within the database itself but calculated from separate fields that are stored. We now have a new Access database that contains more data to calculate. Example Let us try an example to understand this. We will be retrieving some information from a variety of tables — tblCustomers, tblOrders, tblOrdersDetails and tblBooks and reviewing a variety of fields. Let us now run the query and you will see the following results. We want a very simple calculation for a subtotal which will take the quantity of the books ordered and multiply that by the retail price of the book. It is a good idea to begin every expression or any expression with a name to call that field. Let us call this one subtotal. The full expression (Subtotal: [tblOrdersDetails]![QTY]*[tblBooks]![RetailPrice]) includes the table name, first tblOrdersDetails inside square brackets because that is where our quantity field lives. Now an exclamation is telling Access to look inside that table for a field QTY and multiply that by the retail price field in tblBooks. Let us try to run your query and you will see at end the subtotal field. Let us say we want to calculate our sales tax. In the query grid we will do in the exact same way we did before and this time we will call the field simply Tax and Let us multiply Subtotal by nine percent tax rate. Let us run your query again and you will see the new Tax field in the end. Print Page Previous Next Advertisements ”;

MS Access – Data Export ”; Previous Next In this chapter, we will understand how to export data from Access. Data export is actually the opposite of importing data. In importing data, we bring data from other formats in Access, while in exporting we save the data in other formats. To understand what kind of data you can export from Access data, let us open your database and go to the External Data tab. In the Export group, you can see the different kind of options available for data export from Access. Following are the most commonly used data export formats − Microsoft Office Excel Microsoft Office Access ODBC Databases (For example, SQL Server) Text files (delimited or fixed-width) XML Files Example Let us look at a simple example of data export from Access. Open your database where you want to export the data from. In the Navigation Pane, select the object that you want to export the data from. You can export the data from table, query, form, and report objects etc. Let us select the qryAllProjects and then, go to the External Data tab. On the External Data tab, click on the type of data that you want to export to. For example, to export data in a format that can be opened by Microsoft Excel, click Excel. Access starts the Export wizard. In the wizard, you can set the information such as the destination file name and format, whether to include formatting and the layout, which records to export. Once you are done with the required information, click Ok. On this screen of the Wizard, Access usually asks you if you want to save the details of the export operation. If you think you will need to perform the same operation on a recurring basis, select the Save export steps check box and close the dialog box. The following Excel file opens up. Let us now suppose you want to export data to a text file, on the External Data tab, click on the Text File. Specify the export options and click Ok. You will see the Encode dialog box, wherein we want to export the data in default encoding. Select the first option and click Ok. On this screen of the wizard, Access usually asks you if you want to save the details of the export operation. If you think you will need to perform the same operation on a recurring basis, select the Save export steps check box and close the dialog box. You will now see that the Text file is open. Similarly, you can explore other options as well. Thus, it is highly recommended to play with other import export features too. Print Page Previous Next Advertisements ”;

MS Access – SQL View ”; Previous Next In this chapter, we will be covering the SQL view. Whenever you create a query in query design, Access automatically creates the SQL query for you. This actually retrieves data from the tables. To see how your query is created in sql when you create it in query design, let us open your database. Select the Query Design from the Create tab and add the tblEmployees table. Select the field you want to see as query result and then run your query. You can now see all the employee information as query result. You have selected certain fields in the Query Grid; at the same time, MS Access has also created an SQL Query with the results obtained from your Query Grid. To view the SQL, go to the Home tab. Select SQL View from the View menu and you will see the SQL of your query. Example The following is another example wherein, we will see the projects in progress. Let us now run your query. To see the SQL, select the SQL View from the View menu. You can see the SQL query which is generated by Access automatically. This helps retrieve data from two tables. Print Page Previous Next Advertisements ”;

MS Access – One-To-Many Relationship ”; Previous Next The vast majority of your relationships will more than likely be this one to many relationships where one record from a table has the potential to be related to many records in another table. The process to create one-to-many relationship is exactly the same as for creating a one-to-one relationship. Let us first clear the layout by clicking on the Clear Layout option on the Design tab. We will first add another table tblTasks as shown in the following screenshot. Click on the Save icon and enter tblTasks as the table name and go to the Relationship view. Click on the Show Table option. Add tblProjects and tblTasks and close the Show Table dialog box. We can run through the same process once again to relate these tables. Click and hold ProjectID from tblProjects and drag that all the way over to the ProjectID from tblTasks. Further, a relationships window pops up when you release the mouse. Click the Create button. We now have a very simple relationship created. Print Page Previous Next Advertisements ”;

MS Access – Formatting ”; Previous Next One especially useful formatting tool in Access is the ability to apply Conditional Formatting to highlight specific data. Let us take a simple example of conditional formatting. Example In this example, we will be using a form fSubCurrentProjects in our database. We have a list of all of the projects in this database and we have also got a couple of new fields like the On Time Status and the Number of Late Tasks. This form is created from another query. In this query, we have a join between a table and a query that will display the count of due dates or how many projects have tasks that are overdue. We also have a calculated field here that uses the IF function to determine whether or not the count of the due date is greater than zero. It will then display the words Late if the project is late or On Time if that specific project does not have any overdue tasks. Example 1 In this example, we will be using the above form to understand how you can use Conditional Formatting to highlight specific pieces of information. We will now highlight every single project that is currently running late. To apply Conditional Formatting to one field or more than one field, we will need to switch over to the Layout view. Now, select the On Time Status field. Now, go to the Format tab. On that Format tab, you should see a group called Control Formatting and a special button for Conditional Formatting. Let us now click on Conditional Formatting. You will now see a Conditional Formatting Rules Manager and currently we have no rules applied to this control. Let us now create a new rule by clicking on the New Rule button. You will now see a New Formatting Rule dialog box. We will first specify the type of rule we will be creating and here we have two options. The first option is to check the values in the current record or to use an expression, and the second option is to compare this record with the other records. We now have only one of two values in our form; either On Time or the word Late and that is from the given query. Let us now select the “Field Value Is” from the first combo box and then select “equal to” from the second combo box. Now, type the word “Late” in quotation marks. We can now set our Conditional Formatting, how we want this field to look like if the word Late appears in that field. Let us now change the font color to red and make it bold, italic and underline, and that”s our conditional rule. Let us now click Ok and then click Apply, and Ok again. You can see that the word Late is formatted now. This is one example of how to create a very basic conditional format rule. Example 2 Let us take another example. Here, we will make the title or the name of the project red and bold, italic and underline. Select the project name control on your form. We will now go back to our Format tab and click on Conditional Formatting and create a new rule for that specific control as shown in the above screenshot. Here, we will not be checking the value of the current field we have selected, but we will be checking it against another field on this form. Select Expression Is in the first combo box and then click on … button at the end as in the above screenshot. In the Expression Categories, you have every single object that is on this form. Doubleclick on CountofDueDate. This will send the reference to that control or that field up to our expression builder and condition if it is greater than zero. Now, click Ok. Let us now click Ok and then, click Apply and Ok again. That was an example of Conditional Formatting on a field based on values in another field. Example 3 Let us now look at another example of conditional formatting. Let us assume, we want to see which projects are more late or have more late tasks than other late projects. Select the Conditional Formatting option. Click on the New Rule button to create a new rule and then click Ok as in the above screenshot. In the New Formatting Rule, we will now select a rule type “Compare to other records”. Let us further change the Bar color to red. We want our shortest bar to represent the lowest value and the longest bar to represent the highest value. Let us now click Ok and then, click Apply and Ok again. You can now see Conditional Shading applied as in the above screenshot. Let us now go to the Form view. Print Page Previous Next Advertisements ”;

MS Access – Formatting Reports ”; Previous Next In this chapter, we will learn how to format reports. You will find that there are a lot of similarities between formatting reports and formatting forms but there are a few tools and tricks that are specific to reports. Let us now look into the concept of report sections and grouping. For this, we need to open a report that we created in the last chapter. Here, we will see how some of this information is displayed on the report. Here, we will start with the report section and grouping. Let us now go to the Design view of this report. You can see that there”s not a lot to look and here only page header, details section, and page footer are visible. You can add an additional couple of sections very easily. Right click anywhere on that report and you will see Page Header/Footer and Report Header/Footer. This particular report does not have that Report Header/Footer visible. Let us select that option and go back to the Report View. You can see it just adds a little colored area at the very top of the report. In the Design View, expand that area by hovering the mouse right at the top of that page header divider, clicking and dragging down. This will add more area to the report header. In the Report View, you will now see more area at the very top of the report as in the following screenshot. The Report Header and Footer controls appear at the top of the first page of that report. The report footer controls what you see at the very last page at the bottom of the report. The Report Header and Footer is different from your Page Header and Page Footer. Anything that is placed in the Page Header will appear at the top of every page. Likewise, anything that is placed in the Page Footer will repeat at the bottom of every page. Now you can add additional grouping levels, and to do that, you want to make sure you have the group sort and total area turned on. In the Grouping and Totals section of the Design tab, click on the Group and Sort button which will open Group, Sort, and Total area at the bottom as shown in the following screenshot. You can now add additional groups or grouping on any control that you have in your report. Let us now click on Add a Group. In the above screenshot, you can see the underlying control source for report with the project name, task title, due date and percent complete. Let us now say we want to group all of our late tasks by project, so select Project Name from the list. Now you will see an additional grouping level ProjectName Header above your details section. Instead of the project name appearing alongside each individual task that is late, we can now move this control up to this project name header. You can select it, and then press Ctrl + X on your keyboard to cut that from that details section and click anywhere in the background of that project name header, and then press Ctrl + V to paste that control in that project name header. You can now go to the Report View and see the adjustments made to group the things in the report as in the following screenshot. Every single project has its own little level on its own line, and then underneath that area you will see all of the tasks that are late for the above project. With that changed now, you can go through and start formatting your report in the Layout View. If you want to make the project name bigger, then drag the line below and change the font size to 20 in the Format Tab. You can increase the width of the control to cover the entire width of the page. To remove the border around the control, click on the Shape Outline in the Format tab as in the following screenshot. Select the Transparent option. Let us now go to the Design View as in the following screenshot. Adjust the task title to the left of your page as in the following screenshot. Let us now bring the data from the Page Header section to the ProjectName header section using Ctrl+X and Ctrl+V. Let us now go back to the Report View. You will now see that the labels appear directly above the controls that they describe. If you notice, you will see that there is no space between the tasks and the next project name. There should be additional space in between. For that, we need to add ProjectName Footer. Now, go to the Design View. In the Group, Sort and Total area click on the More button next to the project name. You will now find all the options for how to group and sort by project name as in the following screenshot. Here, we have a property called Without a Footer Section. Let us change that option by clicking on that little arrow next to the words Without a Footer Section. Change it to With a Footer Section. As you can see in the above screenshot that the ProjectName Footer section beneath the Details section is added. This will act as the footer for that project name group. Let us now go back to the Report View. To remove shading or the appearance of background for alternating areas in both the Details section and in the Project Name area, go to the Design View again. Let us change the Details section first by clicking on the detail divider and then open Properties sheet. On the Format tab, change the Alternate Back Color to No Color as in the following screenshot. Similarly change the Alternate Back Color to No Color for both ProjectName Header and ProjectName Footer and go to Report View.

MS Access – Alternate Criteria ”; Previous Next Queries come in with many advantages. You can save and run the same query again and again, and a lot of times you want to add alternate criteria. You can add alternate criteria in the following two ways − You can use the OR operator to combine two sets of criteria. You can also use the query design grid, but instead of specifying criteria on the same line, you will need to separate it in multiple row. Example Let us look at a simple example of alternate criteria. Open database and in the Create tab select Query Design. Double-click on tblEmployee and close the Show Table dialog box. Double-click on all the field you want to see as query result. You can now see that alternate criterion is specified in different rows of the LastName field. When you run this query, you will see the employees whose last name is either Pollard or Manning. Let us now run this query. As you can see that only two employees have been retrieved, because of the alternate criteria. If you want to add alternate criteria in multiple fields then you will have to use different rows for all the fields. Let us now add another alternate criterion where we can retrieve information for employees whose last name is either Pollard or Manning or the job title is Accounting Assistant. Let us now run this query. You will now see the following result. Print Page Previous Next Advertisements ”;

MS Access – Reports Basics ”; Previous Next In this chapter, we will be covering the basics of reports and how to create reports. Reports offer a way to view, format, and summarize the information in your Microsoft Access database. For example, you can create a simple report of phone numbers for all your contacts. A report consists of information that is pulled from tables or queries, as well as information that is stored with the report design, such as labels, headings, and graphics. The tables or queries that provide the underlying data are also known as the report”s record source. If the fields that you want to include all exist in a single table, use that table as the record source. If the fields are contained in more than one table, you need to use one or more queries as the record source. Example We will now take a simple example to understand the process of creating a very simple report. For this, we need to go to the Create tab. Before clicking on the Report button to create a basic report, make sure the proper query is selected. In this case, qryCurrentProjects is selected in your navigation pane. Now click on the Report button, which will generate a report based on that query. You will see that the report is open in Layout view. This provides a quick way to adjust the size or width of any of your fields that you see on the report. Let us now adjust the column widths to make everything fit in a better way. Scroll down and adjust the page control at the bottom. This was a very quick way to create a very simple report. You could also make minor changes and adjustments from the report design view. Just like forms, a report is made up of a variety of different sections. You have the detail section, which is where all of your data lives for the most part. You also will see a page header and a page footer section; these appear at the top and at the bottom of every single page in your report. Let us now change the Title of the report and give it another name. Click on the save icon to save your report. You will get the above dialog box. Enter a name for your report and click Ok. If you want to view what this report will actually look like, in Print Preview, you can go back to the View button and click on Print Preview to see what this report would look like when printed either on paper or as a PDF. Using the tools on the lower right-hand corner, you can zoom in or zoom out. You also have some buttons on the Print Preview tab that appear automatically when you switch to Print Preview. In the zoom section, you”ve got a view for one page, two pages; or if you have a longer report, you can view four pages at once, eight pages or twelve pages. You can also adjust simple things such as the size of the paper that you are using to print, the margins for your report, the orientation, the number of columns, page set up, etc. And that is how you can create a very quick simple report using the Report button on the Create tab. Create a Report Using Report Design Report Design is another method for creating a quick report in Access. For this, we need to use the Report Design View button, which is like the Form Design button. This will create a blank report and open it directly to the Design View, allowing you to change the control source and add fields directly to the Design View of the report. Let us now go to the Create tab and click on the Report Design button. It will open a blank report or an unbound report, meaning this report is connected to no other object in our database. On the Design tab in the Tools group, select the Property Sheet. This will open up the Property pane. On the Data tab, assign a record source to this report, to connect it to a database object as in the following screenshot. Select qryLateProjects from the drop-down and now, the next step is to go through and add some fields to this report by clicking on Add Existing Fields list button on the Design tab. Select the fields as in the above screenshot. Drag the fields to you report as in the above screenshot. Go the Arrange tab, and in the Table group, you have a couple of options to choose from. There is a stacked layout and a tabular layout, which is a layout that is very similar to a spreadsheet. Let us select the tabular layout. You can see that it moves all of the labels up to the page header area. These labels will appear only once at the top of every page and the data query will repeat for every record in the Details section. Now, you can go through and make some adjustments to make your ProjectName field wider. As you can see in the above screenshot, there is a lot of space between Detail section and Page Footer. Let us drag the Page Footer up to reduce the space as in the following screenshot. We will now go to the Design tab and click on the View button and choose Report View. You can now see that some project names are not complete; you can adjust this with either the design view, or you can use the layout view to do that. That is how we create a simple report just from the Design View. 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 ”;