₦airaland Forum

It is always a good idea to give your reports a professional look by formatting it further under the Design view, using professional templates and adding pictures to help illustrate ideas. You may also need to preview, print and export the report. Once you’ve created the perfect report, it’s time to share it with rest of the world. Most commonly, you’ll choose to print it or preview it before you start distributing it online. In this tutorial part, I will give you the step by step guide on how to professionally format a report: add pictures and logos, print, preview, export the report to different supported available file formats in MS Access such as pdf, xps, rtf, xlsx, txt, html, doc, etc.

how to print, preview and export your reports to various formats in ms access

This is part 3 of chapter 7 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how you can print, preview and export your report to any format of your choice in MS Office Access.

In the previous part (Part 2) at http://www.microsofttut.com/2017/04/guides-to-create-mailing-labels-and-charts.html, I explained how to create mailing labels, charts and graphs in later versions of MS Access in detail. Also see the full tutorial course content of this Microsoft Access Tutorial Series at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

HOW TO ADD PICTURES TO REPORTS

If your data source tables include embedded pictures using the Attachment data type, you may then want to include them in your printouts. It is possible to show your pictures in a report (and even print them), provided you meet the following requirements:

• Your picture is stored in an attachment field.
• Your picture is stored in a standard picture format (like “.bmp”, “.jpg”, “.gif”, “.wmf”, etc.). If you have another type of file in an attachment field, you just see the icon of the related application (like Microsoft Word for a .doc file) in your report.
• Your picture is the first attachment. If you have more than one attachment, when you select the row

How to Print a Report in MS Access
Printing a report is easy—simply select the port in the navigation pane (it doesn’t even need to be open), and choose File > Print > Print. Access makes it easy with its integrated Print Preview feature. Note that in MS Access 2013 and later versions, you can print directly without making any changes using the Quick Print feature.

How to Preview a Report in MS Access
To get a preview of what your printed report will look like, select it in the navigation pane and choose File >Print > Print Preview. Or, if the report is already open, you can right-click the report tab title and choose Print Preview.

Print Preview mode doesn’t let you make any changes or select any part of the re- port. You’re limited to zooming in and out, and moving from page to page. When you’re finished looking at your print preview, choose Print Preview > Close Preview > Close Print Preview. Or, right-click the tab title of the preview window and choose a different report view.

NOTE:

Reports always use your standard paper size (which is usually 8.5 x 11 inches, or letter size) when you first create them. However, if you change the size, the new size setting is stored with the report. That means the next time you open your report, it still has the customized paper size. The same applies for the paper orientation setting.

Access has two extra options that are not provided in a normal datasheet print preview:

1. Print data only:
Click the Print Preview > Page Size > Print Data Only check box to produce a streamlined printout that leaves out details like column headers and titles. This option is rarely useful, because the resulting printout is harder to read.

2. Print multiple columns:
Click Print Preview > Page Layout > Columns to fit more report data on a page. This option works only if your report is much narrower than the page width. For example, if your report is less than half the width of the page, you can double-up by using two columns. You’ll need half the number of pages.

TIP:

You can change several of the page layout settings (like margins and paper orientation) without heading to the print preview. You’ll find the same buttons in the Report Layout Tools | Page Setup tab of the ribbon, which appears whenever you have your report open in Layout view.

MS ACCESS REPORT EXPORT FORMATS
Although MS Access supports many different formats for exporting a report, you’ll use just a few with reports. The useful formats for exporting reports include: PDF or XPS, Excel, Text file, HTML document formats, etc.

PDF or XPS:
This option lets you preserve your exact report formatting (so your report can be printed), and it lets people who don’t have Access (and possibly don’t even have Windows) view your report. It’s one of the most popular export options.

Adobe PDF Reader is Adobe’s popular format for sharing formatted, print-ready documents (in “.pdf” file extension). PDFs are used to pass around product manuals, brochures, and all sorts of electronic documents. Unlike a document format such as “.xlsx” (MS Excel file extension), PDF files are designed to be viewed and printed, but not edited.

The best feature of PDFs is that they can be viewed on just about any type of computer and operating system using the free Adobe Reader. You can download Adobe Reader at http://get.adobe.com/reader, but you probably don’t need to. Most computers already have Adobe Reader installed, because it comes bundled with so many different programs (usually so you can view their electronic documentation). It’s also used widely on the Web.

Apart from Adobe PDF Reader, there are other applications for viewing Print Document Format files. Microsoft’s Windows Vista and Windows 7 and later versions OS include an electronic paper format called XPS (XML Paper Specification). In time, as XPS is integrated into more and more products, it may become a true PDF competitor. But for now, Adobe PDF Reader is dramatically more popular and widespread, so it’s the one to stick with.

Word:
This option transforms your report into a document you can open in Word. However, the format Access uses is a bit clumsy. (It separates each column with tabs and each line with a hard return, which makes it difficult to rearrange the data after the fact in Word.) A nicer export feature would put the report data into a Word table, which would make it far easier to work with.

HTML Document:
This option transforms your report into a rich HTML document, suitable for posting on the Web or just opening straight from your hard drive. The advantage of the HTML format is that all you need to view it is a web browser (and who doesn’t have one of those?). The only drawback is that the formatting, layout, and pagination of your report won’t be preserved exactly, which is a disadvantage if someone wants to print the exported report.

NOTE:

When you export a report, Access does its best to stick to your page setup. For example, if your report takes 12 printed pages and you export it to Word, Access distributes the contents in exactly the same way over a 12-page Word document. If you export the same report to HTML, Access creates a separate HTML file for each page of the report. It also adds navigation links at the bottom of each page, so you can jump to the next, previous, first, or last page when viewing the report in a browser.

HOW TO EXPORT A REPORT TO A PDF OR XPS
Exporting a report to a PDF or XPS is slightly different than exporting it to any other format, due to the fact that PDF and XPS exporting ability started out as a separate plug-in (helper program) for Access 2007. This plug-in has been incorporated into MS Access 2010 and later versions. So you can create PDFs right out of the box.

To Export a Report to a PDF or XPS:
Click http://www.microsofttut.com/2017/04/how-to-print-preview-and-export-a-report-in-access.html to study full tutorial with screen to screen images.

MS Access allows you to create and format mailing labels through the Label Wizard which reduces your typing stress and facilitates your work and also create various types of chart and graphs through the charts wizard and include them in a report for easier data analysis. You can also format your reports and mailing labels which are part of reports. The most efficient way to build mailing labels is to create it using the Label wizard in Microsoft Access. In this tutorial part, I will explicitly explain and give you a step by step guide on how to create mailing labels and how to create charts of different types especially in MS Access version 2010, 2013 2016 and later versions which you may be finding difficult to create.

how to create a mailing label and design charts in microsoft access

This is part 2 of chapter 7 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how you can create a mailing label through the access label wizard and how to different kinds of charts for any version of MS Office Access you have on your pc.

In the previous part (Part 1) at http://www.microsofttut.com/2017/04/how-to-design-professional-reports-in-ms-excel.html, I explained how to learn and build databases in Access and MySQL through SQL in detail. Also see the full tutorial course content of this Microsoft Access Tutorial Series at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

CREATING MAILING LABEL IN MS ACCESS
Mailing label is one of the special operations that can be achieved using Microsoft Access. This operation is used to extract or retrieve some information or fields that are needed in hard copy format from an existing database table. It is mainly used by organization’s secretaries to lessen the volume of characters to type when sending bulk SMS and also saves the user or the secretary the troubles of retyping the information. The Labels feature or icon is located in the Reports group in Microsoft Access Create tab and is very similar to Reports but with minor difference as you will see shortly.

For example, assuming you are the secretary, the Manager of your company may tell you to send a memo for a meeting to all the staff in the company, instead of starting afresh to type the name and address of all the staff, you can use the mailing label in MS Access to extract the needed information from a database already containing workers bio data like the workers payroll system table, etc.

To Create a Mailing Label in MS Access:
1. First highlight the table or query you wish to use as the data source for the label from the Navigation pane. Referring to the above example, I will select the workers payroll system table.

2. Click Labels located in the Reports group under the Create tab. This prompts the report wizard dialogue box.

3. Specify the orientation of the label by choosing a suiting dimension for your label as shown below. The default setting is ok for use but you can also create a customized dimension for your label by clicking the Customize tab. Click Next.

select any label dimension of your choice

4. Specify the font type, font style, font size, font weight, text colour, etc. (the default selections are ok). Then click Next.

5. Select the fields needed in the mailing label. For example, first name, etc. use the > button to transfer the selected fields from the available fields: box to the prototype label: and then click Next.

6. Specify the sorting field for the mailing label and again use the > button to transfer it to the Sort by: box and then click Next.

7. Lastly, type a unique name for your mailing label, specify whether to see how the labels will look when printed or to further modify your label in the design view and click Finish.

8. MS Access extracts all the record in the selected fields and displays them based on your specified settings.

HOW TO CREATE A CHART OR GRAPH FOR YOUR DATASHEET DATA OR INFORMATION
A chart or graph is a graphical representation of information. Charts are normally used to summarize the relationship among data and to summarize all financial analysis. Some professionals like Data Analysts and Managers prefer graphical representation of data using chart to tabular representation for a more efficient comparison of information.

For example, you might want to chart the data from your sales report table, workers’ payroll system table, etc. You can create a chart and include it in your reports using data from a previously created table or query as your data source.

There has been a rumor that charts are no longer supported in MS Access 2013 and later versions, and that you have to first create it Excel or export your data to Excel. No you can still create your charts directly in MS Access 2013 and later versions but with limited features. If you want a more control to your charts, then create it in MS Excel, a perfect application for creating charts

To Create a Chart for your Data in MS Access 2013 and later Versions:
Click http://www.microsofttut.com/2017/04/guides-to-create-mailing-labels-and-charts.html to study full tutorial post with screen to screen images.

Microsoft Access database makes it so easy for you to create or design standard and professional reports using some previously created tables and queries as data sources and to present them in enticing formats using different methods. MS Access also gives you many options and ways of building the reports with less stress. In this tutorial part, I will explain all about report as one of the objects of a database and how to create reports using the various available methods.

step by step guides to designing professional reports in ms access

