mwready, Author at TATEMS Fleet Maintenance Software | Page 9 of 12
mwready
Author Archives: mwready

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

Girl on a High Wire between two speeding Big Rigs

Well summer is about over and yes I hit my goal and I am able to eat pizza on movie night again 🙂

Check out this great video of World record-holding highliner Faith Dickey crossing a slack line between two speeding Volvo trucks.

Will she make it before the trucks reach the tunnel?

Volvo Trucks – The Ballerina Stunt (Live Test)

How do you get a boat with an 85 foot mast under a 65 foot bridge?

A little ingenuity…

Boat Balls 2

In other trucking, transportation and equipment news:

In TATEMS news we are working on getting a number of new features out the door. For example, the ability to attach items like scanned documents or pictures to a Work order and to the equipment itself.

I hope you have a great week!

All The Best!

Marc

How To Report Tire Costs In Your Fleet and 31 pounds so far…

Did you like the video I posted last week of our puppy Kiba playing Bang Bang you're Dead with my daughter Kelly? Here’s the link. Click here and then scroll down to see the puppy..

31 Pounds so far… and counting. My son Matthew bought the P90X DVD’s and we started the regimen on Apr 1, 2012. I have a few days left to complete the 90 program and I have to say I’ve gotten into pretty good shape compared to where I was in addition to losing 31 pounds.

I’m going to go ahead and continue for another 90 days just repeating the phase 3 part of the program and see what happens.

So I have gone from 233 down to 202, my goal is 175 (which would be pretty amazing). By the way, it’s not just the exercise, I have to restrict my calories to 1400 per day.

I gotta say I am going through excruciating Pizza withdrawals and when I pass below the 200 mark it’s time to celebrate with some Papa Johns 🙂

Has anyone else gone through P90X or had a similar experience? Leave a comment below, I'd love to hear about it.

So this week I created a short video to show you how to see what you have spent on tires for a specific date range.

TATEMS Maintenance Software Work Orders Parts Tires Report

Just to reiterate and add to what's in the video.

Click Reports->More Reports->Report Type:Work Orders

Then choose

“All Work Orders Parts Totals by Equipment #”

Then enter your date range and then if you just want to see what you are spending on tires enter the word “tire”(without quotes) in the “Part Desc” field and also in the “To” field.

Then click preview.

As long as your part description matches the word you are searching for you will see all of that description of parts installed for that date range.

You can also use a wild card asterisk * before and/or after the “Part Desc” word you are searching for to see see part descriptions that maybe include more that one word and your not sure what the other word was.

Until next time…

Thanks Again

Marc Ready
PCHelp, LTD

New Video Version 4.4.00 Parts Inventory Screen updates

I hope everything is going well for you this week.

Hopefully, this video is not as choppy as the unfinished house they are tearing down on the hill above us. More on that in a minute

I just finished up this video but updated my Camtasia Studio (Screen Cam Software) to the latest version so it might be a little bit choppy as I am just getting used to the new interface.

TATEMS Maintenance Software New Version 4.4.00 Parts Inventory Screen Updates

Last week I forgot to post the sample Terms that you can paste into the bottom of your work orders so I have added it here:

SAMPLE TERMS for WORK ORDER

I am also posting the previous Video regarding all the new Preferences settings in Version 4.4.00 below.

TATEMS Maintenance Software New Version 4.4.00 Preferences Options

So anyway someone starting building a huge multimillion dollar house on the hill above our neighborhood about 5 or 6 years ago.

It was all framed and had roofing material stacked on top of it and then it just sat there.

We figured they ran out of money or something.

Then we find out from our neighbor that they were forced to pay for all the utility lines to all the lots on the entire street. Not only that but the houses in our neighborhood that back up to the hill had contracts that said no houses would be built behind them.

So today they are chopping it up and tearing it all down… I've included some video below. It's going to be weird not seeing it up there anymore. It's really become a landmark around here that you could see for quite a distance.

Las Vegas Southern Highlands Unfinished House On Hill Gets Demolished

It may be Las Vegas but we don't go into town much, so you can see what passes for excitement around here. 🙂

Oh well. By the way it's 104° F here in Las Vegas today while they were doing the tear down…

And for you dog lovers you might get a chuckle of this video my daughter took of her trying to get
our dog Kiba to play dead. Enjoy!

Kiba's bang bang

Please let us know if you have any questions or need any help.

All The Best!

Thanks Again,

Marc

New TATEMS Version 4.4.00 Preferences Video

