Volunteers Sought To Help Build UOA Database

[TomJones76]

In previous threads I've discussed creating a UOA database which will allow our community to organize *ALL* of the UOA data we have.
I've got the database format worked out, and I have an Excel spreadsheet that I use for initial data gathering.
I can use scripts to pull data from the Excel spreadsheet into my Access database without much manual intervention.
I'm happy to do all the work myself, but we have a very healthy collection of UOA, especially as our community has grown, plus my wife doesn't really understand me doing two hours of unpaid data entry every night...
I currently have our 51 oldest gasoline UOAs. Some of the labs from back then have changed formats...
I've also gathered 9 of our higher-mileage UOAs from various dates and every single Ford Duratec 30 analysis I could find, which was 22 of them.
I figure I can get this done months earlier if a couple of you guys volunteer to enter in a dozen or so UOA each into my spreadsheet every now and then, which I can then integrate into the database to share with the rest of the board.
If you'd like to get started, let me know, in thread, by PM or by emailing tomjones762003 [[[at]]] yahoo.com.
I can give you a time range for UOA to collect, and if you like I can even give you the URLs for the ones you'll be assigned.
My current format follows. Certain data was censored and certain fields abbreviated due to the fact that it would mess up the chart.

code:

---

Date 3/13/06 3/22/06

Lab Schaeff Dyson

***Vehicle ID***

Make Mercury Chevrolet

Model Sable Prism

Year 2002 2000

Engine 3.0 24V 1.8L 4-cyl (1ZZFE)

Miles on Vehicle 57454 108196

***Oil Identification***

Oil Brand Exxon Castrol

Oil Series Superfl German Syntec Lot M02xxx

Oil SAE J300 5W20 0W30

API SX SL SL

Miles on Oil 9848 4786

Months In Service 7 4

***Elements***

Antimony Sb NR NR

Cadmium Cd NR NR

Vanadium V NR NR

Aluminum Al 6 1

Chromium Cr 0 0

Iron Fe 23 6

Copper Cu 6 1

Lead Pb 1 1

Tin Sn NR 0

Molybdenum Mo 128 5

Nickel NR 0

Manganese Mn NR 1

Silver Ag NR 0

Titanium Ti NR 0

Potassium K NR 0

Boron B NR 1

Silicon Si 16 10

Sodium Na NR 1

Calcium Ca 1262 2959

Magnesium Mg 5 129

Phosporus P 498 715

Zinc Zn 619 860

Barium Ba NR 0

***Viscosity***

cST @ 100 C 7.02 11

SUS @ 210 F NR 62.7

***Misc***

Flashpoint (F) NR 375

Fuel % N 0.5

Antifreeze % 0 0

Water % 0 0

Insolubles % NR 0.2

Sulfur 7 NR

Oxidation 10 NR

Nitration 24 NR

TBN NR 3.2

Ferrous Debris NR NR

Fuel Soot % NR NR

Glycol NR NR

ISO Code NR 17/14

Additives LC NR

URL SNIPPED FOR DISPLAY PURPOSES

Notes SNIPPED FOR DISPLAY PURPOSES

Location Ohio Arizona

***Filters used***

Air Filter NR STP (has about 30K)

Oil Filter NR K&N HP-1003

***Particle Count***

>2 micron NR 2146

>5 micron NR 795

>10 micron NR 220

>15 micron NR 85

>25 micron NR 20

>50 micron NR 1

>100 micron NR 0

---

 

[yugrus]

Count on me for this one, Tom.
I just wonder how do you make this database searchable to web users? I mean, there has to be a web interface built around the database. Also, Access is the great little database for a personal usage, but it is not quite appropriate for application like that.
Who will host and maintain this app?
A suggestion, if I may. I'd say that there should be a bit more extensive data categorizing, like engine or oil used (say,
Castrol->Syntec->5w30
->5w20
->GC ->0w30 etc.)

It will help a lot in implementing different search criteria...

 

[TomJones76]

Yugrus,

