All posts by Ali Jackowski

Making the Connection with SQL

Making the Connection with SQL

Generally, Microsoft applications have been created to play well together.  Most Office Suite users are familiar with the functionality of importing Excel charts and tables into a Word document or a PowerPoint presentation, creating powerful ways to drive home your data’s importance. Similarly, SQL can be used with other Microsoft applications to bring your database’s performance to a new level of power and customized performance.

Excel <-> SQL

Import data from Microsoft Excel 2016 into a SQL Server.

By connecting to an external workbook, you can easily retrieve data to create tables and charts, or even perform analyses. You can import data based on queries from multiple tables or views. Once you have the connection, you can set up your Excel reports to refresh automatically. That way your reports always stay current with the data in your SQL database.

Excel Power Query, also known as the Get & Transform feature, lets you format your imported data without changing the original source. You can use this feature to combine data from different sources, shape and refine data, or create dashboards and pivot charts.

Access <-> SQL

Use SQL to optimize your Microsoft Access database performance

Linking your Access database to a SQL server is an easy way to upsize your database, without completely migrating to a SQL server. The process used to connect Access to a SQL Server is simple, and it allows you to pick which tables and views you want to import. The link between them is a two-way connection, giving you the best of both worlds. Access performs as the front-end, letting you easily create queries, forms, and reports. SQL Server performs as the back-end, storing and processing larger amounts of data.

SQL Server Capabilities

Bring it all together with Microsoft SQL Server

Microsoft is focusing on building powerful Business Intelligence (BI) capable applications. SQL Server 2017 includes services that help organizations better manage complex data. That is why making the connections with the applications you already use is so important.  It gives you the power to do more with your data and the tools to gain valuable business insight.

Mission Critical Training can help you become a master of these powerful applications, enabling you to do more with your data.

Check out our upcoming classes:

 

Creating 3D References between Workbooks in Excel

 Creating 3D References between Workbooks in Excel

Using 3D References between worksheets in an Excel workbook has been around for many years. It allows us to use Excel more like a database than a spreadsheet. As Excel functionality has increased, so has Excel’s capabilities, including the speed that connections update, and the types of connections that can be made between different software applications. However, it is not well known that a 3D connection can be made in different workbooks, which actually adds a new level to the  using Excel more like a database.

It works the same as a regular 3D Reference, but there are some subtle differences. Follow these steps to see how a 3D Reference is made between two workbooks:

  1. Ensure that all workbooks being used for the 3D Reference are open, as you will need access to them during the process. We are going to use two workbooks from two of our Excel classes, and create what is called a Simple 3D Cell Reference.
  2. Click inside the cell in which you will build the formula. For our exercise, we will select an empty cell, as it is much easier to see the process.
  3. Anytime we create a formula, we need to type an equal sign, so Excel knows we are creating a formula. 3D References between workbooks in Excel - Equal sign
  4. Next, we click the Excel icon in the Taskbar, and select the workbook we want to reference in our formula.3D References between workbooks in Excel - Workbooks
  5. Once the workbook appears, click the worksheet within that workbook that you wish to reference. This will start to populate the formula with some references that may look kind of strange. 3D References between workbooks in Excel - Formula bar
  6. Click on the cell on the worksheet you wish to reference, which will populate the selection into your formula.3D References between workbooks in Excel - Formula Population
  7. It is important to understand that the formula you are seeing in the formula bar is actually populating in the original workbook where your reference will populate. From here, either press the Enter key on your keyboard, or click the checkmark just to the left of your formula.3D References between workbooks in Excel - Check
  8. This action will return you to your original workbook and populate the referenced information.3D References between workbooks in Excel - Data connected

These types of references can be used in any formula or function as well, but remember that this is equivalent to a connection. Where you keep the file and the reliability of your network dictates if the information is able to reliably update.

Want more information on 3D References, or ways in which you can use Excel like a database? Check out MCT’s Excel training courses.

Connecting to Excel In Access

 Connecting to Excel In Access

Access allows users to connect to various data sources, such as SQL, other databases, XML files, and SharePoint, as well as Excel files. In order to ensure a proper importing and/or linking, the data needs to have headers for each column of information. Also, there can be no entire columns or rows of information missing, and no additional information, such as worksheet titles, on the worksheet being used for the connection. The worksheet should look similar to this example:Connecting to Excel in Access - Worksheet

