[AccessD] Vehicle maintenance database (cross posted)

Here is a site with about 500 data models, with some you can get access 
mdb structures emailed.
You may have to mix and match from the examples

Barry Williams  'Kick-Start' Data Models
http://www.databaseanswers.org/data_models/index.htm

The VIN contains six sections:  over 17 digits
with a lot of info you might want to purchase a dll that breaks this 
down if useful
plus info changes yearly.
 there is a check digit included to stop transposition errors.

I would be suprised if there isn't a pay webservice to decode VIN  
accessible by soap.

The World Manufacturer Identification, or WMI, which identifies the 
country and company that assembled the vehicle.
In the United States, the WMI is assigned by the Society for Automotive 
Engineering (SAE).
The model year of the vehicle, using a single-letter code which is 
standardized for all manufacturers.
The model description, in a format chosen by the manufacturer.
The manufacturing plant, in a format chosen by the manufacturer.
The serial number of the vehicle, in a format chosen by the manufacturer.
A check digit used to validate the VIN's validity.
The check digit is calculated using an algorithm which is standardized 
for all manufacturers.

For example, the breakdown sheet for a 1996 General Motors vehicle 
offers several possibilities for body styles and engines.

A 2-door hatchback is a "2" in position 6
A 5.7 liter V-8 engine is a "5" in position 8

http://www.tedcrane.com/DISCOtech/VIN_Data.htp
http://www.autoinsurancetips.com/vin.htm
http://www.analogx.com/contents/vinview.htm
Maybe check Chilton manuals for a breakdown

VIN 1FMZU34E6XUC84xxx

1FM=  multi-purpose vehicle built by Ford Motor Company USA
Z=    relates to GVW information.  I think Z equals 5000-6000 lbs.
U34=  4-door Explorer 4x4
E=    4.0L SOHC six-cylinder engine
6=    check digit
X=    model year 1999
U=    built in Louisville, Kentucky
C84xxx= vehicle build sequence



Dan Waters wrote:

>Hi Dave,
>
>This is just me but I would combine all these tables into one.  Everything
>here defines the vehicle itself.  Make a tblVehicle with appropriate
>columns.  You might be able to use the VIN as a no-duplicate index value.  A
>related table would contain information about each maintenance event - work
>done, cost, hours, owner (maintenance requestor), mechanic, etc.
>
>Or, you might make a table using customer information, and then the vehicle
>table would be subordinate to that.  Each customer will have different
>vehicles.
>
>The shops where I take my car to get repaired seem to have fairly good
>maintenance databases.  Perhaps buying one of those would be what 
>


>you're
>looking for.
>
>HTH,
>
>Dan Waters
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>dmcafee at pacbell.net
>Sent: Friday, April 08, 2005 1:45 PM
>To: ACCESS-L at PEACH.EASE.LSOFT.COM
>Cc: AccessD at databaseadvisors.com
>Subject: [AccessD] Vehicle maintenance database (cross posted)
>
>I was given a task to quickly come up with a vehicle maintenance schedule
>database. before I get further, I was wondering if anyone already has
>something in place.
>
>If not, this is what I have so far:
>
>tblModels:
>	ModelID PK
>	Model (Camaro, Corvette)
>
>tblAutoMfg
>	AutoMfgID PK
>	AutoMfg (Chevrolet, Honda)
>
>tblMfgModels
>	MfgModelID PK
>	AutoMfgID FK
>	ModelID FK
>
>tblYears
>	YearID PK
>	ModelYear (2005, 2004, 2003...)
>
>tblAutoYears
>	AutoYearID PK
>	YearID FK
>	MfgModelID FK
>
>I'm looking at schedules that can be either light duty or heavy duty
>The maintenance schedules can also be either date based or mileage (or
>kilometer based).
>
>Something like Oil Changes, every 3000 miles or 3 months. But some items
>have only date based services and others have only mileage based service
>intervals.
>
>I was thinking something like
>tblMaintenanceTasks
>MaintTaskID
>MaintTask (Oil Change, Rotate Tires...)
>
>tblIntervals
>IntervalID
>Interval (3, 3000, 7500...)
>IntervalTypeID
>
>tblIntervalType
>IntervalType (Miles, Months)
>
>But I was also thinking of making tblIntervals, a little less normalized,
>something like:
>tblIntervals
>IntervalID
>IntervalMiles
>IntervalKilos
>IntervalMonths
>
>I have also not been told if we will be drilling down into sub model type
>(i.e. Mustang GT, Mustang LX), so for now, I am assuming that I will be
>relating these suggested service maintenance intervals to tblAutoYears.
>
>Any suggestions?
>
>TIA,
>David McAfee
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada