Sorry JK, if I'm not being very clear. If I give you some background to

what the data is maybe that will help.

The table contains 1000s of cars (model field) each with two values

(InitialValue & Forecast fields). However there are two other fields -

RegPlate & Mileage and each car model has mutliple records, with various

values at different RegPlates and Mileages.

So what I am trying to achieve is the difference between the two value

fields at the various combinations of RegPlate and Mileage.

In my example I was using xxx and yyy to represent two different models

(which would have different Forecast & InitialValue values) & hence

different variances when the plate & Mileage fields are 2005 05 and 20.

But I want my query to detail the Variances (Forecast minus

InitialValue) for each model where Plate & Mileage are 2005 05 and 20

and also where Plate & Mileage are 2004 04 and 40.

I hope I've explained it adequately as I really do appreciate your time

and don't want to provide insufficient clarity.

Does this help clarify things? I welcome your advice.

Thanks,

Jason

field

Jay,

Thermust be somthing that I'm missing

If you limit ForecatA to "2005 05" and 20 and suppose that give you

model xxxx result (525), how do you get results for yyy in Forecast A

(455)???? yyy does not answer the crteria!

Something is missing

Iff([RegPlate]="2005 05" and [Mileage=20],[Forecact]-[InitialNumber],

{ what do you put here?})

Regards/JK

Hi JK,

I really appreciate your help with this. The sql you've just detailed

gives me one field with the actual variance values (numerical) and a

field containing just the letter B.

What I'm trying to achieve is two fields BOTH with numerical

differences in, one called ForecastVarianceA and the other called

ForecastVarianceB. The actual values are based on

[Forecast]-[InitialValue] in both cases, but:

ForecastVarianceA : Where RegPlate=2005 05 And Mileage=20

ForecastVarianceB : WHere RegPlate=2004 04 And Mileage=40

Once I get this I can just add the Model field in the QBE grid. I

need my final output to be:

Model ForecastVarianceA ForecastVarianceB <--------FieldNames

xxx 525 755

yyy 455 235

I just cannot figure out how to do it but am sure it can't be that

difficult. If you could help me get there I'd be extremely grateful

as I've certainly reached my competency level

Many thanks,

Jay

Sorry Jay, I may had misunderstood you

Is this what you are looking for?

SELECT [Forecast]-[InitialValue] AS ForecastVariance,

IIf([RegPlate]="2005 05" And [Mileage]=20,"A","B") AS VarianceCode

FROM tblVehicles

WHERE (((tblVehicles.RegPlate)="2005 05") AND

((tblVehicles.Mileage)=20)) OR (((tblVehicles.RegPlate)="2004 04")

AND ((tblVehicles.Mileage)=40));

(change "tblVehicles" to your table name)

Regards/JK

Hi JK, I'm not sure I understand your suggestion. How will that

give me the two calculated fields I need?

Both need to be Forecast minus InititalValue, but ForecastVarianceA

where RegPlate=2005 05 and Mileage=20, and ForecastVarianceB where

RegPlate=2004 04 and Mileage=40.

What do I need to actually type to get these two fields (and the

Model field)? If you could advise how to construct this in QBE

grid or what SQL to enter in SQL view, that would be great. My table

name is tblUsedValues.

I can do one of the calculated fields at a time but not together and

surely it can be done.

Many thanks,

Jay

Jay,

In ForecastB you recalculate ForecastA insteat of referring to it.

ForecatVarianceB=(Forecast minus InitialValue where RegPlate=2005

05 and Mileage=20) minus InitialValue where RegPlate=2004 04 and

Mileage=40

I hope that this is what you are lookng for

Regards/JK

Hi, I have a table with the following fields (and formats):

Model (text format)

Plate (text format)

Mileage(number)

InitialValue (number)

Forecast (number)

I want to a query which gives me the following fields:

Model

ForecastVariance (calculated field)

ForecastVarianceB (calculated field)

With the ForecastVariance field being: Forecast minus InitialValue

where

RegPlate=2005 05 and Mileage=20

And ForecastVarianceB field being: Forecast minus InitialValue

where

RegPlate=2004 04 and Mileage=40

Doing the query with just one of the calculated fields is

straightforward but I just can't figure out how to do both (as

they both refer to different criteria on the other same two

fields)

Any help would be greatly appreciated.

Many thanks,

Jason