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:

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.

Then choose tblEquipment and click Ok

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

 

 

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.

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

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

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.

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.

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.

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 #.

 

 

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.

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

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

Shell-shocked! You never know when disaster will strike

I was looking forward to a normal day when the call came in.  I just got out of the shower and my wife’s phone was on her nightstand ringing.

“Can I talk to Mom?” my daughter said.  I walked the phone down the hall to hand it my wife.  As she spoke she stayed calm but it didn’t sound good.

Our daughter had been in an accident. Our Honda was crumpled and un-drivable but Thank God she was not injured, and neither were the people in the SUV.

Fast forward a few hours and my wife and I are on our way to the body repair shop so they can begin repairs.  I’m waiting patiently for the pedestrians to cross in front of me before I make a right turn. When suddenly BAM!  We get rear ended in a 3 car accident.  The car behind us runs into us after she got rear ended.

Thank God for no injuries.

Now our Toyota has to go the body repair shop for repair.

2 accidents in one day can cause you to feel a little shell-shocked. I had not been in an accident since I rolled my Dad’s VW when I was 18.

I'm feeling like, ok when will the next accident happen?

The unexpected can happen at any time, that’s why it’s so important to have insurance.

You need to insure against losing your data too.

Please make sure you back up your data every day. The best practice is to make sure it’s backed up off site.

At a bare minimum get a copy of your data off your computer and onto to an external flash or hard drive.

I made a video that shows you much of what I describe below:

In order to save and backup your TATEMS data, you need to make a backup copy of a file called tatems2005be.mdb.

tatems2005be.mdb is the file that contains all the data you entered into the program.

This file is in the folder

C:Program FilesTATEMS 2005

Or the folder below on a 64 bit system

C:Program Files (x86)TATEMS 2005

TATEMS must to be closed on all computers whenever you backup or restore this file.

If you ever have to reinstall the program, you will need to restore this file back into its folder after reinstalling in order to work with the backed up data.

A couple of additional things need to be addressed here. There is a Backup and Compact feature in TATEMS under

Tools->Backup and Compact.

You can see this feature in action if you go to TATEMS Training Videos then click on the “Backup and Compact data” link

The Backup and Compact feature only allows you to backup the data to the same folder where the data file is housed.

So if the data file is in

C:Program FilesTATEMS 2005

then the backup file will be created in that folder.

The backup file will be Date/Time stamped within the actual file name. It will look something like this:

tatems2005be7-19-2006-09-12.mdb

The 7-19-2006 is the date part and the 09-12 is the time part in 24 hour time (9:12AM).

This can be useful if you want to go back to a “point in time” backup to see where some mistake was made or something went wrong.

These “point in time” backups and any backups for that matter should be moved off the hard drive to a CR-R, a Zip drive, USB drive or memory card for safe keeping.

The “point in time” backups should also be moved off your hard drive because they can quickly start taking up space, especially if you do them on a daily basis.

You can always link to a point in time backup by using the Networking Tool in TATEMS.

Under: Tools->Networking. This will allow you to see what's in a file without having to rename it.

Regardless of which approach you use to backup your data, you should do it on a daily basis.