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

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


"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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



"JK" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>

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


"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> "JK" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>
>>
>
>

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

"JK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>>
>> "JK" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>>
>>> "Jay" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>
>>>
>>
>>
>
>

I am not believing the specification as I am reading it, but as I read it:

Forecastvariance: sum(iif( RegPlate="2005 05" and Mileage=20, [Forecast-
InitialValue ],0))

Forecastvariance: sum(iif( RegPlate="2004 04" and Mileage=40, [Forecast-
InitialValue ],0))





"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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




"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "JK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>> "JK" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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


"JK" <(E-Mail Removed)> wrote in message
news:%23E%(E-Mail Removed)...
> 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
>
>
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "JK" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>> "Jay" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>>> "JK" <(E-Mail Removed)> wrote in message
>>>> news:%(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>>
>>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Jason,

It is still not clear what you mean by the words "details the variances", if
you want to see individual models (more then one entry for each model) then
it is a simple two calculated fields:

SELECT tblVehicles.Model, IIf([RegPlate]="2005 05" And
[Mileage]=20,[Forecast]-[InitialValue],0) AS ForecastA, IIf([RegPlate]="2004
04" And [Mileage]=40,[Forecast]-[InitialValue],0) AS ForecastB
FROM tblVehicles
WHERE (((tblVehicles.RegPlate)="2005 05") AND ((tblVehicles.Mileage)=20)) OR
(((tblVehicles.RegPlate)="2004 04") AND ((tblVehicles.Mileage)=40))
ORDER BY tblVehicles.Model;

However, if you want to see only one entry for each model, you have to
aggregate ForecastA and B in some way (sum, average?)

If this is the case you can do it by using a cross-tab query based on
another query:

1st Query (qryVariances)

SELECT tblVehicles.Model, [Forecast]-[InitialValue] AS ForecastVariance,
IIf([RegPlate]="2005 05" And
[Mileage]=20,"ForecastVarianceA","ForecastVarianceB") AS VarianceCode
FROM tblVehicles
WHERE (((tblVehicles.RegPlate)="2005 05") AND ((tblVehicles.Mileage)=20)) OR
(((tblVehicles.RegPlate)="2004 04") AND ((tblVehicles.Mileage)=40))
ORDER BY tblVehicles.Model;

2nd Query

TRANSFORM Sum(qryVariances.ForecastVariance) AS SumOfForecastVariance
SELECT qryVariances.Model
FROM qryVariances
GROUP BY qryVariances.Model
PIVOT qryVariances.VarianceCode;


If you want average, change "Sum" in the beginning to "Avg" (without the
quotation marks)

Did I score a hit? :-)

Regards/Jacob

"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> "JK" <(E-Mail Removed)> wrote in message
> news:%23E%(E-Mail Removed)...
>> 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
>>
>>
>>
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>> "JK" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>> "JK" <(E-Mail Removed)> wrote in message
>>>>> news:%(E-Mail Removed)...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>>>> news:(E-Mail Removed)...
>>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Jacob,

Absolute Hit! I can't thank you enough for your help with this. It has
been absolutely invaluable .

In fact you've more than solved it as your suggestion re the cross-tab query
will also cover something else I'll need to do. Sorry if my explanations
were a little garbled. You get used to using what is 'in-house' jargon
(like variance) without realising how it may be ambiguous to other people.

Thanks again for ALL your help. I'm sat looking at two wondeful lists of
+/- numbers which is what I've been after for the last few days.

All the best,

Jay
____________________

"JK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jason,
>
> It is still not clear what you mean by the words "details the variances",
> if you want to see individual models (more then one entry for each model)
> then it is a simple two calculated fields:
>
> SELECT tblVehicles.Model, IIf([RegPlate]="2005 05" And
> [Mileage]=20,[Forecast]-[InitialValue],0) AS ForecastA,
> IIf([RegPlate]="2004 04" And [Mileage]=40,[Forecast]-[InitialValue],0) AS
> ForecastB
> FROM tblVehicles
> WHERE (((tblVehicles.RegPlate)="2005 05") AND ((tblVehicles.Mileage)=20))
> OR (((tblVehicles.RegPlate)="2004 04") AND ((tblVehicles.Mileage)=40))
> ORDER BY tblVehicles.Model;
>
> However, if you want to see only one entry for each model, you have to
> aggregate ForecastA and B in some way (sum, average?)
>
> If this is the case you can do it by using a cross-tab query based on
> another query:
>
> 1st Query (qryVariances)
>
> SELECT tblVehicles.Model, [Forecast]-[InitialValue] AS ForecastVariance,
> IIf([RegPlate]="2005 05" And
> [Mileage]=20,"ForecastVarianceA","ForecastVarianceB") AS VarianceCode
> FROM tblVehicles
> WHERE (((tblVehicles.RegPlate)="2005 05") AND ((tblVehicles.Mileage)=20))
> OR (((tblVehicles.RegPlate)="2004 04") AND ((tblVehicles.Mileage)=40))
> ORDER BY tblVehicles.Model;
>
> 2nd Query
>
> TRANSFORM Sum(qryVariances.ForecastVariance) AS SumOfForecastVariance
> SELECT qryVariances.Model
> FROM qryVariances
> GROUP BY qryVariances.Model
> PIVOT qryVariances.VarianceCode;
>
>
> If you want average, change "Sum" in the beginning to "Avg" (without the
> quotation marks)
>
> Did I score a hit? :-)
>
> Regards/Jacob
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> "JK" <(E-Mail Removed)> wrote in message
>> news:%23E%(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>>
>>> "Jay" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>> "JK" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>>
>>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>> "JK" <(E-Mail Removed)> wrote in message
>>>>>> news:%(E-Mail Removed)...
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>>>>> news:(E-Mail Removed)...
>>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>