2 calculated fields using same fields, diff criteria?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

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
 
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
 
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 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 said:
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 said:
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
Click to expand...
Click to expand...
 
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,

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
My pleasure mate

Jay said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top