After you have created your database (or utilize an existing database), setup the connection between Access and Excel following these steps:

  1. Click the External Data tab, go to the Import & Link group, and click the ExcelConnect to Excel in Access - Database
  2. Once the dialog box appears, browse and select the file in which you keep the desired data.Connect to Excel in Access - File
  3. We are going to select Link to the data source by creating a linked table for our example.Connect to Excel in Access - Linked Table
  4. Click OK to take you to the next step.
  5. Next, select the worksheet that has the information you want linked. If you have specific Named Ranges you want to use, then you can also choose them during this step. (To learn more about Named Ranges, check out our Excel Advanced training course.)Connect to Excel in Access - Named Ranges
  6. Ensure your data is listed in the next section, then click the Next button at the bottom of the dialog box.Connecting to Excel with Access - Data
  7. Make sure the First Row Column Headers radio box is checked, so Access knows what to name the columns, then click Next.Connect to Excel in Access - Column Headings
  8. Type in the name to be used for your new table. Coect to Excel in Access - Name
  9. Click Finish.
  10. Click OK, when the confirmation dialog box appears.Connecting to Excel with Access - Confirm
  11. Double-click your new table in the Navigation Pane to the left, when it appears, and your new table will populate.Connecting to Excel with Acess - Navigation Pane

This link is always active when the database is open. Any changes made to the Excel workbook will be immediately visible in your new table.

Get more ways to connect your data with an Access class from Mission Critical Training.

 

Mail Merge in Word

Mail merge in Word

Being able to enter data and save it in one central location is great, but being able to access the information is more important, since data that never gets used for anything is pretty worthless data. In some cases, we use our data for statistics, so we can make decisions for the future of the company. In others, we could be using the data for current reporting, enabling us to make day-to-day decisions. Alternatively, we may need to use our data to help us market to our existing clientele, or bring back clientele that have possibly moved on from using our company. For this last option, we need a way to quickly create the necessary labels, letters, or mailers from data that was downloaded from a database, which typically is downloaded into an Excel file. To do this, we can use the Mail Merge feature in Word. This is quick and easy to do.

Just follow the steps in this video:

Want to learn more ways to make Word work for you? Take a training course from Mission Critical Training.

Back to School

Back to School

Do you remember the excitement that you felt when you got to go Back to School shopping? Buying new sneakers, lining up your freshly sharpened pencils, picking out just the right Trapper Keeper (either the one with the sweet gray kittens or the one with the shiny red Corvette on the cover), in order to establish your persona for the next nine months, gave us a thrill that was hard to contain. What was it that made the prospect of new school clothes and supplies so exciting? It was the fact that these purchases were all about you, your future, and striving to actualize your latent potential. In other words, it was a time you spent investing in yourself.

Fall is here once more, and the store aisles are again filled with folders and pencils. You may have outgrown the need for Trapper Keepers, but you never outgrow the need to invest in yourself and your future.

Mission Critical Training wants to help get you back to the excitement of the Back to School spirit this fall. We have plenty of classes that can bring your skills to the next level, and position you to move forward in your career.

Our upcoming computer training classes include:

We also have a slate of career development classes that can help you transition into management, sales, project management, or even fine-tune your communications skills.

Need to move those skills forward in a hurry? Check out our intensive two- to five-day Boot Camps!

It’s time to relive the exhilaration of knowing you are investing in yourself. It’s time to get Back to School!

Register online, or contact our sales office for more information, including private group training, at (303) 900-0850 in Colorado or (602) 955-7787 in Arizona, or training@mcstech.net.

Up your SharePoint Skills

SharePoint Skills

Got game?

Mission Critical Training has upped its SharePoint game so you can up yours!

For those just getting started building their skills in site development on the SharePoint platform, we offer SharePoint Level 1, which introduces basic concepts and tools to create and edit SharePoint sites. SharePoint Level 2 gives those with a basic knowledge of the application the ability to harness more advanced functionality and build more complex sites. MCT is excited to now offer SharePoint Level 3, empowering you with the skills needed to edit and configure more advanced web parts, as well as the ability to create app templates, customize content by audience targeting, and more. In our two-day SharePoint Designer class, you will learn to open up the back end of SharePoint to customize sites and build customized workflows.

Register online, or contact our sales office for more information, including private group training, at (303) 900-0850 in Colorado or (602) 955-7787 in Arizona, or training@mcstech.net

For our full calendar of classes, please visit our website.

 