We released a new version of TATEMS a couple of weeks ago and in this video I go over the new preferences options in this version update. Below this video you will also see a link to see a list of all 23 items in this update.

Here is the link to get the update:

https://tatems.com/updates

And to see the list of all 23 items included in this update click the link below:

tatems.com/updates/updatelist.cfm

I hope everything is going well for you and yours as we move into summer. I'm working hard on bring you more updates and also to get our TATEMS reminders software out of beta testing.

Thanks Again

How to get 25Gb of Cloud Storage for your TATEMS data and more… But there is a catch.

DropBox

I have been using a service called Dropbox a cool cloud storage service that I have installed on my desktop computer, my Android phone and my iPad.  It's a very cool and easy way to transfer things like photos from one device to the other.

For example, I can snap a picture on my phone and instantly upload it to DropBox and then later open it from my desktop computer or iPad.

I can also use it as an extra backup for data files or any document.

The only drawaback with Dropbox is that it only gives you 2 Gigabytes of space for free.  That may be enough for most people but if you need more starage there are some other options.

Google Drive

Google just came out with a similar new cloud storage service called Google Drive that gives you 5GB of storage for free. Google says that thier service will also be searchable using Google Search technology.  Google has apps for the Desktop and Android phones but not Apple devices yet.

Note: I just went to the Google Drive site and as of 2:30 PDT Arril 25, 2012 they are saying it's not ready. Although I was able to get it on my Android Phone. But after installing it on my Android Phone, I got the same message saying it's not ready but will notify me when it is

SkyDrive

That sounds good, but if you really want more free storage space Microsoft is offering 25GB of free storage with thier SkyDrive service.  But to get the free 25 GiGs you have to have to actuallt use the service before Monday April 30, 2012

You can use any of these services to save backup copies of your TATEMS data file or any other data for that matter off site.

To get the apps for these for your Mobile device just search for the service name or click the links above to get them on your desktop.

I just added some stuff to Microsoft Skydrive to make sure I get that free 25GB of storage and I reccomend you do too.

I don't see any reason not to use all three services for ultimate safety. 

Take care and have a great weekend.

3 New Spreadsheets (with video) to link to your TATEMS data

It's been a while since I last posted. I hope everything is going well for you and yours.

It's been interesting (foiled home invasion) around our house to say the least, more on that in a minute…

But first, some extras to help you get more out of TATEMS.

I just uploaded the the 3rd new spreadsheet since I last posted.

They are numbers 65, 66 and 67 on our spreadsheets page

TATEMS Maintenance Software Spreadsheets Page

Number 65 is called TATEMSReportDriverTotalMiles-KmTraveledPerEquipment and it will show you
How many miles or kilometers a driver has driven during his time assigned to that vehicle. It displays the following columns:

Serviced By
Equip #
Work Order ID
Date Requested
Short Descriptions
Detailed Description

TATEMSReportDriverTotalMiles-KmTraveledPerEquipment Screen Shot

TATEMSReportDriverTotalMiles-KmTraveledPerEquipment Screenshot

Number 66 is called TATEMS-OpenWorkOrdersServicedByShortAndDetailedDescriptions and it will show you all the currently open work orders for all equipment along with the short description and detailed description for each open work order. It displays following columns:

Serviced By
Equip #
Work Order ID
Date Requested
Short Descriptions
Detailed Description

TATEMS-OpenWorkOrdersServicedByShortAndDetailedDescriptions Screen Shot

TATEMS-OpenWorkOrdersServicedByShortAndDetailedDescriptions Spreadsheet Screen Shot

Number 67 is called TATEMS-WorkOrdersTotalsByMonthPerEquipment and it shows you all the costs for your work orders for each piece of equipment for each month. It will display the following columns:

Equip Num
Month
Year
Labor Totals
Parts Totals
Misc Totals
Discounts
Tax
Total Costs

TATEMS-WorkOrdersTotalsByMonthPerEquipmentSpreadsheetScreenShot

Work Orders Totals By Month Per Equipment Spreadsheet Screen Shot

Once you have your data linked from TATEMS to one of these Excel spreadsheets you can filter and sort it any way you wish.

I may create a video to show you these spreadsheets in action and how you can link them to your data. But in the meantime you can check out this previous video to help you get started.

So anyway, a couple days ago we had an attempted home invasion next store.

These people pull up in an old Chrysler Sebring and ring our doorbell first.

Of course our big dog Maya starts barking like crazy and my wife looks out and there is nobody there. So the dogs must have scared them off.

