How do I set up a monthly vehicle maintenance report that total m.

[Guest]

Hello,

Your assistance is greatly appreciated ... USDA new employee working with
an existing database in Excell that is not functional. Would like to convert
the below database to an Acces database system.

Vehicle License # Year, Make, Model, Color, Date of Annual Inspection,
Date of Last Oil Change (ability to project 3, 000 out next oil change),
Mileage Last Oil Change, Vehicle Assigned to.

DATE Ending Mileage from Previous Month, Ending Milage Current Month,
Total Miles of Travel, Total Fuel Charges, Maint. Costs, Maintenance
Description, Date of Maintenance, Mileage Maint.
Complete, Vendor, Comments


October-04 (Beginning of FY 2004-05)

Sub-Total
RUNNING TOTAL
November-04

Sub-Total
RUNNING TOTAL
December-04
Sub-Total
RUNNING TOTAL
January-05

Monthly reports are required from database.

Thank-you.

Dawn Thomas, Secretary, USDA
APHIS CCEP/PPQ

 

[John Vinson]

Hello,

Your assistance is greatly appreciated ... USDA new employee working with
an existing database in Excell that is not functional. Would like to convert
the below database to an Acces database system.

Vehicle License # Year, Make, Model, Color, Date of Annual Inspection,
Date of Last Oil Change (ability to project 3, 000 out next oil change),
Mileage Last Oil Change, Vehicle Assigned to.

DATE Ending Mileage from Previous Month, Ending Milage Current Month,
Total Miles of Travel, Total Fuel Charges, Maint. Costs, Maintenance
Description, Date of Maintenance, Mileage Maint.
Complete, Vendor, Comments

The first thing to realize is that Excel is an excellent spreadsheet
program - but it is NOT a relational database! Access and Excel are
*very different programs* and require a different mindset.

Your Access database will need to contain several tables, such as:

Vehicles
LicenseNo <Primary Key>
VehicleYear ' Year is a reserved word, don't use it for fieldnames
Make
Model
Color

Employees
EmployeeID ' Primary Key
LastName
FirstName
<other bio data>

VehicleAssignments
LicenseNo
EmployeeID ' joint 2-field Primary Key
AssignedDate
<maybe EndDate if you want to reassign vehicles but keep history>

OilChanges
LicenseNo ' link to Vehicles
OilChangeDate
<other fields, e.g. amount of oil needed, observations>

Maintenance
LicenseNo
MaintenanceDate ' joint 2-field primary key
Cost <currency>
Description
Mileage
Vendor
Comments

Milage
LicenseNo
MilageDate ' joint 2-field Primary Key
Milage
MonthlyFuelCost

Or you might even want to put in a Fueling table with a record for
each fuel-up.

The monthly totals would NOT be stored in any Table; instead, you
would use Totals queries to sum up the milage, fuel, and maintenance
costs.

Get the properly normalized tables (as modified from my suggestions
above) correct first, and then the calculations and reports will
become much more straightforward. One trap to avoid is the common one
of designing the database structure to fit the desired reports -
that's backwards! Design the tables to fit the logical structure of
the data; *then* generate the reports from Queries combining the table
data appropriately.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps