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
:
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!!
:
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
:
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
:
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!!