BUILDING STANDARD AND PROFESSIONAL REPORTS IN MS ACCESS
Report is one of the objects of Microsoft Access database that is used to prepare a database in a format suitable for print out. This is because you have control over the size and appearance of every item and you can display information the way you prefer using report object. It is also used to perform calculations.

This is part 1 of chapter 7 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain Microsoft Access reports an give you a step by step guide on how to create them using various methods.

In the previous chapter (Chapter 6) at http://www.microsofttut.com/2017/04/learn-sql-in-ms-access-mysql-databases.html, I explained how to learn and build databases in Access and MySQL through SQL in detail. Also see the full tutorial course content of this Microsoft Access Tutorial Series at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

Reports are a great way to organize and present data from your Access database. Reports enable you to format your data in an attractive and informative layout for printing or viewing on screen. Reports are often used to present a big-picture overview, highlighting main facts and trends. The data in a preview or in a printed report is static.

Reports merely present the data; they do not alter the underlying data in the tables. Each time a report is opened, Access displays the most recent data. I will advise you to first create a functional report before formatting it to give it a professional look.

COMPARING ACCESS REPORTS AND FORMS

Although there are many similarities when working with forms and reports, there are a few major differences also.

1. Reports only display data, whereas forms allow you to display and edit, add, and delete data.
2. Command buttons are often used on forms but are rarely used on reports.
3. Reports have slightly different design sections compared with forms.

The last bullet point is worth explaining in much greater detail. As you may recall from Chapter 3, forms have a Form Header, a Form Footer, and a Detail section. Anything in the Form Header displays at the top of the form, and anything in the Form Footer displays at the bottom of the form. The Detail section is where the actual form data display and where most of the form controls are placed.

UNDERSTANDING ACCESS REPORT SECTIONS

Reports are a bit more sophisticated when it comes to displaying and grouping data, so reports contain additional sections that provide this functionality. All reports have Report Header, Page Header, Detail, Page Footer, and Report Footer sections, and special reports that display grouped output will also have Data Header and Data Footer sections. In addition, a grouped report will have a separate Group Header and Group Footer for every grouping level of data.
Below are some of the sections of a report.

Report Header: Controls in this section will display at the very top of the first page of a report. This means that the report header is found only at the first page of a report in MS Access.

Page Header: Controls in this section will display at the very top of every page of a report. They will appear immediately after the Report Header on the first page of the report.

Group Header: This section appears on a report only if you display the report data in a grouped fashion. Every grouping level of data will have a separate Group Header named after the control being grouped. Normally, this section will contain a textbox to display the field you are grouping the report data on. The data result will appear only once for each unique grouping section.

Detail section: The detailed record-by-record data appear in this section of the report. If the report is being grouped, it will display every record within that group and then continue to the Group Footer section.

Group Footer: This optional section appears on a report only if you display the report data in a grouped fashion. Every grouping level of data will have a separate Group Footer named after the control being grouped. Normally, this section will contain a calculated control to summarize the records in the Detail section. Often, records for each group will be counted, averaged, or totaled.

After this section is displayed for one unique grouping, the report will return to the Group Header and then display the results for the next data grouping. The iteration over the Group Header, Detail section, and Group Footer continue until every unique data grouping is displayed.

Page Footer: Controls in this section will display at the very bottom of every page of a report.

Report Footer: Controls in this section will display on the last page of a report immediately after the last Detail section records and Group Footer results are displayed.

I will illustrate these report section when later while explaining how to design reports through the Design Report method in MS Access.

GETTING STARTED WITH REPORT DESIGNS AND FEATURES
Well-designed reports should be organized, grouped, sorted, and presented in a manner that is useful for the end user of the database. If you are building reports that other people will be using or viewing, it is critical that you involve them in the process of designing the report to ensure that it meets their needs. It is very helpful if the end user can provide you with an existing paper report that matches the format and design of the report you are building. If a report does not exist, you should work closely with the end user to develop a hand-written mockup of how the report should look. This will then become the basis for your design of the Microsoft Access report.

Once you have a paper design of the report, the next task is to figure out what table or tables your report data come from. If you need to display data from multiple tables in your report, you will first need to build a query, or if using the Report Wizard, Microsoft Access will build the query for you. That table or query is then assigned to the Record Source property of the report (just like forms).

AVAILABLE VIEWS OF A REPORT
The available views for the report as object of a database are:

1. Report view
2. Print Preview
3. Layout view
4. Design view

Each view has its distinct purpose, advantages, and disadvantages that are summarized here. While building database reports, you will often switch between these views.

Report View:

This view is used for viewing all of the report data, but it does not show page breaks. You cannot make any design changes in the Report View, but you can apply and remove data filters and instantly see how the output updates.

Design View:

This view provides the most powerful way to work on the design and layout of the report. In this view, you cannot see any of the report data.

Layout View:

This view is a combination of Report View and Design View. It enables you to make most design changes to the report while viewing live report data. It is very powerful because it instantly allows you to see how design changes will appear on the report with data. In this view you cannot edit the form data.

Print Preview:

This view enables you to see all of the report data and pagination exactly how it will appear when the report is printed. You cannot make any design changes to the report in Print Preview.

NOTE:

To see the available views for a report, open a report or create a new one and then click the View drop down arrow in the Views group under the Home tab as shown below.

Available view for MS Access reports

METHODS OF CREATING REPORTS
Reports are created from one or more tables or queries. To use several tables, you would first create a query to retrieve data from those tables.
There are three major method or ways of creating a report in MS Access. They include:

1. Auto Report Method
2. Through Design Report
3. Through Report Wizard

AUTO REPORT OR ONE-CLICK REPORT METHOD
This is a fast and automatic method that is used to create a report using default design. All you need to do is to highlight your data source and Access does the rest for you. It creates a basic report of the data in the current query or table to which you can add features such as groups or totals.

The one-click report generating approach gets you started in a hurry, but it’s not as convenient as it sounds. Most tables have quite a few fields, and an automatically generated report includes them all. The resulting report is often so wide that it stretches right off the edge of the page. You can remove and resize columns after you create the report to solve the problem), but you’ll also need to move the page number and resize the report in Design view. With all that trouble, it’s usually easier to create a blank report and then add the columns you want under the Design view.

To Create a Report through Auto Report (One-Click) Method:
Click http://www.microsofttut.com/2017/04/how-to-design-professional-reports-in-ms-excel.html to study full tutorial with screen to screen images.

Before an error message whether customized or default can be displayed, you must restrict what goes into a cell in your worksheet, so that when it is violated, the error message will be displayed. You should also set a custom input message for the cell or column to tell he user the type of data or number of characters to enter in that cell. In this tutorial, I will explicitly explain step by step with screenshots, how you can set a custom error message, input message and error alert for a cell, group of cells or column in MS Excel.

Guides on how to set up a custom message for a cell in ms excel

For example, as an MS Excel spreadsheet manager, you might want to design a product stock system for one of your clients. The system will have a PRODUCT DESCRIPTION column in which the number of characters for the description of each product must not be more than 50 characters. You will need to add a custom error message for that column through the Data Validation feature in Microsoft Excel. You can also set up input message to tell the user the type of data he or she is required to enter in that cell or column.

This is part 4 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 3) at http://www.microsofttut.com/2017/04/how-to-create-dropdown-menu-list-for-a-worksheet.html, I explicitly explained how to create a drop down menu list in MS Excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

To Set a Custom Error Message for a Column in your Excel Worksheet:

1. Prepare your worksheet like the one shown below.

The product stock system for the custom error message illustration

2. Highlight the entire column C by placing your cursor on the “C” label and clicking when you see the down pointing arrow.

3. Click the Data Validation drop down arrow located in the Data tools group under the Data tab. Then click Data Validation from the resulting dropdown menu option. This prompts the Data Validation dialogue box.

4. Under the Settings tab, click the Allow: drop down arrow and select Text Length from the resulting options. This activates the Source text box.

5. Select between from the Data drop down menu list.

6. Since the character should not be more than 50, type 0 in the Minimum text box and type 50 in the Maximum text box as shown below.

Specify the minimum and maximum allowed characters

You have restricted the number of characters that each cell under the PRODUCT DESCRIPTION column will accept. But if the specified number of characters are exceeded, MS Excel will pop up a default error message dialogue box that warns the user about wrong entry without telling the user how to correct the mistake.

So you need to set an input message that displays beside the selected cell and tells the user the type and number of characters accepted in that cell. But it is optional.
Then you need to customize the error message to advise the user on what to do.

To Set a Custom Input Message for a Cell or Column in Excel:

1. Highlight the desired cell or entire column and call up the Data Validation dialogue box using the above specified procedure.
Click http://www.microsofttut.com/2017/04/how-to-set-custom-error-input-message.html to study full tutorial with screen to screen images.

Creating a drop down list for a worksheet column is very useful especially if you have to type the same data or range of values into different cells of a worksheet over and over again. It makes you type less. When you set up a dropdown menu or list for your columns, MS Excel inserts a dropdown arrow beside each cell in that column. All you have to do is to click the dropdown arrow and select one of the options in the menu. In this tutorial, I will show you a simplified easy guide on how to create or remove a drop down list for a worksheet column.

Guides on creating a custom drop down menu or list in ms excel

For example, if in a sales record worksheet, the cells in the PRODUCT NAME column must contain one of the following items: keyboard, scanner, joystick, CPU and mouse, in order to save yourself from the stress retyping these items over again, you prepare a dropdown menu list for that column and then select any of the items from the menu using Excel data validation tool which allows you set some rules to limit the type of data that would be entered in a particular column or call.

This is part 3 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 2) at http://www.microsofttut.com/2017/04/how-to-insert-data-into-worksheet-using-data-entry-form.html, I explicitly explained how to insert data into a worksheet using the data entry form in MS Excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

To Create a Drop Down Menu for a Column in MS Excel:
1. Prepare your worksheet like the one shown below.

Sales report worksheet for the dropdown list illustration

2. Highlight the entire column B by placing your cursor on the “B” label and clicking when you see the down pointing arrow.

3. Click the Data Validation drop down arrow located in the Data tools group under the Data tab. Then click Data Validation from the resulting dropdown menu option. This prompts the Data Validation dialogue box.