Thanks for volunteering, and good questions.
My long-term goals do include putting the database somewhere web searchable, but I'd rather get the project rolling FIRST, then worry about how to do queries on the web.
You make a very good point about Access. At my workplace I've become keenly aware that it is, in fact, a toy compared to MS SQL Server or Oracle... or even MYSQL. However, for the early data gathering part of the project, it will suffice.
As far as data categorizing, you're right. I'm considering creating a system for classifying oils, cars and engines via dropdowns down the road, but that's a "later on" feature.
My initial way of displaying the data will be sharing the Access DB with folks or providing CSVs of the data suitable for your choice of spreadsheet application, or directly into Excel.
Also, displaying the data in pretty tables, perhaps for posting in the site or forums. Lots of ways to do that.
PM me your email and I'll send you the blank spreadsheet and a list of target UOAs.

 

[yugrus]

PM sent

 

[LarryL]

Microsoft SQL is free. If you passed out a blank Excel spread sheet with the columns defined, for people to fill in, it might make the process of filling the database easyer. Records in the database from the spread sheet would be rejected if the defaults in the database were not met, and doners could correct the error instead of you, and return the corrected spreadsheet to upload.

AOL instant messaging could be setup to pass the spreadsheets back and forth. Their limit is 16MB, which for this stuff is no limit at all.

 

[TomJones76]

I'm basically handing out the Excel spreadsheet to any volunteers, then doing what you say.
As far as data transmission, I'm sticking to email for now, so that I can do this on lunch breaks at my day job. Not a bad idea, though, if my job wasn't so corporate.

 

[427Z06]

I would of liked to have seen city+state for location data. Quite a bit different weather between Amarillo/Brownsville, San Francisco/Palm Springs.

 

[TomJones76]

Good point.
I'm in Ohio, and there's a world of difference between winters in Columbus and Cleveland.
Going forward, I'm going to put examples on my spreadsheet in the format:
Akron, OH, USA

I just hope none of you Texans take offense to not being considered a separate country...
By the way, I've got two volunteers so far.

 

[DNS]

quote:

---

Originally posted by LarryL:
Microsoft SQL is free. If you passed out a blank Excel spread sheet with the columns defined, for people to fill in, it might make the process of filling the database easyer. Records in the database from the spread sheet would be rejected if the defaults in the database were not met, and doners could correct the error instead of you, and return the corrected spreadsheet to upload.

AOL instant messaging could be setup to pass the spreadsheets back and forth. Their limit is 16MB, which for this stuff is no limit at all.

---

Microsoft SQL is not free. Microsoft does have a free free database similar to SQL called MSDE however, it doesn't scale well for a loarge number of users. If you're looking for something that is going to be web based, MySql would be better because a great number of hosting services support it.

 

[LarryL]

Microsoft SQL Server Express Edition is free. It's a full feature version that can scale to anything you like. The Express version will certainly handle your needs. You are limited to 4GB's, or about 4,000 MB's, plenty of room for the future.

For a front end you can try Visual Studio 2005 .Net Express version, also free. It all works on Windows and costs nothing. And you can import Excell or Access and the VB.Net front end in VS.Net can talk to all three at the same time, and export all the data back to Excell or HTML or whatever you like. It's an easy environment and you don't have to go anywhere else for any of the pieces.
Free full feature version of SQL Server
Visual Studio 2005 with VB.Net Express Edition

 

[TomJones76]

LarryL,

THANKS. I knew about MSDE, but SQL Server and VS2005 Express being free was a new one to me.
I will wait until I have a little more data before getting all fancy on the user interface end, but once again, THANKS. Neat stuff.

 

[LarryL]

You can build a database and then add a user interface and write no code at all. You create a VB project, add a form and drag a table onto the form. All the edit boxes and labels will automatically be generated, and all the logic to add, update, edit and delete will be generated automatically, too.
When you're ready to start, send my a table definition and I'll start the project and pass it back to you. And I can help to make sure the table is well designed so you can get back reports that will be useful.

 

[TomJones76]

LarryL,

Cool.
The main table is up to 78 UOAs.
That's the first 78 gasoline UOAs on the board, from day 1 to October 30, 2002.
I also have 37 assorted UOA not in the main table and my volunteers are working on another 30.
What I have found helpful for my own purposes is making a form to enter the data for each of the big labs so that I can just tab through quickly, which writes to my main table.
Got OAI, Blackstone and Schaeffer's forms so far.
I had been distributing just the Excel file to volunteers. I'd LIKE to send out a model ACCESS database, but I worry about training issues.