As part of researching/looking for a car - I got an app to d/l the entire Redbook/au website (spec/price sheets only) - totaling about 4gb in saved text files @ about 12kb each.

I wrote an app to cycle through everyone of these and separate them into 53 columns per vehicle (brand, model, equip, length, fuel,etc,etc,etc) to be imported into access for quering/filtering purposes.

Now im constructing some simple queries, but i havn't done any sql in 3 years and im having trouble finding the answers i need on sql tutorial sites :(

The query im having problems with atm is a fuel usage filter:

What it needs to do is ask user 'Max Fuel Use-City?', then strip the existing 'Fuel_City' column of "L/100km" (since its listed as XX.XXL/100km so that it can do FuelUse < Fuel_City.

What I have tried is:

SELECT Cars.*
FROM Cars
WHERE Replace([Cars.Fuel_City],"L/100km","")<[Max Fuel Use-City?];

---- ^ that says too complex

SELECT Cars.*
FROM Cars
WHERE (Replace([Cars.Fuel_City],"L/100km","")<[Max Fuel Use-City?]) AS MaxFuel;

---- ^ error

SELECT Cars.*, Replace([Cars.Fuel_City],"L/100km","") AS MaxFuel
FROM Cars
WHERE MaxFuel<[Max Fuel Use-City?];

---- ^ that wont treat 'MaxFuel' as an existing component, and brings up and input box for what MaxFuel equals

Any ideas? I've thought about trying a LIKE command, but not sure how id get it to say MaxFuel < * in *L/100km (since * could be 1-4 characters)

*Once ive got the queries setup, if anyone is interested in the 30mb 35,357 vehicle database (it may be missing a few though, depends how effective the app was that spidered the website), I will upload it - however atm it only has data about specs, not prices (will import as separate db later).

thx

dude f***ing hell, just buy a car

it's not that hard

dude f***ing hell, just buy a car

it's not that hard

ive already pretty much decided on either a subaru liberty or forester (whichever comes up first) - but now i need to finish this db coz ive already put too much time into it.. all it needs is some queries to filter stuff and it could be quite useful

if anyone wants the mdb as is to put your own search functions in, ill upload as is

subaru.com.au.

Liberty 2.5i - 9.3 L/100
forester 2.5 - 9.7 L/100

wow, that was hard!

Surely you should put more thought into the car other than L/100km? I mean they're different enough cars that one would probably suit your needs more than the other, if nothing else the forester is cheaper @ $30k driveaway.... that's base model of course.

I could take a look at it for you but I can't guarantee any sort of turnaround.

My gut says that you are trying to do too much in a single SQL statement, resulting in unnecessary complexity.

Build up a view ... err, query ... that organises the data how you like it, then perform a select statement on it with criteria.

This freaks me out a bit:

Replace([Cars.Fuel_City],"L/100km","")<[Max Fuel Use-City?];

It looks like you have a string field that is city mileage, written something like:

"2.1L/100km"

This is a prime candidate for 'cleanup' in a sub-query, as you actually need to not only strip out the suffix but ALSO convert it to a float.

You can do it all in a single statement but it will unnecessarily f*** with your head...

Go to a subaru dealership and test drive both. Whichever you enjoyed driving the most - buy....

$sql = 'SELECT * FROM `cars` WHERE `car` LIKE \'subaru\' AND `cityuse` < 10';

this is what phpmyadmin spat out when I stuck in some random s***, of course it returned no values from my database coz there's no data in it so I don't actually know if it works. It runs on php/mysql/apache though not access because I hate access and know bugger all about it but I'm sure it might be adapted.

Go to a subaru dealership and test drive both. Whichever you enjoyed driving the most - buy....

he might enjoy driving a v12 jag but if you stop for fuel every 10 km/s it's not going to be fun for very long is it? This is what the OP is getting at.

last edited by whoop at 15:08:36 25/Nov/07