4. Click the Allow: drop down arrow and select List from the resulting options. This activates the Source text box. Type the cell reference of the cells containing the list items. In the above illustration, the list items are contained in column D (cell D2 to cell D6). So type D2:D6 or click the quick selection bolt located at the extreme end of the textbox. Clicking this bolt takes you back to your worksheet.

Then highlight cells D2 to D6 and then click the minimized quick selection bolt again to return to dialogue box again. You will notice that Excel has automatically generated the absolute cell references (i.e. $D$2:$D$6) of the highlighted cells as shown below.

Data validation dialogue box

NOTE:

In a case where there is no column that contains the menu list item, you will type them directly under the Source text box separating each item with a comma and a space.

5. Check the check box which says: Apply these changes to all other cells with the same settings

6. Click OK to exit the Data Validation dialogue box.

Excel add a drop down arrow beside each cell in column B as shown below.
Sales with the added drop down menu

You will notice that each cell in column B, including cell B1 has a drop down arrow when activated. But you don’t want a drop down list for cell B1 because it contains the column title.

To Remove the Drop Down Arrow from the Cell Containing the Column Title:
Click http://www.microsofttut.com/2017/04/how-to-create-dropdown-menu-list-for-a-worksheet.html to study full tutorial post with screen to screen images.

A data entry or input form is just a way to quickly enter data into a cell or group of cells of a worksheet. It is mostly used when the Excel spreadsheet is too big or long for the screen. Instead of scrolling to and fro to enter data into the various columns and rows, you use a data entry form which relieves you of the scrolling stress.
Guides on how to insert data using the excel data entry form

For example, to insert records in a sales record worksheet for the month of January which has a column for each day of the month, you will have move to and fro, but a data entry form makes the data entry process faster, easier and less stressful.

The Data input Forms option is normally located from the Quick Access tool bar. But by default, the Data entry Form is hidden and needs to be called out. When it is called out, the icon resides in the Quick Access toolbar. The Quick Access toolbar located above the tab bar as shown below.

The quick access tool bar

This is part 2 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 1)at http://www.microsofttut.com/2017/04/how-to-create-custom-excel-worksheet-template.html, I explicitly explained how to create a custom worksheet template in MS Excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

HOW TO CALL OUT THE DATA ENTRY FORM ICON AND CUSTOMIZE THE QUICK ACCESS TOOL BAR IN EXCEL

Customizing the Quick Access tool bar helps you to locate and position several options and commands such as the data form command icon, which are not available in the MS Excel Quick Access tool bar and tabs by default. It also allows you to customize each tab and call out tabs like the Developers tab and the Add-Ins tab which are hidden by default. All these are available in the Excel Option dialogue box.

To Call Out the Data Input or Entry Form Icon In any Version of MS Excel:

1. If you are using MS Excel 2007 and lower versions, click the Office button located in the extreme top left of the Excel workbook screen. Then click Excel Option which is the last option in the resulting menu list.

If you are using MS Excel 2010 and later versions, click the File menu or tab and then click Options. This prompts the Excel Option.

2. If you are using MS Excel 2007 and lower versions, click Customize located on the left menu.
If you are using MS Excel 2010 and later version, click the Quick Access tool bar option on the left section of the dialogue box as shown below. The rest of the steps are the same for all versions of MS Excel.

3. To add the Data Form option to the Quick Access toolbar, click the Choose Commands From drop down arrow and select Commands Not in the Ribbon from the resulting menu options.

4. Select Form from the resulting option and click the Add>> button located in between the second and the third column of the dialogue box as shown below.

Click http://www.microsofttut.com/2017/04/how-to-insert-data-into-worksheet-using-data-entry-form.html to study full tutorial with screenshots.

Creating or designing a custom worksheet template in MS Excel is simple and helps to save a lot of time and stress especially if you have to create different worksheet of the same format over and over again. Using this, operation, you prepare the first worksheet and save it as a template. You can create different worksheet templates for different purposes like the payroll, the Students’ grade list, Sales record and report, semester GPA calculation, loan payment or amortization worksheet templates, etc. You can also select from any of MS Excel’s pre-designed templates. I will also show you how to customize the Quick Access tool bar and tabs in Microsoft Excel.
Easy guides on creating a custom MS Excel worksheet template

CREATING CUSTOM WORKBOOK AND WORKSHEET TEMPLATES IN MS EXCEL
Now, I will give you a step by step guide on how to create a custom worksheet template for any version of Microsoft Excel. Note that Excel custom templates are saved with the file extension “.xltx”.

You can also create a worksheet or workbook template from some default Excel Workbook templates as you will see below. Some of the pre-designed MS Excel templates in Excel 2013 include:
Billing Statement, Blood Pressure Tracker, Expenses Report, Loan Amortization, Personal Monthly Budget, Sales Report, Time Card. But you can get more updated Excel templates at https://templates.office.com/en-us/templates-for-Excel.

This is part 1 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous chapter (chapter 7) at http://www.microsofttut.com/2017/04/learn-what-if-analysis-tools-scenario-manager-and-data-tables.html, I introduced the what-if analysis tools and explained how use the scenario manager and data tables in ms excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

To Create a New Custom Worksheet Template in MS Excel:
1. Open a new workbook.
For those using Excel 2007 and lower versions, you have three workbooks by default. Delete two of the default worksheet (probably Sheet 2 and Sheet 3).

For those using Excel 2013 and newer versions, you don’t have to border because you have only one worksheet by default.

To see the difference between a workbook and a worksheet, go to chapter 1: part 2 at http://www.microsofttut.com/2016/06/chapter-1-part-2-user-interface-and.html.

2. Design the worksheet inserting the static data (data common to all the worksheet that you will create from this template).

For example, a template for the monthly sales record will contain titles like: PRODUCT ID, PRODUCT NAME, QUANTITY BOUGHT, COST PRICE, QUANTITY SOLD, SALES PRICE, QUANTITY IN STOCK, etc.

Leave columns that will contain dynamic data (data that will vary from worksheet to worksheet like products name, sales price, cost price, etc.) empty.

3. Insert the formulas for calculating QUANTITY SOLD in the cell formed by the intersection of the first record and the QUANTITY SOLD column using their cell references in the formula.

For example, in the worksheet sample shown below, the QUANTITY SOLD is in column E, QUANTITY BOUGHT is in column C and QUANTITY IN STOCK is in column G of the worksheet, then the formula for calculating the QUANTITY SOLD would be: QUANTITY BOUGHT – QUANTITY IN STOCK.

So place your cursor in cell E3 which is the intersection of column E and row 3 (the first record) and type the following formula: =C3 – G3 then press the Enter key.

A sample of a monthly sales report is shown below.

From the sample MS Excel worksheet template above, the static data columns are the PRODUCT ID column (A) and the dynamic data columns are the PRODUCT NAME column (B), the QUANTITY BOUGHT column (C), the COST PRICE column (D), the QUANTITY SOLD column (E), SALES PRICE column (F) and the QUANTITY IN STOCK column (G).

4. For the QUANTITY IN STOCK column, type the following formula in cell G3:
=C3-E3 then press the Enter key. A circular reference warning dialogue will appear. Just click OK.

5. Click the File tab, then click Save As. Then specify the save location. This prompts the Save As dialogue box.

6. Type in a unique file name for the worksheet template in the File Name text box.

7. Click the drop down arrow at the extreme right of the Save As Type drop down menu and select the Excel Template option from the menu option as shown below.

Select the file format and click save

8. Click the Save button and the template file will be saved to a default folder called Custom Office Templates.

To Make Use of your Newly Created Custom Excel Worksheet Template:
Click http://www.microsofttut.com/2017/04/how-to-create-custom-excel-worksheet-template.html to view full tutorial post with images.

The Goal Seek tool is one of the powerful tools of MS Excel used to determine the correct value for one of the arguments of a function that yielded or returned a particular result or answer. It works on the basis of what-if analysis and can also be used to solve practical business problems such as word problems in Mathematics, etc. In this tutorial, I will give you a step by step guide on how you can manipulate and use the MS Excel Business tool: the Goal Seek tool to solve business problems. Then I will give you some practical exercises on what-if analysis to test your knowledge.

step by step practical tutorial on how to use the goal seek tool in ms excel

One of the practical problems that can be solved using Goal Seek goes like this: If 720 is the answer gotten by multiplying 40 by a certain number, what must be this number or figure?

This is part 3 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 2) at http://www.microsofttut.com/2017/04/learn-what-if-analysis-tools-scenario-manager-and-data-tables.html, I introduced the what-if analysis tools and explained how use the scenario manager and data tables in ms excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

SOLUTION THE ABOVE PROBLEM USING THE GOAL SEEK TOOL IN MS EXCEL

To get the correct number using the Goal Seek tool, prepare your worksheet like the one shown below.

First goal seek worksheet

Type 40 which is the known or correct variable in cell B2, then type any number of your choice for the unknown or incorrect variable in cell B3. Then for the expected answer, type the following answer in cell B4: =B2*B3 and press the Enter key.

Click the Data tab, then click the What-if Analysis drop down arrow located in the Data tools group.

Then Select Goal Seek from the resulting options.

This prompts the Goal Seek dialogue box. Enter the values and cell references as shown below.

MS Excel requires you to provide three parameters or values in the 3 text boxes shown above. The required parameters are:

1. SET CELL: This is the cell that contains the formula that MS Excel uses for the Goal Seek analysis. In the case of the above example, the set cell is cell B4 (the absolute cell reference is $B$4).

2. TO VALUE: This cell holds the desired answer. In this case your desired answer is 720.

3. BY CHANGING CELL: This cell holds the cell reference of the cell containing the incorrect or unknown variable. In this case, its cell B3 (the absolute cell reference is $B$3).

Click OK twice. Your worksheet should now resemble the one shown below with cell B3 now containing the correct value for the specified answer in cell B4.

A MORE PRACTICAL ILLUSTRATION OF GOAL SEEK
Now let me illustrate Goal Seek in MS Excel with a more practical step by step example.

QUESTION:
Joe-Links Business center made a total profit of $100000 as at December 2016. The company’s Manage proposed a profit of $180000 for the year 2017. Assuming they offer 50 services, at what price should they offer each of their services to meet up with the proposed income?

