calculating data in one field only

  • Thread starter Thread starter THuff
  • Start date Start date
T

THuff

I am setting up a database to track Vehicle Expenses for my company.
In this database exist the table: tblVehDetails
In this table exist the following fields:
DetailID as Primary Key
VehNum as long integer
Date as Date/time
Gallons as double
Cost as currency
Expenseid as long integer
Vendorid as long integer
Odometer as long integer
Notes as memo
VNid as long integer

I need to calculate the miles the vehicle has traveled
over the course of time. More specifically every month.
I understand how to calculate two different fields within a query
but do not know how to calculate data in the same field.
Ultimately the miles traveled ends up on a monthly report and it may be
within the report that this calculation needs to take place.
At this moment I am drawing a blank on how to proceed.
Thanks in advance for any enlightening information you may provide.
Terry
 
I would use a totals query. Something like the following.

SELECT VehNum,
Format([Date],"yyyy mm") as Monthname,
Max(Odometer) - Min (Odometer) as Miles
FROM tblVehDetails
WHERE [Date] Between #1/1/04# AND #1/31/04#
Group By VehNum, Format([Date], "yyyy mm")

You could get multiple months for each vehicle by setting the date range to
longer intervals.
Between #1/1/03# AND #12/31/03#
would give you monthly miles for each month of the year for each vehicle (as
long as there was at least one reading in the month for the vehicle)
 
I am setting up a database to track Vehicle Expenses for my company.
In this database exist the table: tblVehDetails
In this table exist the following fields:
DetailID as Primary Key
VehNum as long integer
Date as Date/time
Gallons as double
Cost as currency
Expenseid as long integer
Vendorid as long integer
Odometer as long integer
Notes as memo
VNid as long integer

I need to calculate the miles the vehicle has traveled
over the course of time. More specifically every month.
I understand how to calculate two different fields within a query
but do not know how to calculate data in the same field.
Ultimately the miles traveled ends up on a monthly report and it may be
within the report that this calculation needs to take place.
Click to expand...

A Totals query based on your table, grouped by VehNum, Year([Date])
and by Month([Date]), will help here; you can get the milage for the
month with an expression like

MilageForMonth: Max([odometer]) - Min([odometer])

You can also calculate total gallons using Sum([gallons]), total cost
as Sum([cost]), and so on.

Notes, and probably VendorID and ExpenseID, would probably be specific
to a single record and therefore should not be included in this totals
query.
 
Back
Top