RE: Calculating the cumulative totals for an ending result

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You say you have three tables –
Table 1 – Vehicles
Table 2 – Drivers
Table 3 – Is it Odometer or Movement? Seems like four tables.

Your query fields do not correspond the sample data.
Unit, Ending, TripDate, Movement
Unit#, Driver, Date, Business, Personal, 2008 Ending

From the sample data I can see Fred Personal = 2,500.
But I get 134 as 3.400 or 8,400 – not 7,700.

--
KARL DEWEY
Build a little - Test a little


"Chel" wrote:

I hope that I have not posted a reply twice. Thank you Karl for coming back
to me. I have figured out where you were writing your SQL stmts. I even wrote
my own. However, it does not give me a rolling total.

SELECT Odometer.Unit, Odometer.Ending, Movement.TripDate,
Movement.Movement,([Ending])+([Movement])AS Total
FROM Odometer INNER JOIN Movement ON Odometer.Unit = Movement.Unit
GROUP BY Odometer.Unit, Odometer.Ending, Movement.TripDate, Movement.Movement;

I have 3 tables in my database.

Table 1 – Vehicles contains all information pertaining to our leased trucks.
VIN, plate, Unit # (primary key), rental cost etc

Table 2 – Drivers – this is just a list of names

Table 3 – Has a look up list for the Unit # & Driver (I created a
relationship between tables 1 & 2 and Table 3) This is the table where I will
input the date, Business KM & Personal KM. I also needed to show the ending
Odometer reading for Dec 2008.

Eg:

Unit# Driver Date Business Personal 2008 Ending
133 Fred 12/31/08 10000
133 Fred 01/31/09 1000 1000
133 George 02/28/09 500 1000
133 Fred 03/31/09 1000 500
134 Joe 12/31/08 5000
134 Joe 01/31/09 500 200
134 Fred 02/28/09 1000 1000
134 Joe 03/31/09 500 200

I need to build queries that can give me that following information:-
If I need to know how many personal KM Fred has driven in 2009?
I need the query to calculate 2,500

If I need to know what the Odometer reading for unit #134 as at 02/28/09?
I need the query to give me 7,700.

My drivers give me hardcopy KM logs, so when I am inputting the information,
I only want to do it the once. Eventually, we could give them access to input
it directly and it would save me a lot of time.

Any pointers would be great.


"KARL DEWEY" wrote:

Let us back up a little. Post a sample of your data. I sounds like you have
an Excel spreadsheet per person as you did not mention a name in the list of
data fields. Is this correct?

--
KARL DEWEY
Build a little - Test a little


"Chel" wrote:

Thank you Karl for your response. I am still having difficulty however and it
is probably because I am not understanding your lingo. I am unable to change
my table column as the information that I am inputting will be received as
'Business KM and Personal KM' per unit# with the driver for the month.
My whole purpose with this database is to get away from using so many
different excel spreadsheets with a common purpose.
I need different information at different times. At the end of the year, I
need to calculate the amount of personal km any particular driver has used.
Throughout the year this driver could be driving various units. I also need
to be able to call up a particular unit# and find out as of a particular
month what the odometer reading is. It will also be helpful for tracking our
vehicles and figuring out the lease costs & repairs that have occurred
throughout the year.

From your reply below:- am I correct in assuming that you are doing this
from the select query design table?
I have the following rows in which to work:-
Field
Table
Total
Sort
Show
Criteria
or

This is where I get lost- I am not sure where to input your formula's. I
know that where is under the Total row. Do I just type the sequence in?

I just want the query to add prior month movement to the current movement
and so on.... Once I can figure out how to do this query, I can make seperate
queries in order to calculate the total personal km for the year.

Perhaps I am getting too far ahead of myself!! :)

"KARL DEWEY" wrote:

Omission ---
LastDate --
SELECT Unit, Odometer, Max([TripDate]) AS LastDate
FROM YourTable
WHERE Format([TripDate], "yyyymm") = Format(DateSerial(Year(Date()),
Month(Date()),0), "yyyymm");

SELECT YourTable.Unit, (Format(Year(Date()), Month(Date()),0), "mmm yyyy ")
& Max([YourTable].[Odometer]) - LastDate].[Odometer] AS [Mileage for ]
FROM YourTable INNER JOIN LastDate ON [YourTable].[Unit] = [LastDate].[Unit]
WHERE Format([TripDate], "yyyymm") = Format(DateSerial(Year(Date()),
Month(Date()),0), "yyyymm");

--
KARL DEWEY
Build a little - Test a little


"KARL DEWEY" wrote:

I would change the table to have these fields --
Unit, TripDate, Odometer, Purpose

Record each trip entering the above information.
To calculate last month's usage use a totals query like this --
LastDate --
SELECT Unit, Odometer, Max([TripDate]) AS LastDate
FROM YourTable
WHERE Format([TripDate], "yyyymm") = Format(Year(Date()), Month(Date()),0),
"yyyymm");

SELECT YourTable.Unit, (Format(Year(Date()), Month(Date()),0), "mmm yyyy ")
& Max([YourTable].[Odometer]) - LastDate].[Odometer] AS [Mileage for ]
FROM YourTable INNER JOIN LastDate ON [YourTable].[Unit] = [LastDate].[Unit]
WHERE Format([TripDate], "yyyymm") = Format(Year(Date()), Month(Date()),0),
"yyyymm");

--
KARL DEWEY
Build a little - Test a little


"Chel" wrote:

I am quite new to this, but I sure it is a very simple process to get to the
answer I want. I want to create a query that calculates the ending kilometers
for a vehicle - month by month. Do I perhaps need a couple of queries instead
of trying to do it all in one?

I have columns that show the unit #,month, Business KM, Personal KM, (I
calculated the movement), I also have a column that shows the starting KM.

How do I:
#1- Pull the information to show January's ending KM for a specific unit?
#2 - If I wanted to show the ending KM's for the unit in March (how would it
know to add up Jan - Mar movement)

After I have this figured out, I am going to add parameters in order for the
user to change the query to the information that they require - hope I am not
being too ambitious!!
.

Relevant Pages