SOLUTION:
Prepare your MS Excel worksheet like the one shown below.
Click http://www.microsofttut.com/2017/04/how-to-use-goal-seek-tool-in-ms-excel.html to study full tutorial on goal seek with screen to screen images.

What-if analysis is one of the most important benefits of Microsoft Excel as a spreadsheet application. Performing what if analysis means changing the values of certain key variables in a formula or worksheet. It can be performed using some of MS Excel’s powerful tools such as Scenario Manager, Goal Seek and Data Tables. In this tutorial, I will give you a step by step guide on how you can manipulate and use these MS Excel Business tools: Scenario Manager and Data tables in various fields.

step by step guide to learn what if analysis tools - scenario manager and data tables

SCENARIO MANAGER
Scenarios Manager is one of MS Excel’s powerful what-if tools used to substitute values automatically in a worksheet. It is used to calculate loan amortization on what if basis. An example of a logical question that Scenario Manager that Scenarios answer are:

If I decide to pay back a 12 year $1000000 loan within 10 years on monthly basis, what then will be my monthly pay to amortize the loan and what will be my total payback amount?

This is part 2 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part at http://www.microsofttut.com/2017/04/how-to-calculate-loan-payment-using-pmt-function-in-excel.html, I explained How to use the PMT function to calculate loan payment or amortization. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

FUNCTIONS OF SCENARIOS
1. It is used in loan amortization analysis.
2. Scenario Manager is used to adjust values automatically in a worksheet.
3. It is used to substitute values such as Loan amount, Interest rate and Period automatically.
4. Scenario is used to summarize loan amortization.
5. It enables the user to calculate the monthly pay, total pay and bank profit at the end of each period.

GENERAL STEPS TO CREATE A SCENARIO SUMMARY IN MS EXCEL
Now, I will give you the step by step guide to create a summary for loan amortization or payment using the scenario manager in Microsoft Excel. I will also illustrate it further with a practical example.

To Create a Scenario Summary for Loan Payment in MS Excel:

1. Prepare the loan amortization table and calculate the monthly pay, total pay and bank profit using the PMT function as illustrated above.

2. Click the Data tab, then click the What-if Analysis drop down arrow located in the Data tools group. Then Select Scenario Manager from the resulting options as shown below.

3. This prompts the Scenario Manager dialogue box. Click Add and then type in the scenario name or year.

4. Specify the Changing cell that is the loan amount, interest rate, and period and click OK.

NOTE:
You either type the cell references of the changing cell or click bolt icon at the extreme right to make a selection and then click on the bolt icon again to commit your selection as shown below.

5. Next, enter a value for each of the changing cells and click Add.

6. Repeat steps 3 to 5 for other set of values given. You can always add, edit, delete or merge any set of value by selecting the appropriate action button as shown below.

7. Click Summary and specify the Result cells that is the monthly pay, total pay and bank profit

8. Click OK.

DATA TABLES FOR WHAT IF ANALYSIS
Data tables help you to see results of multiple inputs such as scenario inputs at the same time. It is used to compare different data set output in MS Excel and for scenario summary.

TYPES OF MS EXCEL DATA TABLES
There are two types of data tables that can be used for what-if analysis. They include:

Click http://www.microsofttut.com/2017/04/learn-what-if-analysis-tools-scenario-manager-and-data-tables.html to study full tutorial on what if analysis with screen to screen images.

When you include multiple data sources in a query, you use JOINS to limit the records that you want to see based on how the data sources are related to each other. You must use JOINS to combine records from both data sources, so that each pair of records from the sources becomes one record in the query results. JOINS in MS Access is just the same as any other Relational Database Management System (RDBMS) like MySQL

You can also join queries in the same way that you join tables and can also join both tables and queries. Joins behave similarly to query criteria in that they establish rules that the data must match before they are included in the query operations. Unlike criteria, joins also specify that each pair of rows that satisfy the join condition will be combined in the record set to form a single row.

I explained table joins or relationship in chapter 2 (part 2) at http://www.microsofttut.com/2016/07/table-operations-and-relationshipsjoins.html. You may need to revise it because it is a prerequisite to Join Queries.

This is part 3 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to create a JOIN Query in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions. I will also explain object joins or relationship and then give you some practical exercises.

In the previous part (Part 3) at http://www.microsofttut.com/2017/04/practical-application-of-sql-in-ms-access-mysql.html, I explained UNION query SQL in MS Access and MySQL in detail. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

TYPES OF QUERY JOINS

There are many types of query joins, but I will discuss only the five most common JOIN types. They include:
1. Inner Join
2. Left Outer Join
3. Right Inner Join
4. Full Outer Join
5. Cross Join

NOTE:

To create query joins in MS Access:
Create an empty SELECT query through the Query Design option. Then switch the empty SELECT query over to SQL view where you can type your SQL statements directly.

INNER JOIN:
Inner Join shows only rows where a common value exists in both of the joined tables. They are the most common type join. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be returned. For example, the tables below are used for storing information of emergency patients and those of regular patience in a Health Clinic. The first table is named emergency_patients and the second, regular-patients.
Emergency patients table

Regular patients table

Referring to the two tables above, if you want to show the ID and HOME TOWN fields and return only the records of emergency patients that are also regular patients, then your inner join query SQL would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E INNER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns four records that appear in both tables as shown below.
Also note how I aliased the table names. Note also, the ON clause used here in place of the WHERE clause, you have Join Queries.

Inner join query result

LEFT OUTER JOIN:
When a query with a left outer join is run, it returns all the records from the first (left) table and the corresponding records from the second table that have a matching value in the join field of the first table.

For example, still referring to the above two tables, if you want your query to show the ID and HOME TOWN fields and return all the records of emergency patients, including the records of regular patients that are emergency patients, then your left join SQL statement would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E LEFT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns 6 records as shown below.

Left outer join query result

Two records returned in the screenshot above have empty data under the HOME TOWN field because the emergency_patients table don’t have the HOME TOWN field. So the query returned empty spaces.

RIGHT OUTER JOIN:
When a query with a right outer join is run, it returns all the records from the second (right) table and the corresponding records from the first table that have a matching value in the join field of the second table.

For example, still referring to the above two tables, if you want your query to show the ID and HOME TOWN fields and return all the records or regular patients, then your SQL statement should resemble this:
Click http://www.microsofttut.com/2017/04/how-to-create-join-queries-in-ms-access.html to read full post with screen to screen images.

I have been so theoretical since the introduction of this SQL chapter for MS Access and MySQL Databases. Now let me show you the real life or practical, real life and business applications of SQL (Structured Query Language) that I have been explaining. I will apply SQL to build these two types of advanced query in MS Access and MySQL databases, namely:
1. THE UNION QUERY
2. THE QUERY JOIN

THE UNION QUERY:
This just like the table relationship/join I explained in chapter 2. This query lets you combine the results returned by two SELECT statements or queries and displays them as one result. In table join, MS Access does all the underground work for you. All you just did then was to click and Access generates the SQL statements without your knowledge. But now, you will generate everything from the scratch. That is what makes it an advanced query.

This is part 3 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain the real life or business application of SQL in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.

In the previous part (Part 2) at http://www.microsofttut.com/2017/04/full-explanation-illustration-of-sql.html, I explained all SQL clauses and Keywords in MS Access and MySQL in detail. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

NOTES:
The UNION query joins two or more SELECT queries together, also, the SELECT statements that you combine in a union query must have the same number of output fields in the same order and with the same or compatible data types.

Also note that the Text data type and the Number data type are compatible for the purpose of union query.

You have to switch to the SQL view in MS Access to create a union query because a union query is SQL –specific.

The basic SQL syntax for a union query that combines two SELECT query is:

SELECT [field_1], [field_2],… FROM [table_1], [Table_2],…
UNION ALL
SELECT [field_a], [field_b],… FROM [table_a], [Table_b],…

NOTE:

The ALL keyword is optional. When you use the ALL keyword, duplicate rows are not removed from the combined set that is produced by the UNION query. You can also use it when you are sure that the SELECT statements will not produce any duplicate rows or if you want to see duplicate rows.

The UNION keyword combines the results returned by two SELECT statements or queries and displays them as one result.

HOW TO CREATE A UNION QUERY IN MS ACCESS
There are two basic methods of creating UNION queries in Microsoft Access:
1. Combining two SELECT queries previously created in Query Design view
2. Creating the UNION query directly in SQL view

COMBINING TWO SELECT QUERIES PREVIOUSLY CREATED IN QUERY DESIGN VIEW
This method becomes the alternative if you have previously created the two SELECT queries and then wish to combine or merge their results.

For example, as an Administrative Head of a University, you might want to merge or join two queries named old students record and new students record to form one query. Since you already have the two queries available, you will use this alternative to create the JOIN query.

To Combine Two Previously Created SELECT Queries Using the UNION Keyword:
1. Open the first SELECT query in SQL view and copy the SQL statement. Referring to the above example, I will first open the new students records SELECT query in SQL view and copy the SQL statement as shown below.

Click http://www.microsofttut.com/2017/04/practical-application-of-sql-in-ms-access-mysql.html to study full post with screen to screen images.

It is good you get used to the various SQL clauses and Keywords in Microsoft Access and MySQL Relational Database Systems as it will hasten your rate of understanding of SQL as a whole. Here, I will explain the six most commonly used SQL clauses and Keywords in MS Access and MySQL databases. I will also explain some SQL keywords in detail and illustrate how they are used in MS Access and MySQL databases.

Detailed explanation and illustration of SQL clauses

These SQL clauses include:

1. SELECT clause
2. FROM clause
3. WHERE clause
4. ORDER BY clause
5. GROUP BY clause
6. HAVING clause

This is part 2 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to learn SQL in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.

In the previous part (Part 1) at http://www.microsofttut.com/2017/04/learn-sql-in-ms-access-mysql-databases.html, I explained How to build advanced queries and action queries in MS Access. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

SELECT CLAUSE:
It consists of an operator followed by an identifier. A SELECT clause lists the fields that contain the data you wish to retrieve from a database. You can use square brackets ([]) to enclose the name of a field or identifiers in a SELECT clause. If the field name(s) does not contain spaces or special characters, the square brackets are optional. But if the field name(s) contains spaces or special characters, then you must enclose it in a pair of square brackets.

