I have an Access database from my auto repair business that contains all of my customer information as well as all of the vehicle and repair history performed over the last 10 years. I would like to have a script written in ASP that would run on my local server that would provide me with some information in that database.
I'm looking to enter a vehicle licence number in a form and have it return to me a page that lists about 10 items that we track, such as oil change, brake inspection, fuel filter, etc. I have a text string for each item that we would search for. Each of the items that we are tracking would be assigned a mileage and time interval to determine if it has been too long between services or over a certain mileage since that last service. I would need an admin interface to specify the service name, text string to search for, mileage and number of days to assign to each item.
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
For example, I want to search for the latest oil change in a cars history (it may have several, we're only concerned with the last one performed) and I want to know the date and mileage that it was done. I set the oil changes to be done every 3000 miles or every 90 days. The results for each item tracked would fall into one of three categories,
**1. -** never performed.
**2. -** performed within the specified time and mileage and not due for service yet.
**3. -** the service is now due again because it has been over the specified time or mileage.
The results page would look something like:
Vehicle Data for License #3LFB079
Rancho Palos Verdes, Ca 90275
1995 Acura Legend
Driven an average of 17 miles per day
Last serviced at 121,500 miles 91 days ago on 1/1/2007
Current estimated Mileage - 123,047
**Services due now**:
Coolant Service - last performed 5/7/2004 at 74,567 miles, due at 98,567 miles or by 5/7/2004, OVERDUE
Fuel Injection Service - last performed 4/1/2005 at 89,441 miles, due at 104,441 miles or by 4/1/2006, OVERDUE
**Sevices not due yet**:
Oil Change - last performed 1/1/2007 at 121,500 miles, due at 124,500 miles or by 4/1/07, not due for another 1,453 miles or 8 days.
Tire Rotation - last performed 1/1/2007 at 121,500 miles, due at 127,500 miles or by 7/1/07, not due for another 4,453 miles or 98 days.
**Items with no history**:
Fuel Filter - no records shown
Brake Fluid Change - no records shown
etc, etc, etc.......
Name - **[url removed, login to view]** (Microsoft Access)
4 tables involved:
**1. Customers:** (contains customer information)
cust_id - Customer ID # (unique number)
firstname - Customers first name
lastname - customers last name
**2. Vehicle:** (contains vehicle information)
cust_id - Customers ID # (same as cust_id in the Customers table)
vehicle_id - vehicle id # (customers have more than one vehicle, unique number for each car)
licence - the cars licence # (possible to have 2 cars with same license)
MilesPerDay - the average number of miles that this car travels per day (defaults to 32 on first visit)
**3. History:** (history for each car, many listings for each car pointing to individual repair orders in the Orderlist table)
licence - the cars licence # (same as in Vehicle table)
cust_id - Customers ID # (same as cust_id in the Customers table and in the Vehicle table)
vehicle_id - vehicle id # (same as vehicle_id in the Vehicle table)
recno - Repair order number (can have multiple repair orders per car)
dateposted - the date of each repair order
odom_in - the cars odometer reading (mileage) when the car was in for service
lineno - a number that was originally assigned to the repair order as an estimate number
**4. Orderlist:** (line by line information for each repair order)
workid - same as the lineno from the History table
description - a description of the work done on this repair order. there are many lines of information for each repair order.
The only information provided by the form would be the licence number, so the query would need to join the tables "Vehicle" and "History" where the license # is the same, also pull the customer number from the from the "Vehicle" table and open the "Customers" table with that info. Then, open the "Orderlist" table with the workid number from the "History" table.
Right now, I think I've provided enough info to gather some interest from you all, if you need more info, please ask. The final results will be an input form that asks for a cars license number, an output page as shown above and an admin page that allows me to specify text strings, items and intervals.
* * *This broadcast message was sent to all bidders on Thursday Mar 29, 2007 6:55:20 AM:
Thank you for bidding on my ASP project. Several of you have requested more information on the database and I have added a zip file containing a sample with some data. I have also had several requests to use asp.net or to change the database. I cannot make changes to the database, only access it for information. I am also not able to run asp.net on my server.
Thank You and have another look if you would like.
ASP on a web server