Excel Archives | TATEMS Fleet Maintenance Software
Tag Archives for " Excel "

How To create TATEMS Spreadsheets from Scratch

In this post, I am going to go over how to create a custom spreadsheet that links to your TATEMS data.

I have created and posted a number of these custom spreadsheets that you can download for free at

https://tatems.com/fleetmaintenancesoftware/spreadsheets.cfm

Hopefully not too technical

This might be a little technical for some, but if you take the time to learn it, I think you'll like the results.

Here is a video that shows you this process using Excel 2003:

TATEMS Import TATEMS Data Into Excel 2003

The written instructions

Below explains how to do it using Excel 2010

In Excel click on Data->From Access

 

Then you will want to navigate to the:

TATEMS2005be.mdb

file in

C:\Program Files\TATEMS 2005

Or it will be in the :

C:\Program Files (x86)\TATEMS 2005

folder.

Excel-Data-From-Access-Select-Datasource-ScrenShot

Select-Datasource-tatems2005be.mdb in the TATEMS 2005 folder

Then choose tblEquipment and click Ok

Excel-Select-tblEquipment-From-TATEMS-Fleet-Maintenance-Software-Database

Select the tblEquipment table

Then on the Import Data popup choose properties

The Query

Then click on the Definition Tab and paste this SQL statement into the “Command Text” box”

 

SELECT tblEquipment.*,tlkpEquipmentType.sEquipmentType FROM tblEquipment,tlkpEquipmentType WHERE tblEquipment.lEquipmentTypeID = tlkpEquipmentType.lEquipmentTypeID

 

Then change the “Command Type” from table to SQL then click OK

 

Excel-2010-Connection-Properties-Command-Text-And-Command-Type-Popup-Screenshot

Connection Properties->Definition Command Text And Command Type

 

Then click OK and then click on again on the Import Data screen:

 

This will show you all the columns for each piece of equipment and the equipment type. You can delete the columns you don't need and rename the others.

Get any data you want

You can use this technique to grab whatever data you need from the database by using different SQL statements.

You can also use the wizard in Excel to create your queries.

In Excel click on

Data->From Other Data Sources-> From Microsoft Query

then choose:

Microsoft Access Database*

from the list of data sources.

then click ok.

Excel Data From Other Data Sources - Microsoft Query Choose Data Source ScreenShot

Choose MS Access Database* then click OK

Then in the “Select Database” window, you will need to navigate to the:

TATEMS2005be.mdb

file in:

C:\Program Files\TATEMS 2005

Or it will be in the :

C:\Program Files (x86)\TATEMS 2005

folder.

or on your network.

You can use the Drive at the dropdown at the bottom or use the network button if needed.

And also check the box that says Read Only

Excel 2010 Data Other Data Sources Select Database Choose tatems2005be.mdb database-Screenshot

navigate to the TATEMS database file called tatems2005be.mdb click on it then click the Read Only check box

Joining Two Tables Together

Then you will be presented with the Query Wizard window and you can choose the columns inside the tables that store the info you are looking for. You usually want to expand the table called tblEquipment and choose the fields you need. In the screenshot below I have selected: sEquipNum, sEquipDesc from tblEquipment and sLocation from another table called tlkpLocation.  So, if you need info like Location which is stored in another table called:

tlkpLocation

then navigate to the:

tlkpLocation table

and choose:

sLocation

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Wizard-Choose-Columns-Screenshot

Select the fields from the tables you want to display and click next

The wizard will automatically join the 2 tables together so that the Location, in this case, will be linked to the proper piece of equipment. After you have chosen the columns to display then click next and on the following screen you can create your own criteria for the report.

Filter Data

On the next screen, you will be able to filter the data if you want to.  If not just click next.  In the screenshot below I have selected the sLocation field then I chose equals from the first dropdown in the left column and once I selected that then the right-hand column populates with the data I want to filter for. I will choose King City from the list in the screenshot below. You can also filter on multiple fields.  Once you have selected a field to filter by and created the filter you can move to another field and filter by that field.

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Wizard-Filter-Data-Screenshot

Microsoft Query Wizard Filter Data Screen

Sort Order

Then click next and you can create your own sort order. You can sort by multiple fields, in order of importance. In the screenshot below I will just sort by the sEquipYear field as seen in the screenshot below.

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Wizard-Sort-Order-Screenshot

Microsoft Query Wizard – Sort Order Screen Shot

Then click next and you can then choose to either return the data Microsoft Excel to view it or you can view the data and the query in the Microsoft Query window. If you are familiar with SQL then you can manipulate the query to suit your needs. I'll choose the View data or edit query in Microsoft Query as selected in the screenshot below.

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Wizard-Finish-Screenshot

Microsoft Query Wizard – Finish

Microsoft Query

You can now see the data you have selected and once in the Microsoft Query Window you can customize your query even further.

 

For example, you can set the names on the column headers to a more user-friendly name by clicking on the column you want to rename and then click on Records->Edit Column.  I have selected the sEquipNum column to edit in the screenshots below and will change it from sEquipNum to Equipment #.

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Query-From-MS-Access--Edit-Column-Screenshot

Microsoft Query Edit Column

 

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Query-From-MS-Access--Edit-Column-Field-Column-Heading-Screenshot

Edit Column change sEquipNum to Equipment # then click OK

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Query-From-MS-Access-After-Edit-Column-Heading-Screenshot

Column heading in Microsoft Query after changing sEquipNum to Equipment #

 

Once you have the query the way you want it you click on the little “exit door” icon at the top to return your data to Excel.

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Query-From-MS-Access-Exit-Return-Data-To-Excel-Screenshot

Exit and return data to Excel

After you click the “return data to Excel” icon you will see the Import Data dialog popup and on that screen you just click OK

Excel-2010-Data-Other-Data-Sources-Select-Microsoft-Query-Query-From-MS-Access-Exit-Return-Data-To-Excel-Final-Import-Data-Screenshot

Import Data dialog after clicking Exit return data to excel.

I hope this gives you a basic idea of how to create your own reports in Excel with your TATEMS data.

All The Best!

Marc

New Equipment Filters Spreadsheet with More Info and Video Tutorials

I hope you're doing well so far this week

Our puppy Kiba is not doing so well, He got clipped (neutered) yesterday.

The Vet says he has to stay confined for 10 days and he's already getting antsy.

Gas prices are down to $3.35 per gallon at the Sam's club nearest to us here in Las Vegas.

It's supposed to get up to 102 ° F today. It's cooling down…

Now on to today's Tip:

I had a request from a client yesterday for a filters report that could be broken down by customer or a list of equipment.

So I created a spreadsheet that will link to your TATEMS data And display the following columns in Excel:

Equip Num
Customer
Location
Dept Or Area
Lube Primary
Lube Secondary
Air Filter
Air Inner
Fuel Primary
Fuel Secondary
Hydraulic
Trans Filter
Water Filter
Other Filters

Once you open it up in Excel you can sort it and filter it anyway you want.

I show you how in this video:

You will need to have Microsoft Excel to use this spreadsheet and have it link to your TATEMS data.

This spreadsheet will link to your TATEMS data if it’s in the default installation location on 32 bit Windows.

C:Program FilesTATEMS 2005tatems2005be.mdb

The video in a previous post below shows you how to change the data path in the spreadsheet if you have Excel 2007 or Excel 2010. Click here to see the previous post.

And the video below shows you what you will probably need to do in order to make it work on your computer if you are not running Excel 2007 or 2010

You can get this newest spreadsheet at our website and it’s called:

TATEMS-EquipmentFilters-WithCustomer-Location-DeptOrArea-Fields

It’s completed for you online and ready to download at the link above or here:

https://tatems.com/spreadsheets

This spreadsheet is number 61 in the list on that spreadsheet page.

I hope this tip has been helpful for you.

Please leave any questions, comments or requests for special Excel Spreadsheets below. If your spreadsheet requests are feasible I’ll get them created and posted in a future TATEMS Tip.

Thanks Again,

Marc Ready
PCHelp, LTD

P.S. We plan to have a updated version of TATEMS in a a day or two.

P.P.S. We are also about ready to release a beta version of our new TATEMS Reminders program. This program runs in the windows notification tray down by the clock separately from TATEMS. It will automatically send out different reminder types via email to as many people as you need to send them to. Let me know if you would like to try out the beta version.