NOTES:
A name that contains spaces is easier to read and can save you time when designing forms and reports, but may make you type more when writing SQL statements. This fact should be considered when naming objects in a database.

If your SQL statement has two or more fields that have the same name but from different tables or data sources, then you must add the name of each field’s data source to the field name. The data source name is the identifier in the FROM clause of the SQL statement.

When you want to include all fields from a data source, you can either type them individually in the SELECT clause or use the asterisk wild character (*). For example, to include all the fields the customer table, you can type: customer.*.When you use the asterisk, Access determines when the query is run, which fields the data source contains and includes all those fields in the query. This helps to keep the query up to date if new fields are added later to the data source.

You can the asterisk with one or more data sources in SQL statement. If there are multiple data sources and you wish to include asterisk with only one of the data sources, then you must include the data source name together with the asterisk, so that MS Access can determine the right data source to apply the asterisk.

For example, suppose you want to select all the fields from customers table and then select only the order_dates field from the orders table, your SELECT clause should resemble this:
SELECT customers.*, oders.[order_dates];

WARNING:
Do not use the asterisk character when you intend adding new field which are not relevant to the query in the data source table, else you get undesired query results.
SELECT STATEMENT KEYWORDS

Some of the common SELECT statement keywords are:
1. THE “DISTINCT” KEYWORD:
If your data source field contains duplicate data and you want your query to return each of these duplicate data once, you add the DISTINCT keyword to your SELECT clause. For example, suppose you want your query your return distinct years from the order_year field of your order table, your SELECT clause should resemble this:
SELECT DISTINCT order.[order_date];

2. THE “AS”KEYWORD:
This is used for substituting names for field names or expression. You can change the label that is displayed for any field by using the AS keyword and a Field Alias.

A field alias is a name that you assign to a field in a query to make the result easier to read. For example, if you want your query to return data from the customer_phone field, which contains customer’s phone numbers, you can improve the readability of your query result by using a field alias in your SELECT statement as follows:
SELECT [customer_phone] AS [Customer Phone Number]

NOTE:
You must use a field alias when you use an expression in a SELECT clause.

USING A FUNCTION, AN EXPRESSION OR AN AGGREGATE FUNCTION AS AN IDENTIFIER IN A SELECT CLAUSE
Sometimes, you might want to retrieve only a part of a field’s data. For example, suppose that you want your query to return only the birth year of your customer’s from the birth_date field, then your SELECT clause might resemble this:
SELECT DatePart(“yyyy”, [birth_date]) AS [Birth Year]

This expression contains the DatePart function as an identifier and two arguments:
“yyyy” (a constant) and [Birth_date] (an identifier).

NOTES:
You can use any valid expression or function as an identifier in a SELECT clause provided it outputs a single value when given a single input value.

FROM CLAUSE:
Click http://www.microsofttut.com/2017/04/full-explanation-illustration-of-sql.html to study full post with screen to screen illustration.

SQL is used to retrieve data from a database. SQL is simply a computer language (closely resembles English language) that database programs (DBMS) or applications like Microsoft Access understand and MySQL. The knowledge of SQL is important because every query in MS Access is first translated to its equivalent SQL before it is being process by the database engine. Understanding how SQL works can help you to create better queries and makes it easier for you to fix, debug or manipulate a query in order to obtain your desired output.
step by step guide to learning sql in access and mysql databases

WHAT IS SQL?
SQL as an acronym means “Structured Query Language”. It is a computer language for working with sets of facts and the relationship between them. Relational Database Management Systems (RDBMS) such as MS Access, MySQL, etc. use SQL to work with data.

Like many other computer languages, SQL is an international standard language recognized by standard bodies such as ISO and ANSI.

This is part 2 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to learn SQL in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.

In the previous chapter at http://www.microsofttut.com/2017/04/how-to-build-and-manipulate-parameter-queries-in-access.html, I explained How to build advanced queries and action queries in MS Access. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

When you use SQL, you must use the correct syntax or statement. A syntax or statement is a set of rules by which the elements of a language are correctly combined to achieve a particular result. SQL syntaxes are based in English syntax and uses many of the same elements as Visual Basic for Application (VBA) syntaxes.

NOTE:

You really need to be grounded in MS Access Advanced Queries before you can flow in SQL because the latter is more complex and needs a deeper understanding of its pre requisite which is Advanced Queries. I advise to revise the previous chapter (chapter 5 – Advance Queries) before you continue.

WHERE TO TYPE SQL STATEMENTS IN MS ACCESS
Microsoft Access has SQL environment (view) or interface where you can type SQL statements directly without having to first write in design query view.

To Access the SQL view in MS Access:
1. Launch MS Access and open previously created database or create a new one if you wish.
Click http://www.microsofttut.com/2017/04/learn-sql-in-ms-access-mysql-databases.html to study full tutorial with screen to screen images

Like I said earlier in part 1 of this chapter at http://www.microsofttut.com/2017/03/how-to-build-advanced-action-queries-in-ms-access.html, there are queries that are better built or designed through MS Access query wizards. The main 3 queries that I will advise you to build through the query wizards are:
1. Find Duplicates Query
2. Find Unmatched Query
3. Crosstab Query

You can also build these 3 queries through the query design but it will be very tedious. Also it is very difficult to debug their errors. But the Microsoft Access Query wizard simplifies the design steps for you and also reduces the possible errors.

You can also build simple select query through the query wizard, but building it through query design gives you more flexibility because it allows you to construct everything from the scratch.

Other queries like the four action queries (make table, update, append and delete queries), parameter queries are better and easier to build through query design.

I will now show you how you can utilize MS Access query wizard to easily build the Find Duplicates query, Find Unmatched query and Crosstab query. In part 1 at http://www.microsofttut.com/2017/03/how-to-build-advanced-action-queries-in-ms-access.html, I explained action queries in detail. See also the other MS Access topics I have explained in the previous chapters at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

FIND DUPLICATES QUERY
This type of advanced query helps you find records with duplicate field values in a single table or a previously created query. When using MS Access query wizard to create the find duplicates records query, you will be required to specify the field or column on which the query filter process will be based i.e. the field that could contain the duplicate field values.

For example, a university registrar may want to filter out the records of students that will graduate in a particular date or the dates where more than one student will graduate. In either cases, he will specify the REG DATE field in the query wizard as the field that will contain the duplicate values.

To Build the Find Duplicates Query through MS Access Query Wizard:
1. Click the Create tab and then click the Query Wizard icon located in the Queries group.

2. This prompts the New Query Wizard dialogue box. Select Find Duplicates Query Wizard, then click OK as shown below.

3. Next, select the table that may contain duplicate values. Referring to the above example, the name of the table is Students Record, then click Next.

4. Select the field(s) that may contain duplicate field values or information. Also referring to the above example, I will select the REG DATE field because it is the field that has the duplicate registration date values.

