Time Clock Data Report

[CharissaBelle]

I work for a trucking company and we are attempting to move into the current century and go digital with our drivers time cards. We have installed electronic tablets into all the trucks this year (per government regulations) and were able to add a form through the tablet software which a driver can select either "start" or "end", enter their trip number and it fills in the driver's name, truck used, current odometer, and the date/time. These entries can be exported to a csv.

So I've created a table in Access that has the following fields:
Driver


Shift (Start or End)
TripNumber
TripDateTime
Tractor
Odometer

I have an append query which uploads the data from the csv file into Access exactly as it comes to us from the tablet software.

I have the data now I'm looking to get some reports run on it. I want to see the start of a trip, the end of a trip, and how many miles the driver traveled. I'm stuck. I know I need to query. I was thinking I need to somehow get the start and end in the same record, but I can't seem to figure out how to do that. Sometimes a driver misses putting in an ending entry or accidentally puts in two starting entries. Now that I have the data, I don't know how to manipulate it so it's usable. Any help is appreciated.

[ranman256]

welcome to the time card club. Users are always forgetting a punch ,thus screwing up the times.
I usually run a check for missing punches. These records are flagged as possible errors and shown to the manager for correction.

[ssanfu]

I would have a "FirstName" field and a "LastName" field instead of one field for driver name.

No two trips will have the same TripDateTime for the same driver, so you might try a query like this

SELECT tblTimeCards.Driver, tblTimeCards.TripNumber, tblTimeCards.Shift, tblTimeCards.TripDateTime, tblTimeCards.Tractor, tblTimeCards.Odometer
FROM tblTimeCards
ORDER BY tblTimeCards.Driver, tblTimeCards.TripDateTime;

[CharissaBelle]

welcome to the time card club. Users are always forgetting a punch ,thus screwing up the times.
I usually run a check for missing punches. These records are flagged as possible errors and shown to the manager for correction.

Haha! Indeed. Do you have any resources or websites that go through how to run these checks? Our payroll lady inputs the schedule on a daily basis. Driver_ID, Trip_ID, & ScheduleDate. I've been able to run a comparison query of sorts that shows where there are "schedule" entries without time card entries. I just don't know how to make this into something that she'll be able to use.

Also, I'm wanting to somehow calculate the miles driven in the trip so I can see if a driver reported he ran a trip but then only went 4 miles or something like that. I want the odometer at the end of his shift minus the odometer at the beginning of his shift. Any ideas how to do that since they are two separate time card entries?

[CharissaBelle]

One other thing, a driver may start a trip on one day and finish the trip on another day. Like start at 23:00 on a Friday and finish at 08:15 on Saturday morning.

[CharissaBelle]

Zonar Timecard Export.zip

And one more thing, I am attaching a sample spreadsheet of data that comes from the tablet and gets uploaded to Access. There aren't very many entries because I didn't save the last import I did.

[isladogs]

I haven't looked at your file but hopefully this will help

I want the odometer at the end of his shift minus the odometer at the beginning of his shift. Any ideas how to do that since they are two separate time card entries?

To do this, use a subquery. Have a look at the explanation on Allen Browne's website - the meter reading example is similar to your situation: http://allenbrowne.com/subquery-01.html

One other thing, a driver may start a trip on one day and finish the trip on another day. Like start at 23:00 on a Friday and finish at 08:15 on Saturday morning.

Dates & times are actually stored as numbers (double datatype)
Days are whole numbers (days since 30/12/1899) and times are decimals (06:00 or 6am is 0.25)
For example: typing the following into the immediate window, gave me the results shown

?Now;     CDbl(Now)
30/01/2018 20:36:02        43130.8583564815

So to get the time difference as a number you would calculate

CDbl(ShiftEnd) - CDbl(ShiftStart)

where the date & time are both recorded for start/end of each shift
Then convert back into time values. Lots of possible ways to do this
For example, say you get a result 0.3
Hours: 24*0.3 = 7.2
Hours&Mins: Hour(0.3) & ":" & Minute(0.3) = 7:12

[CharissaBelle]

