change name from "aliminium_X" to "Aluminium"

[anil]

Hi all
I have written the query (Not complete,just an idea):

SELECT LocationName,AYear,AMonth, Parameter,Units, Count(Result) as C,
Avg(Result) AS A FROM [qryResults] GROUP BY LocationName, AYear,AMonth,
Parameter, Units
HAVING ((([qryResults].Parameter) Like "Aluminium*"));

here 'Parameter' are like = Aluminium_X, Aluminium_Y, Aluminium_Z
(examples) and so on

I want all of them to be as 'Aluminium'.

Is that possible in query,I tried with iif statement in criteria as

IIF((([qryResults].Parameter) Like
"Aluminium*"),"Aluminium","Aluminium"))

but it did not give me proper results.

Can some one please help
Thanks
anil

 

[Smartin]

Hi all
I have written the query (Not complete,just an idea):

SELECT LocationName,AYear,AMonth, Parameter,Units, Count(Result) as C,
Avg(Result) AS A FROM [qryResults] GROUP BY LocationName, AYear,AMonth,
Parameter, Units
HAVING ((([qryResults].Parameter) Like "Aluminium*"));

here 'Parameter' are like = Aluminium_X, Aluminium_Y, Aluminium_Z
(examples) and so on

I want all of them to be as 'Aluminium'.

Is that possible in query,I tried with iif statement in criteria as

IIF((([qryResults].Parameter) Like
"Aluminium*"),"Aluminium","Aluminium"))

but it did not give me proper results.

Can some one please help
Thanks
anil

Ditch HAVING and IIF, use WHERE:

WHERE [qryResults].Parameter Like 'Aluminium*'

[OT] Are you sure you didn't mean 'Aluminum' <G>

 

[anil]

Hi smartin
You misunderstood me.

I am getting results as (Including all other fields) depending upon
methed used to test Aluminium :

Ara - Aluminium_X - mg/l -..........
Beu -Aluminium_Y - mg/l - .........
Bir - Aluminium_Z - mg/l - ..........

where as I want them as

Ara - Aluminium - mg/l -..........
Beu -Aluminium - mg/l - .........
Bir - Aluminium - mg/l - ..........

I just want to change its name format.I hope I make this time more
clear.
Thanks
anilI

 

[John Vinson]

Hi all
I have written the query (Not complete,just an idea):

SELECT LocationName,AYear,AMonth, Parameter,Units, Count(Result) as C,
Avg(Result) AS A FROM [qryResults] GROUP BY LocationName, AYear,AMonth,
Parameter, Units
HAVING ((([qryResults].Parameter) Like "Aluminium*"));

here 'Parameter' are like = Aluminium_X, Aluminium_Y, Aluminium_Z
(examples) and so on

I want all of them to be as 'Aluminium'.

Is that possible in query,I tried with iif statement in criteria as

IIF((([qryResults].Parameter) Like
"Aluminium*"),"Aluminium","Aluminium"))

but it did not give me proper results.

Can some one please help
Thanks
anil

I'm not sure I understand; I think you're using the word "parameter"
in a way quite different than normal in Access.

Do you have FIELDNAMES in your table Aluminium_X and Aluminium_Y?

What is qryResults? Perhaps a Crosstab query putting field values into
dynamic fieldnames...???

What ARE the "proper results"?

John W. Vinson[MVP]

 

[David F Cox]

LIKE "*Aluminium*"

will find "Aluminium" anywhere in the field.

Hi all
I have written the query (Not complete,just an idea):

SELECT LocationName,AYear,AMonth, Parameter,Units, Count(Result) as C,
Avg(Result) AS A FROM [qryResults] GROUP BY LocationName, AYear,AMonth,
Parameter, Units
HAVING ((([qryResults].Parameter) Like "Aluminium*"));

here 'Parameter' are like = Aluminium_X, Aluminium_Y, Aluminium_Z
(examples) and so on

I want all of them to be as 'Aluminium'.

Is that possible in query,I tried with iif statement in criteria as

IIF((([qryResults].Parameter) Like
"Aluminium*"),"Aluminium","Aluminium"))

but it did not give me proper results.

Can some one please help
Thanks
anil

I'm not sure I understand; I think you're using the word "parameter"
in a way quite different than normal in Access.

Do you have FIELDNAMES in your table Aluminium_X and Aluminium_Y?

What is qryResults? Perhaps a Crosstab query putting field values into
dynamic fieldnames...???

What ARE the "proper results"?

John W. Vinson[MVP]

 

[Douglas J. Steele]

SELECT LocationName,AYear,AMonth, "Aluminium" AS CorrectedParameter,
Units,Count(Result) as C, Avg(Result) AS A FROM [qryResults] GROUP BY
LocationName, AYear,AMonth, Parameter, Units
HAVING ((([qryResults].Parameter) Like "Aluminium*"));

 

[anil]

Thanks all of you
Actually there was test attached with name of parameter to be tested
like aluminium_acidsol or aluminium_icpms and so on.So I want to
correct it to Aluminium.
I did it with Left("parameter",9) and hence get only Aluminium with
removing all the remaining traces.
But thanks for your time and efforts
anil