5. With that field selected, click the single forward pointing arrow button (> to move the selected field from Available fields to Duplicate-value fields, then click Next as shown below.
Click to transfer the duplicate value fields

6. Select the other fields that you would like the find duplicates query to display in addition to the duplicate value fields you selected earlier. You can select all the remaining fields if you wish. Then click Next as shown below.
Select other fields to display in addition to the duplicate value fields

7. Type a unique name for the query, Specify whether to view the query or modify the query design further, then click Finish as shown below.
Enter a name for your find duplicates query

8. MS Access runs the query and displays the records for you. Check whether the find duplicates query returned the desired result, then close the query.

FIND UNMATCHED QUERY
This type of advanced query helps you find records in one table that have no related records in another table. The Find Unmatched Records query compares the two table and filters the records that have no related record in the order table. This means that both tables must have a matching field in which MS Access will base its comparison and filter process in the query wizard.

For example, a university registrar has been making use of two similar tables namely: Students Former List and Students Recent List to store the records of new students and he knows that the records of some set of students are contained in both tables. If he wants to filter or extract the records of the other students that are not duplicated in the two tables, then he must use the Find Unmatched Query. The two tables are shown below.

Students’ former list table

Students’ current list table

The 2 tables have the same structure. Now, studying the two tables closely, which field do you think could best serve as the matching field?

Answer: The REG NUM field of course. This is because 2 different students other information may coincide, but the registration number can never be the same

To Build the Find Unmatched Query through MS Access Query Wizard:
1. Click the Create tab and then click the Query Wizard icon located in the Queries group.

2. This prompts the New Query Wizard dialogue box. Select Find Unmatched Query Wizard, then click OK as shown below.
Click find unmatched query wizard
3. Next, the find unmatched query wizard asks you to specify the first table or query that contains records you want in the query results. You can specify any of the tables first. So referring to the example above, I will select the Students Former List table. Click Next as shown below.
Select the first table that contains desired records
4. Next, you specify the other table or query that contains the related records. In my case, I will now select the Students Current List table. Click Next as shown below.

Select the second table that contains related records

5. Now you have to specify the matching fields in both table. These fields must have the same data type and must contain similar or related data or information. Referring to the above example, the matching field is the REG DATE field, so I will select this field in both table. Click the Matching fields button in between the two tables, then click Next as shown below.

Click the matching fields button

6. Next, select the other fields you would like to see in the find unmatched records query results and click Next.

7. Type a unique name for your new find unmatched records query. Specify if you would like view the query results or to modify it further in the query design view. Then Click Finish as shown below.
Type a name for your unmatched query

8. MS Access now runs the query and displays the records that do not match in both tables as shown below. Cross check the query results to see if it returned the desired records. Then close the query.
Find unmatched query result

CROSSTAB QUERY
This type of action query is used to calculate and restructure data for easier analysis. Crosstab query can be used to calculate a SUM, AVERAGE, COUNT or other types of total functions for data that are grouped by two types of information. When using the crosstab query, you have to specify the Row Heading field(s), the Column Heading field and the Calculation field.

Click http://www.microsofttut.com/2017/04/how-to-build-advanced-queries-through-query-wizard.html to study full tutorial with screen to screen images.

The deeds of mercy are those actions you take in order to help your fellow man either spiritually (spiritual works of mercy) or physically (corporal works of mercy). Deeds of mercy involves using your God-giving talent to draw souls fallen souls to God by showing them acts of kindness, pity, generosity, etc.

In this article, I will explain the corporal and spiritual works of mercy and also reflect on these deeds of mercy.

SEE ALSO: DREAMS, MEANING AND CONSEQUENCES at http://www.myhealthcrave.com/2017/02/dreams-meanings-and-consequences.html

There are two main categories of the deeds of mercy. They include:
1. The Corporal Works of Mercy
2. The Spiritual Works of Mercy

WHAT ARE THE CORPORAL WORKS OF MERCY?
They are the deeds of mercy that involve using the physical gifts God has given you to help others especially those in need of material things. Some of these physical gift may be your material wealth, act of kindness, etc. There are seven corporal works of mercy.

NOTE:
Please don’t just read these deeds of mercy, meditate on them.

The Corporal Works of Mercy are:

1. Feeding the Hungry:
Reflection: Have you ever given some food to those your poor neighbours or do prefer to discard excess food items instead of giving it to the less privilege? Don’t see yourself as a poor man. Even if you are poor, you are better than some paupers. Help them with the little you have.
2. Giving Drink to the Thirsty:
Reflection: Have you ever saved a soul dying of lack of water
3. Clothing the Naked:
Reflection: You change your wardrobe from time to time. Do you know that some poor people have only one cloth? Why not give them some?
4. Sheltering the Homeless:
Reflection: Have you ever welcome a stranded stranger in your house before? I tell you, some people have welcome God’s Angels by their acts of hospitality. Abraham once entertained Angels unknowingly through his acts of benevolence. Why not try it today?
5. Comforting Prisoners:
Reflection: If only you know how lonely and discouraged those in prisoners are, you would not spend a full day without visiting them. How many times have you visited those in prison?
6. Visiting the Sick:
Reflection: Do you know that just your visit to a sick person reliefs him or her of some pains? Always visit your sick friends and neighbours. Pray for them and buy some drugs for them.
7. Burying the Dead:
Reflection: Do you know that attending the burial of a diseased is the last respect you give him or her? Always try as much as possible to attend burial of some of your dead friends and relatives.

WHAT ARE THE CORPORAL WORKS OF MERCY?
This category of are the deeds of mercy that involve using the spiritual gifts God has given you to help others especially those lagging behind spiritually. Some of these spiritual gifts include: convincing tongue, prayerfulness, etc. There are seven spiritual works of mercy.

The Spiritual Works of Mercy include:

1. Admonishing Sinners:
Click http://www.myhealthcrave.com/2017/03/reflection-on-deeds-of-mercy-corporal.html to read full article

In this chapter 5 tutorial, I will explain the various advanced queries in MS Access and other RDBMS (such as Mysql), such as the four Action queries which include: the Make Table query, Append query, Update query and Delete query, then I will go over to Parameter query, Crosstab query, Find Duplicate query, Find Unmatched query, etc.

These advance queries can also be built directly in MS Access SQL view, but I don’t want to bother you with much of codes until I explain SQL in the next chapter because it is a broad topic and can be cumbersome if not properly explained. So in chapter 6 (introduction to SQL), I will revisit some of these advance queries and show you how they can be built directly in SQL view.

There is another type of query called the UNION query, but it is too complex for this chapter, I will explain it in the chapter 6 (under SQL).

This chapter 5 of this MS Access tutorial has 3 parts. This is part one and I will show you the in and out of action queries in RDBMS (Relational Database Management System) like Mysql. In part 2, I will explain how to build Find Duplicate query and Find unmatched query. Then in part 3, I will explain how to build parameter query and crosstab query in MS Access 2013 and 2016.

In the previous chapter (chapter 4) at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html, I explicitly explained how to build simple select queries, the various methods of filtering a database in Microsoft Access, various query functions and expressions, then lastly I illustrated the practical applications of all these in Student’s result (Grade list) system and Workers; payroll system.

BUILDING ACTION QUERIES IN MS ACCESS
Action queries are queries perform specific operations and can be used as an efficient tool for making modifications to bulk data. They can also be used to change, retrieve, and display data. Before creating an action query, it is always advisable that you first run a select query to determine how the action query will affect your records and the appropriate criteria to set in order to get a desired result.

TYPES OF ACTION QUERIES
There are 4 types of action queries which include: Make Table query, Append query, Update query and Delete query. Each of them is used to carry out a specific action on a database.

MAKE TABLE QUERY
This type of action query creates a new table from all or part of the data in other table(s) based on the specified criteria.

For example, in a database table that contains records of students, both male and female, you can filter the male students from that existing table using a simple select query and then use a make table query to create a new table for the male students using the records the simple select query returned.

To build a make table query:
1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s) you wish to include in the simple select query. Then close the Show Table dialogue box.

3. Select the fields that you would like to include in the query. Referring to the example above, you can select all the fields.

4. Enter your query criteria in the Criteria row. Referring to the example above, I will type "Male" (in double quotes) under the SEX column as shown below.

Simple Select query for the make table query

5. Click the Design tab, then click the Make Table icon located in the Query Type group. This prompts the Make Table dialogue box.

6. Type a name for the new table. In my case here, I will type “Male Students Record” (without quotes)

7. Next you have to specify where you want MS Access to save your new table as shown below. You have 2 options. You can either select Current Database or Another Database. If you select the later, then you have to specify the location and name of the database. Click OK as shown below.

Make table dialogue box

8. Click the Run icon located in the Results group.

9. A dialogue box pops up asking for confirmation of the action you want take. Click Yes to copy the results of the simple select query to a new table as shown below.
Make table confirmation dialogue
MS Access pastes or appends the matching records to the specified table.
APPEND QUERY
This type of action query adds a group of records from one or more tables to the end of another table(s) based on the specified criteria. It is very similar to the make table query.

For example, if a university admitted a certain number of new students and saved their records in another table. But later they wished to add the master list of all their students in another table, an append query will be needed. But first they have to create a simple select query to select the record of the new students.

NOTE:
The two tables must not have the same number of fields or columns but ensure that each field in one of the tables have an equivalent field with the same or similar data type in the other table.

To build an append query:
Click http://www.microsofttut.com/2017/03/how-to-build-advanced-action-queries-in-ms-access.html to see full post with screen to screen images and steps

Having given enough basic knowledge of Microsoft Access query in the previous parts of this tutorial chapter, let me now show you some of their practical or real life applications. In these applications, we will use functions like the IIF and AVERAGE functions, etc.

I will show practical illustrations with two systems namely:
1. Students’ Result System
2. Workers’ Payroll System

I will discuss students' result system in this part 4 of chapter 4 the MS Access tutorial and discuss workers' payroll system query in part 5 (last part of chapter 4).

TIPS:

Please try to type the expressions of functions used in these examples. Don’t copy the syntaxes as it will help to reduce syntax errors and also improve your rate of comprehension.

I will also give you some practice exercises which you should do on your own in the last part of chapter 4 (part 5).

You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016 at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html or study part 3 - SOME SPECIAL QUERY FUNCTIONS & EXPRESSIONS IN MS ACCESS & THEIR APPLICATIONS at http://www.microsofttut.com/2017/03/some-special-query-functions-in-ms-access-applications.html

You can also take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html
.
QUERYING THE STUDENTS’ RESULT SYSTEM (GRADE LIST) IN MS ACCESS 2013 & 2016
The Students’ Result system or Grade list is a database that contains students’ information on the various subjects the offered which includes the score in each subject, grade, total score, total grade, average, total average, etc.

You can apply query to filter some record that match your criteria. For example, you might want to see the records of students who performed very well in a particular subject, etc.

In the illustration below, you will see the IIF and AVERAGE functions in action.

PRACTICAL STUDENTS' RESULT SYSTEM QUESTION
Given that the marking scheme of Government Secondary School Owerri is as follows:
• 0 – 39.9 = F
• 40 – 49.9 = E
• 50 – 59.9 = D
• 60 – 69.9 = C
• 70 – 79.9 = B
• 80 – 100 = A
• Above 100 = ERROR

If 50 Students participated in this examination in the following subjects:
• Maths
• English
• Biology

1. Using the above marking scheme, calculate the following:
a. Total score
b. Average score
c. Grades on respective subjects
d. Overall grades

2. Filter all the students that made grade “A“ in English and Maths.

3. Filter all the female students that made grade “A” in Biology.

SOLUTIONS TO THE STUDENTS’ RESULT SYSTEM QUESTION
First, you have to create a table which will contain students’ information like ID, First name, Sex, Scores for each subject, Total score, Average Score, Overall grade, etc. Ensure that you specified the right data type for each field column.

While creating the table in design view, specify Number data type for columns that will contain numbers like the T SCORE, AVG SCORE columns, etc. Also specify Short Text data type for columns that will contain text like the FIRST NAME, SEX columns, etc. The data type for the ID column would be Auto Number as shown in the figure below.

Design view for the grade list table

Switch over to datasheet view and add 50 records to the table. Don’t add anything in the T SCORE, AVG SCORE and OV SCORE columns. Their values will be calculated in the query design view. A sample of the table is shown below.

Sample datasheet view of the grade list table

Now, using this table as data source, create a simple select query. Drag over all the fields into the query section as shown below. Now insert an empty column near each subject in the simple query environment.

To achieve this: click on the column whose immediate left you wish to insert the new column, then click Insert Column located in the Query Setup group under the Design tab.
For example, to insert a column between the ENG and MTH columns, click on the MTH column.