Have a look at the explanation on Allen Browne's website - the meter reading example is similar to your situation: http://allenbrowne.com/subquery-01.html

Okie dokie. I can see where this could be my solution. I'll work on going through it and applying it to my db. Thank you.

If anyone else has any resources, I am open to as many suggestions as I can glean from you all.

[ssanfu]

I am lost right now. You posted your dB, but there are only tables, queries and a macro. No forms??
And I cannot figure out the relationship between table "ZTimecard" and the rest of the tables.
BTW, I would renames table "ZTimecard" to "TimecardMaster" and "1Zonar" to "TimecardTemp" or "TimecardImport".


AutoNumber
----------------
Purpose Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key field.

Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques



I created relationships between the tables I and added suffixes to the PK/FK fields to make it easier to see the relationships:

Note that "State" is a reserved word. (See Problem names and reserved words in Access)


Have you or did you design the dB on paper, whiteboard, cardboard, etc BEFORE jumping into Access?

I first thought "TerminalCity" is where the trip ended (which didn't make sense to be in the Employee table), but maybe it is the location/facility that the employee works at/is assigned to?

What are fields POTrip1, POTrip2, POTrip3 & POTrip4?? These are repeating fields, so they should be in their own table.


About the tablet in the Tractor.... How do you plan to handle when you have multiple drivers with the same name? ie Jim Smith or John Brown or ....

[CharissaBelle]

The purpose of the db I attached is for testing and learning how to handle the new timecard system. Eventually, the tables and reports will be added to our "real" db which is much more complete. The main thing I'm trying to figure out and learn is how to get the data to be usable and normalized as part of our Access db. Trying to find the total trip miles (odometer at end of trip minus odometer at start of trip). What I would like in a report is the driver, departure date, trip number, truck number, beginning odometer, ending odometer, and total miles. That is the puzzle I'm trying to work out using the data that comes from the tablet software.

I am lost right now. You posted your dB, but there are only tables, queries and a macro. No forms??

I haven't created any forms in this db. The "real" one for our company has forms for inputting. For my "testing" one, I don't need any forms because no data is being entered.

And I cannot figure out the relationship between table "ZTimecard" and the rest of the tables.

There isn't a relationship between ZTimecard and the rest of the tables. ZTimecard is just the table I use to import the data from the tablet. I expect to get rid of 1Zonar once I get the hang of exporting from the tablet and importing in to Access. For now, I'm importing to 1Zonar and then using an append query to add the records to ZTimecard. I know it's redundant, but for now I need to do it. I do have all the relationships built in the real db, but deleted them in this one and am just creating the relationships when I create queries.

Have you or did you design the dB on paper, whiteboard, cardboard, etc BEFORE jumping into Access?

I did design everything on paper with the "real" db. I worked with multiple developers and was part of a Access Users Group that met monthly while I was designing. Once I had done everything I could, I hired a developer to go through, clean up, and finish it. I'm trying to figure out this part on my own to save cost and to learn.

I first thought "TerminalCity" is where the trip ended (which didn't make sense to be in the Employee table), but maybe it is the location/facility that the employee works at/is assigned to?

What are fields POTrip1, POTrip2, POTrip3 & POTrip4?? These are repeating fields, so they should be in their own table.

There are reasons for all of this. I did go through this with other developers and this was the best way we could come up with to do it.

About the tablet in the Tractor.... How do you plan to handle when you have multiple drivers with the same name? ie Jim Smith or John Brown or ....

I have control in the tablet or what each driver is named. I can add a middle initial or whatever so it would still be a unique value. I can also do the same thing in Access.

[ssanfu]

Apologies... nothing was said that your dB was an example/test dB. If I had known I wouldn't have spent so much time on it.

ridders52 posted a link to Allen Browne's site that I used (a long time ago - almost 19 years) to create a dB to track mileage, total gas cost, etc.
I wrote a few UDFs to handle the calculations. (Module1)


Good luck with your project...

[CharissaBelle]

Sorry. I meant to say that it was a test db in the original post and must have forgotten. Thank you Steve! Your advice is very much appreciated. This has been super helpful.