Then a few minutes later she looks out from upstairs and sees this big dude banging on the next door neighbors door. And he's jiggling the handle acting all agitated.

That neighbor does not live there full time and we know he's not home because we haven't seen him for a couple of weeks.

So my wife goes outside and says “What are you doing?” He says “The guy owes me money and I know he's in there” My wife says “I think you have the wrong house”.

He has a female accomplice with him and she looks like she comes from the other side of our neighbors house.

My wife goes back in the house and then she watches out the window and they take off in their car with no license plates on it.

So she goes back outside and notices that our neighbors door had a huge uneven hole about 2 feet in diameter up next to the lock.

Now she's worried that they saw her and she tells me our neighbors not going to be very happy.

So anyway we call the cops out and they talk to us but there isn't much they can do because the neighbor is not there to actually file a report.

They do enter his house and do a little CSI work. They also tried to jery rig the door so that you don't see the big gaping hole from the street.

So we'll see what becomes of it and thank God we have a big dog as a deterrent, although I'm seriously considering adding some other forms of home protection now.

Have a great week and feel free to Like, Tweet or comment below!

Marc

TATEMS Reminders Add-on Program Video Demo and a new spreadsheet Video too!

I hope you had a nice Christmas and New Year!

This first video is a demo of a new spreadsheet requested

It is called

TATEMSEquipPartsAndLaborTotalsWithCustomerAreaDeptLocationMakeModelYearAndCurrentODO

Another nice short (but descriptive) name, I know…

It was created because the client wanted to be able to see Parts and Labor Totals for Model and or Year of Equipment and other fields like Equipment Location.

It is spreadsheet number 64 at:

https://tatems.com/spreadsheets

I forgot to show you how to filter it by the columns at the top but you can see how on one of the other videos I made here:

http://blog.tatems.com/new-equipment-list-with-out-of-service-column-plus-video

at about the 40 second mark.

And this second video gives you about a 4 minute demo of the new TATEMS Reminders program that we are working on.

I did not use Zoom on the TATEMS Reminders video so you may want to click the Full Screen button on the lower right side of the player.

Let me know if you would like to see a custom spreadsheet created that we can share with our users too!

Make sure you click the Facebook like button and leave a comment if you would like to try the beta version of this new add-on.

Thanks Again And Have a Great Week!

Truck Driving Dog…Music Video of the Year?

Someone sent me this video and I thought it might be something you all would like too.

Enjoy!

We are almost done with our free add-on for current TATEMS owners or trial users
called TATEMS reminders.

It's a program that will run in the windows system tray without TATEMS having to be open.

It will display your reminders quicker than the reminders popup inside TATEMS and it will email any segment to any number of recipients you want on a daily, weekly or monthly schedule.

You'll also be able to email selected reminders lists to anyone on demand.

I'll post a demo video here next week, and maybe a download link if it's ready.

Thanks Again,

and Have a Merry Christmas!

New Equipment List with Out Of Service Column plus Video

It's been a while since I posted. I hope everything is going well for you and yours.

In this post a new spreadsheet and a Kiba update…

Cristy, one of our California clients asked for an equipment list that shows
whether each piece of equipment is out of service or not.

So the Excel spreadsheet I created will link to and display your TATEMS data just like the other ones posted at:

https://tatems.com/spreadsheets

This is number 63 on the list an is called oddly enough:

TATEMS-Equipment-List-With-Out-Of-Service-Column

I know, pretty original right?

The columns included in the spreadsheet/report are:

Equip#
Our of Service?
Desc
Make
Model
Year
VIN/Serial#
License
Sort Order#

Once you refresh your data you can sort and filter any way you want to.

This video shows you the columns and how to link your data if your data is on a network or on a 64 bit version of windows:

Now for a little Kiba (our puppy, now 8 months old) news.

A few weeks ago we had a scare…

He got a hold of my pack of trident gum on my nightstand. The Gum has “Xylitol” in it which is poison to dogs.

We took him to the vet and they forced him to regurgitate and took his glucose readings…

He came through it with flying colors and is fine and hyper crazy as ever…

A few days ago we got some snow in the hills near our house here in Las Vegas. So we took him and Maya(Golden Lab) up to his first snow (about a 15 minute drive) and he had fun biting it and romping around a bit. We couldn't get very far off the highway so we had to keep them on leash.

Kiba Maya And Me (Marc Ready )In-The-Snow- between Las Vegas and Parump Nov-5-2011

Kiba's first snow experience!

Until next time…

By the way if you have a request for a custom spreadsheet I can create and share let me know.