To enter your calculations on the appropriate columns, insert your cursor in the Field row. Type the column name for that column, then type the colon sign ( after the field name in the Field row and then begin to enter the appropriate syntax at the appropriate column and then press the enter key on the keyboard after entering each syntax. Check the box for each added field under the Show row.

1. Type the following syntaxes:
a. T SCORE: [MTH]+[ENG]+[BIO]
A sample of the query environment and the syntax is shown below.
Go to http://www.microsofttut.com/2017/03/query-practical-application-students-grade-list.html to study full tutorial with screen to screen images.

A payroll system is the compilation or list of workers’ information and their payment details in an organization or company. It is a database containing all workers information and their payment details such as basic salary, allowances, gross pay, and net pay.

In the last part (part 4) at http://www.microsofttut.com/2017/03/query-practical-application-students-grade-list.html, I illustrated the practical applications of MS Access query functions in Students’ result system (Students’ Grade List). In this part 5, I will illustrate the business applications of these query functions in workers’ payroll system.

You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016 at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html or you can also take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

QUERYING WORKERS’ PAYROLL SYSTEM IN MS ACCESS
You may wish to filter or extract some records that match your criteria. For example, you might want to see the number of workers with a certain qualification like MSc, BSc, etc. In such cases, MS Access helps to simplify the protocols for you especially in big organizations where there are very large number of workers.

In the illustration below, you will see the IIF and AVERAGE functions in action.

DEFINITION OF SOME TERMS USED IN PAYROLL MANAGEMENT

As we move along in this illustration, we will be using some payroll terms. So I will like to define some of these terms so that you will become used to them.

PAY SLIP:
This is a detached leaflet or a record from the payroll that contains a worker’s information and payment details.

BASIC SALARY:
This is an amount or sum of money a worker is entitled to at the end of the month in a company or organization which is highly influenced by worker’s qualifications and level, and also by the strength of the organization.

ALLOWANCE:
This is a fringe benefit that is allocated to every worker in order to encourage workers and enhance their performance in the company or organization.

TAX:
This is a compulsory levy that is deducted from a worker’s basic salary at the end of the month in order to generate revenue for the state and federal government.

GROSS PAY:
This is the summation of or the total of a worker’s basic salary and all the allowances he is entitled to at the end of every month.

NET PAY:
This is Gross pay minus all deductions such as tax and loans. It is the final take-home of a worker at the end of the month.

PRACTICAL ILLUSTRATION OF A PAYROLL SYSTEM
QUESTION:
1. The question is as follows:
The manager of JOE-LINKS SERVICES wants to computerize the payroll system of the company.

a. As a spreadsheet expert, design a payroll system to his recommendation and approval to pay his workers on monthly basis such that basic salary is influenced by qualification as follows:

• WASSCE = $20,000
• OND = $25,000
• NCE = $28,000
• HND = $32,000
• BSc = $40,000
• MSc = $50,000
• PhD = $80,000 respectively.

b. Marital allowance would be 8% and 10% for single and married workers respectively.

c. Transport, Feeding, Dressing and Housing allowances would be 6%, 4%, 5%, and 8% of their basic salaries respectively.

d. Compute a tax deduction such that:
WASSCE = 5%, OND = 6%, NCE = 8%, HND = 10%, BSc = 12%, MSc = 15% and PhD = 20% of their basic salaries respectively.

e. Calculate the Gross pay and Net pay for each worker.

f. Setup an advanced query or filter to filter all workers that earn above $30,000.

g. Also filter al single workers with qualifications of MSc

SOLUTION TO THE PAYROLL SYSTEM QUESTION
First, you have to create a table which will contain workers’ information like ID, First name, Qualification, etc. You may wish to abbreviate these column titles but make sure that you are consistent when referring to them in the query section. Also ensure that you specified the right data type for each field column.

We will need other columns like: Basic salary, Transport, Feeding, Dressing and Housing allowances, Tax, Gross pay and Net pay. But we will generate these columns in the query environment.

While creating the table in design view, specify Number data type for columns that will contain numbers. Also specify Short Text data type for columns that will contain text like the FIRST NAME, Marital status, Qualification columns, etc. The data type for the ID column would be Auto Number as shown in the figure below.

Visit http://www.microsofttut.com/2017/03/query-practical-application-in-workers-payroll-system.html to study full post with screen to screen pictures.

There are some built in MS Access query functions to help you set more criteria. These functions apply to different field types. There are some for Date field, Text field, Number fields, etc. Some of these functions include the Between, Year, DatePart, DateSerial, DateAdd, etc. Most times they are combined with AND, OR, etc.

There are many of them but in this part 3 of this MS Access tutorial chapter (chapter 4), I will now list about 16 of these expressions below with the fields they can be applied to, their expression and then their descriptions.

This is the part 3 of chapter 4 of the MS Access tutorials.
You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016 at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html

Or study part 2 here at http://www.microsofttut.com/2017/03/methods-of-filtering-database-in-ms-access.html

You can take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

The table or database from which we will draw our reference is shown below.

EXPRESSIONS THAT CALCULATE DATE AND ALSO USE THE RESULT AS CRITERIA
1. FIELD: PREDICTED REG DATE
EXPRESSION: Between Date() And DateAdd("m",2,Date())
DESCRIPTION: This expression displays records of Student’s whose Registration date fell between today’s date and two months from today’s date.

2. FIELD: REG DATE
EXPRESSION: <Date()-20
DESCRIPTION: This expression displays the records of students whose registration dates are less than 20 days old.

3. FIELD: REG DATE
EXPRESSION: Year([REG DATE])=2016
DESCRIPTION: This expression displays the records of students that registered in the year 2016.

4. FIELD: REG DATE
EXPRESSION: DatePart("q",[REG DATE])=3
DESCRIPTION: This expression displays the records of students that registered in the third quarter of the calendar.

5. FIELD: REG DATE
EXPRESSION: DateSerial(Year([REG DATE]),Month([REG DATE])+1,1)-1
DESCRIPTION: This expression displays the records of students that registered on the last day of the month.

6. FIELD: REG DATE
EXPRESSION: Year([REG DATE])=Year(Now()) And Month([REG DATE])=Month(Now())
DESCRIPTION: This expression displays the records of students that registered in the current year and month.

EXPRESSIONS THAT USE TEXT AS CRITERIA
Click http://www.microsofttut.com/2017/03/some-special-query-functions-in-ms-access-applications.html to read full post with screen to screen ilustration images.

This is the part 2 of chapter 4 of the MS Access tutorials. You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016 at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html.
You can take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

FILTER BY FORM:
This is the first method of filtering database. This filter method creates a lookup column in each field where the criteria can be selected. It then retrieves the records that match the specified criteria.

To filter a database by form:
1. Open the database table in datasheet view.
2. Click the Home tab. Click the drop down arrow beside the Advanced command in the Sort & Filter group and select Filter by Form as shown below.
3. Access then opens up a blank datasheet with empty fields below the column title and then assigns a lookup button to each field.
4. Select your criteria from the lookup button assigned to each field. You can also type it directly in that field. For example, in my case, I want my filter query to return student(s) whose name(s) is Joseph. So I will type “Joseph” (in quotes) under the FIRST NAME column as shown below.
5. Click the Toggle Filter icon to apply filter. Access then extracts the record(s) that met the specified criteria. You may wish to save or remove the filter by clicking the Advance then clicking Clear All Filters.

FILTER BY SELECTION
This is another method of filtering a database. Here, Access extracts the records that met the specified criteria. Unlike the filter by form method, the criteria would be specified before filtering. This method gives you many filter options such as “Contains”, “Equals”, “Greater than or equal to”, “Less than or equal to”, “Between”, etc.

To filer a database by selection:
1. Open the database table in datasheet view
2. Specify your criteria i.e. select any record’s information you wish to use as your criteria. For example, if I want to filter out all the medical students in my database, I will go under the COURSE column and select any field that contains the word “Medicine”.
3. Click the Home tab.
4. Click the drop down arrow beside the Selection command in the Sort & Filter group. Then select any option that suits your intention. In my case, I will select Contains “Medicine” as shown below.
5. Access extracts the records of all medical students as shown below. You may wish to save the query result as a new query or you discard it.

FILTER BY EXCLUDING SELECTION
This is the third method of filtering a database in MS Access. It is just the opposite of filter by selection i.e. unlike the filter by selection that displays the records that met the specified criteria, it displays the records that did not match the specified criteria. This method gives you many filter options such as “Does not contain”, “Does not equal”, etc.
To filter a database by excluding selection:
1. Open the database table in datasheet view.
2. Specify your criteria i.e. select any record’s information you wish to use as your criteria. For example, if I want to filter out all the medical students in my database, I will go under the COURSE column and select any field that contains the word “Engineering”.
3. Click the Home tab.
4. Click the drop down arrow beside the Selection command in the Sort & Filter group. Then select any option that suits your intention. In my case, I will select Contains “Medicine”.
5. Access displays all the records that did not match the specified criteria.
ADVANCE FILTER/SORT
This is the fourth method of filtering a database in Access. It is the best method of filtering a database because it allows the user to filter a database using one or more criteria, unlike all the previously explained method that limits you to only one criteria.

To apply the advance filter in a database:

1. Open the database table in datasheet view.
2. Click the Home tab. Click the drop down arrow beside the Advanced command in the Sort & Filter group and select Filter by Form.
3. Select Advance Filter/Sort.
4. Access takes you to the query design view. Select the fields whose data you want to retrieve. You can select more than one field at a time by holding down the ctrl key and then clicking on the fields needed.
5. Click and drag the selected fields into the first field in the Field row (first row). Once you drop them in the first field, Access automatically fixes the fields for you. The Table row displays the table/query name from which the field came.
6. Set the query criteria inside any field of the Criteria row.
For examples, to retrieve records of students whose age falls between 18 and above and whose names start with letter “J”, type: >="18" in the AGE field, then type: Like "J*" in the FIRST NAME field as shown below.
Visit http://www.microsofttut.com/2017/03/methods-of-filtering-database-in-ms-access.html to see full post with screen to screen images.

Query is one of the objects of a database that is used to view, change and analyze data in different ways. It can also be used to perform calculation and can also serve as source data for forms and reports in MS Access. It can also be used to retrieve some records in one or more tables based on a specified criteria.

This can be very useful especially if there are so many records in a table and you wish to retrieve only the records that met the specified criteria. To achieve such a task manually would take much of your time, but with MS Access database queries, you can achieve it within few minutes.

This is chapter 4 of the MS Access tutorials. This chapter has 5 parts and this is the first part (part 1). In this part, I will explain all the features of query as an object of a database in Microsoft Access. I will also discuss the various views available in query object, the various operators used in query operation and the various methods of querying or filtering a database in MS Access.


In the previous chapter (chapter 3), I explicitly explained Form as an object of a database in MS Access. See it at http://www.microsofttut.com/2017/01/forms-manipulations-and-some-powerful-built-in-functions.html

You can take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

QUERY – A POWERFUL OBJECT OF A DATABASE

Queries enable you to extract data from your database tables and allow us to answer questions we have about the data. Queries may combine data from multiple tables and manipulate data output through the use of expressions, formulas, and functions.

Knowing the appropriate questions to ask and retrieve from your database in a business environment is just as important as knowing how to build that question into a database query. Most people know the questions to ask their company’s database but they don’t know how to ask such questions.

This chapter will give you a deep knowledge about Microsoft Access operators and their operations, simple select query and filter query or query by filter. The queries we will discuss in this chapter are the simple or ones, then in the next chapter, we will discuss advanced queries in detail.

We will also demonstrate the use of functions such as Like, In, Between, Null, and Parameters. Also in this chapter, will also illustrate some of the practical applications of query in the design of students’ result system and workers’ payroll system

What Can You Ask and Retrieve from your Database?
When working with databases in a business, there are numerous questions you may want to your company’s database. Assume you are managing a retail store with numerous sales employees working on sales commission. Here are some basic examples of questions you may ask your retail store database:

• Which are the best-selling products?
• Which employees are the most productive?
• What day and time of week is busiest in your store?
• What products have the highest profit margin?
• What are the total dollar sales by product each day this week?
• What is the price for a particular product?
• What products in inventory have never sold?

It is important to realize that vast amounts of information may be queried from the database if you know the right questions to ask—and you know how to formulate that question into a database query. Knowing the right questions to ask comes with managerial experience and will not be covered as a topic in this chapter. Instead, this chapter will explain how to translate a question or request for information into a Microsoft Access query.

WORKING WITH QUERIES IN MS ACCESS
Any query can be saved and rerun at a later time. Each time a query is run, it will retrieve the most recent data from the database tables to generate the output. As a result, queries are often used as the basis for database forms and reports.

It is also important to understand that there is a direct “connection” between the query output and the table data. Any change you make to data in the query output is actually modifying the table data at the same time. This is similar to the connection we saw earlier between forms and tables.

Before going into query types and their designs, it is necessary that you know and familiarize yourself with the different types of OPERATORS and when they should be used because you will make use of them when querying your database.

OPERATORS
These are signs, symbols or words used in MS Access to define criteria and to perform calculations.

TYPES OF OPERATORS
There are 3 main types of operators used in MS Access. They are:
• Mathematical Operators
• Relational Operators
• Logical Operators
Now let me elaborate them one by one.

1. MATHEMATICAL OPERATORS

These are the most commonly used operator. They are used to perform calculations. Examples include:
• Addition (+)
• Subtraction (-)
• Multiplication (*)
• Division (/)

2. RELATIONAL OPERATORS

These are the operators used to create or define criteria. They are sometimes used together with logical operators to combine two or more criteria. They include:
• Equal to (=)
• Not equal to (<>
• Greater than (>
• Less than (<
• Greater than or equal to (>=)
• Less than or equal to (>=)

3. LOGICAL OPERATORS

These are the operators usually used to combine two or more criteria. They are used together with relational operators to define complex criteria. They include: AND, OR, NAND, NOR, EOR (XOR) and NOT.

• AND returns true when the two (or all) conditions are true, else false (i.e. returns false).

• OR returns true when either of the two or both of the conditions are true, else it returns false.

• NAND is the negation (reverse or opposite) of AND (pronounced NOT AND). It returns false when both conditions are true, else it returns true.

• NOR is the negation of OR (pronounced NOT OR). It returns false when either of the two or both conditions are true, else it returns false.

• EOR (XOR) (pronounced EXCLUSIVE OR) returns true when either of the conditions is true (i.e. when both conditions are not the same), else it returns false.

• NOT is used in a case of one condition. It returns true when the condition is false, else it returns true. In electronics programming, it is called an INVERTER.

The figure below contains the truth table of some common logical operators in MS Access.

Visit http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html to see full post with screen to screen images.

World Soccer or Football League 2017 is another awesome soccer I have ever enjoyed on my android. This is the latest version of this soccer game!

The game really has many unique features which made it outstanding from other football games. But it has similar features with PES 2017 and FIFA 2017. I have also played the apk version of this soccer game on my PC with the help of an android emulator like bluestacks, koplayer, andyroid, etc.

Apart from using an android emulator to install the game, World Soccer League also has the exe raw file which you can install directly on your PC (From Windows 7 and above). So computer users now have to option: either to utilize the android emulator to install the apk raw file on their pc or install the exe file directly. I enjoyed World Soccer League (WFL) 2017, both the android and pc version. The game can also be played on tablets.

World Football League's menus and lineups are really similar to the ones in the PES saga. Just like in those video games, you won't find the names of real players, but instead, very similar versions of their names. For example, instead of Benteke you have Bantaka, etc.

In this post, I will show you how to download, install, setup and play World Football League. I will also give you the new and updated features of this awesome soccer game.

See Also: GUIDES TO DOWNLOAD AND INSTALL LATEST VERSION OF HEAD SOCCER LA LIGA 2017 APK + MOD at http://www.microsofttut.com/2017/03/guides-to-download-and-install-latest-hsll-2017-apk.html

New and Updated Features of World Soccer/Football League
==> Save and keep great image or video of each the moment.
==> Achievements and Global Activity are provided
==> Support 15 languages
==> There are around 60 national teams, 60 clubs and total of 2000 players.
==> 4 game modes are now available: Exhibition, Cup, League, and Training.
==> The direct pc version can only be played on Windows 7 and above

How to Download & Install World Soccer League (WSL) 2017 on PC
As I said earlier you have two options here: the exe raw file installation and Installation through an android emulator. Both options are simple and free.
Click http://www.microsofttut.com/2017/03/download-play-world-soccer-league-2017-pc-android.html to view full post with download links and screenshots.

The latest PES (Pro Evolution Soccer) Club Manager apk + data file is now available for download. This android game is a soccer simulation game in the PES series and has been downloaded and played by more than 10 million soccer game lovers.

What I enjoyed most in this PES Club Manager (PCM) is its awesome graphics and also it gives me the opportunity to create my choice dream team, and access and many other features. I know you also will enjoy this awesome android apk game.

The only discouraging feature of PES Club Manager android game is that it is an online game only. This means that you must be connected to the internet to play it.

See Also: HOW TO DOWNLOAD & INSTALL DREAM LEAGUE SOCCER 2017 APK MOD + OBB DATA at http://www.microsofttut.com/2017/03/download-install-dream-league-soccer-2017-apk.html

Minimum Device Requirement For PES Club Manager Android Game
Below are the least requirement your android device must meet for PCM apk +data file to run on it and some additional info about the game:
• A minimum of 1GB RAM
• A minimum of 500MB internal storage space.
• Requires android 4.2 and above.
• PES Club Manager (PCM) is a free-to-play game. This means you can play the entire game without paying a dine. But you can purchase PES Coins to speed up your game play strengthen your club faster.

Additional Information For PES Club Manager Apk
• Last Update: February 13, 2017
• Game Size: 914MB
• Apk Developer: Konami

New and Updated Features Of Latest PES Club Manager (PCM) Apk
There has been various updates in this current version of PCM with some new features.

Updated Features of PES Club Manager
• It features 3D Matches and is Powered by the PES Console Game Engine
• The game came with over 5,000 + Officially Licensed Real Players that can be seen around the globe
• You can also change weak and tired players when playing any match with your opponent
• The game also has real-time match simulation which manages user style when controlling and implementing tactics to your created team in real time.

New Features PES Club Manager
• LIVERPOOL FC, the prestigious 5-time champion of UEFA Champions League is now an icon image.
• New Hall of Fame event, "CLASSIC TOURNAMENT"
• Filter function for player list
• Winning ceremony demo movie for season/cup
• Image import function for Team Edit

Guides on How to Download & Install PES Club Manager Apk/Data File
Visit http://www.microsofttut.com/2017/03/download-play-latest-pes-club-manager.html to read full post with images and direct download link of PES Club Manager apk.

Grand Theft Auto 5 or GTA V is one of the games that every action game freak would love to play. You can play this adventure action game on your pc or console. GTA 5 can now be played on android devices for free. All you need is to files: the apk+data file and the apk downloader, then you need an android phone with a fairly high configuration with android version 4 and above and at least 1 GB RAM.

The graphics are amazing, basically the same as a console, and the controls are surprisingly really good. Not to mention most have Bluetooth controller support. Try GTA V: Vice City (not free from google play store), San Andreas (Download link given below) and others.

In this post, I will show you how to download, install, setup and play GTA 5 on your PC, Console or Android device within 10 minutes! I will also tell you some of the new and updated features of the game. So just get ready to follow my self-explanatory steps!

Also note that Grand Theft Auto 5 is a HD (High Definition) action adventure game and as a result consumes a lot of Hard Drive space and also a lot of memory (RAM), though the game’s graphics worth it. So ensure that you have enough space in your Hard Drive (at least 500 GB space) and a large RAM size (from 4 GB upwards).

Read Also: HOW TO DOWNLOAD AND PLAY UPDATED MORTAL KOMBAT X (MKX) APK+OBB DATA FILE V1.12 at http://joelinkstech..com.ng/2017/03/download-and-play-updated-mortal-kombat.html

New & Updated Features of Grand Theft Auto V:

• Adjustable graphic settings.
• Cloud save support for playing across all your mobile devices for Rockstar Social Club Members.
• Re-mastered, high-resolution graphics built specifically for mobile including lighting enhancements, an enriched color palette and improved character models.
• Dual analog stick controls for full camera and movement control.
• Three different control schemes and customizable controls with contextual options to display buttons only when you need them.
• Compatible with the MoGa Wireless Game Controllers and select Bluetooth and USB gamepads.
• Integrated with Immersion tactile effects.

Guides to Download, Install, Setup & Play Grand Theft Auto V On PC, Console or Android Device

I will now give you a detailed guide on how to successfully download and install GTA V on your PC or android phone.
For the Android, PC or Console GTA V Installation Guide: See full post with pictures and direct download links at http://joelinkstech..com.ng/2017/03/download-install-gta-v-pc-android-apk.html