Question regarding query/calculation :)

[dragonslayer]

Hi, I am new to this forum an quite new microsoft access !
It may sound confusing but here we go:

Basically i have two tables (part of my car ad database) in which am utilising to perform a query/calculation/lookup - i don't know what to call it

The first table is called "car valuation" - and stores pre entered data on a cars:
(these are the fields)
CarValuationID (PK)
Make
Model
Type (SUV, sedan...)
RRP.
ValuationPrice (this is where i want the actual query to go)

The other table is called "Ad Car Details" and stores information on the users car in which they want to post
(these are the fields - that are relevant to the question)
AdCarDetailsID (PK)
CarValuationID (FK)
Make
Model
Type
YearOfProduction

The purpose of the valuation system is to analyse the make, model and type of car combination the user entered and match it with a corresponding "pre entered" combination (car valuation table). Obviously it is impossible to store every car on earth in a DB so if the combination of make model type that the user entered does not exist the valuation will not either (i.e. it will not appear on the form where the user will decide the price they wish to make for their car)

I lack skill in programming, however have made following expression in query builder (i don't know if this is the right thing to do)
- also when i try to run it, it says an error about parenthesis (or something along that line)

here is the expression i build (sorry for the length) - it is basically consists of a process to match the users fields with the pre stored ones then run a IIf function to calculate the appropriate value of the vehicle depending on the type and year it was made:

IIf ([AdCarDetails]![Type]=[CarValuation]![Type] And [AdCarDetails]![Make]=[CarValuation]![Make]And [AdCarDetails]![Model]=[CarValuation]![Model] , IIf ([CarValuation]![Type]in("sedan", "hatchback", "wagon"), [CarValuation]![RRP] - ((( (Year () - 1) - [AdCarDetails]![YearOfProduction]) * [CarValuation]![RRP] * 0.06) + (Year () -( Year () - 1)) * [CarValuation]![RRP] * .24) , IIf ([CarValuation]![Type] = "ute", [CarValuation]![RRP] - ((( (Year () - 1) - [AdCarDetails]![YearOfProduction]) * [CarValuation]![RRP] * 0.15) + (Year () -( Year () - 1)) * [CarValuation]![RRP] * .30), "" ) ) «Expr»

Please give me advice/alternatives to why it is not working and if it the most appropriate way to achieve the function...
Thanks, regards Sam

 

[RainLover]

Sam

You have the same details in two Tables. Make, Type etc.

I would suggest that you create a table that store all this infomation.

Make
Model
Type
YearOfProduction.

Naturally this table would also have a Primary Key. I would suggest AutoNumber.

Then the other tables need only store the Primary Key of this Table as Foreign Key in the Others.

Then in your Query if you join the Tables by the Foreign Keys you have a perfect match.

From there your calculations may become less complicate.

 

[dragonslayer]

Sam

You have the same details in two Tables. Make, Type etc.

I would suggest that you create a table that store all this infomation.

Make
Model
Type
YearOfProduction.

Naturally this table would also have a Primary Key. I would suggest AutoNumber.

Then the other tables need only store the Primary Key of this Table as Foreign Key in the Others.

Then in your Query if you join the Tables by the Foreign Keys you have a perfect match.

From there your calculations may become less complicate.

i purposely separated these fields as whilst yes there may be repetition, the user has the right to ender any model make or type, but the other table (car valuation) directly exists for the singular purpose of matching the entered make model and type with the make model and type in the car valuation table (if it exists) and if so providing the user with an estimate value for there vehicle (the calculation) at the time of entering their new car ad (i.e. filling out the fields for the car details table)... if the entered details (by the user) does not match any pre-defined records (combination of make,model,type) then the valuation estimate will not be provided...

 

[dragonslayer]

Additionally, if i were to create a table with just that:
Make
Model
Type
YearOfProduction,

then the calculation will be limited as i will need to create a record for every Make, mode and type - for every possible year of production i.e. a bmw in 2006 and 2010.. the way i hope to achieve it is each combination of model, type and make is only saved once and it is updated to the most recent RRP.. so the user regardless of their year of production will be able to receive an estimate - by the aforementioned calculation (the query for some reason dosnt work ..)

Thanks again !

 

[RainLover]

Some people have the ability to read and understand complicated SQL statements like yours.

I am not one of them. However if you can put that into a working version of your Database I would be able to have a look then.

Don't need the full database. Just enough to get the thing working with a few records.

Also please post in Version 2003.

 

[dragonslayer]

Some people have the ability to read and understand complicated SQL statements like yours.

I am not one of them. However if you can put that into a working version of your Database I would be able to have a look then.

Don't need the full database. Just enough to get the thing working with a few records.

Also please post in Version 2003.

I was able to simplify the SQL into an expression that unexpectedly ran without any sort of error.. however the result was empty regardless if i changed the false value ..

IIf([AdCarDetails]![Type]=[CarValuation]![Type] And [AdCarDetails]![Make]=[CarValuation]![Make] And [AdCarDetails]![Model]=[CarValuation]![Model],IIf([CarValuation]![Type]="sedan" Or "hatchback" Or "wagon",[CarValuation]![RRP]-((((DatePart("yyyy",Date())-1)-[AdCarDetails]![YearOfProduction])*[CarValuation]![RRP]*0.06)+((DatePart("yyyy",Date())-(DatePart("yyyy",Date())-1))*[CarValuation]![RRP]*0.24)),""))

 

[RainLover]

Glad you got it sorted.