Three Ways to Mark Tasks as Completed – Project

Mark Tasks as Completed

 Marking tasks as completed is an important part of keeping up with progress during a project. More than likely, Project Managers are not on job sites 24 hours a day, seven days a week. Thus, the Project Manager may update the Project file, or someone else might, and everyone has different ideas about what is easier to do, when it comes to software.

In Project, there are a number of different ways to mark tasks as complete, so we are going to list three in this tip.

1:The easiest way to mark a task is to use 100% command, which is located on the Task tab, in the Schedule group.Mark Task as Completed - Task panel

Before clicking the command, ensure you have the correct task selected. Click the 0% command, if you were on the wrong task.

2: Double-clicking on a task will open a dialog box for Task Information. In this dialog box, under the General tab, typing 100% in the Percent Complete field, then clicking OK, will mark a task as complete.Mark Task as Complete - Dialog box

The Task Information dialog box gives you a lot of information about the task. As a side note, it is worth becoming familiar with the different areas of this dialog box.

3: Using the Update Project dialog box will allow the user to update multiple tasks at once. To use this dialog box, select the Project tab, go to the Status group, and select the Update Project command.Mark Task as Complete - Update Project

In this dialog box, selecting a date and clicking OK will mark all tasks, prior to and including that date, as complete.

There are more ways to mark tasks complete that take more steps to find. These three steps are the easiest to find, as well as to explain to others who might be updating information in a Project file.

Learn more ways you can make MS Project a powerful tool in managing your tasks and resources with a class from Mission Critical Training.

 

Why does a simple triangle fully define a Project? The Triple Constraint

Triple Constraint - Triangle

Draw a triangle. Put scope, time, and cost around the three sides. Now put Resources in the center. You have just fully captured the parameters of a Project! Customer satisfaction, quality, and risk are all about real or perceived variability in one or more of these.  This beautiful, simple model, called the Triple Constraint, drives all Projects and all Goal-oriented activity.

Project Management can be mastered with a class from Mission Critical Training.

Change a Web Part Link in SharePoint

Change a Web Part link in SharePoint

Making SharePoint easier to navigate and keeping navigation simple are two very important pieces to user buy-in. Getting these right ensures users will continue to return to the SharePoint site.

Typically, when we add web parts to a page, the title link navigates to the app in the web part. But what do we do if we want the link to take us somewhere else or to a specialized view?

We can actually change the link in the web part to make it take us somewhere else. This is especially helpful when using web parts that have titles that take the user nowhere. All web parts have this functionality, and along with changing the title, this duo can become another tool to be used for easy navigation.

Follow these steps to find out how to set up this functionality:

  1. Navigate to the page with the web parts you wish to change.Change Web Part Link - Edit
  2. Click the Page tab of the ribbon; then go to the Edit group (to the left), and click Edit.
  3. Hover on the web part you wish to change. Once the drop-down arrow on the right-hand side of the web part appears, click and select Edit Web Part. Change Web Part Link - Edit
  4. Once the edit display box appears (you might need to scroll right and up, depending where your web part is located), expand the Advanced section. In some cases, there is a URL already in the field and in other cases, it is empty. Change Web part Link - dropdown menu
  5. Just below the URL field is the Description field. This can also be changed to hide the actual URL, when a user hovers on the title of the web part. Change Web part link -Hide URL
  6. The actual title of the web part is located in the Appearance section of the edit display box. This allows you to change what the title reads, so it can reflect where the URL navigates. Change Web Part Link - Appearance
  7. Once done with the changes, click OK at the bottom of the edit display box. Change Web Part Link - OK
  8. Once you click the Save button, located in the Edit group of the ribbon, your changes will be visible. Change Web Part Link - Save
Become a master of Web Parts by taking a SharePoint class with Mission Critical Training.

PowerPivot KPI Setup – Excel

PowerPivot KPI - arrows

PowerPivot is an added functionality in Excel that offers a dynamic way to display information from other sources. A KPI (Key Performance Indicator) allows a user to dynamically create visuals for data being displayed in the PowerPivot table. Once you have a PowerPivot table setup, which requires a number of steps that include activating the PowerPivot contextual tab, ensuring the data you want to display is added to the PowerPivot model, and creating at least one Measurement.

Watch our video on setting up KPIs for your PowerPivot table:

If you are not familiar with these items, a great place to learn about PowerPivot is by taking our Excel Dashboards class.