Transform???

  • Thread starter Thread starter Alejandra Parra
  • Start date Start date
what are you "transforming" from and to?


Is it possible to make a TRANSFORM query with alphanumeric data???
 
I'm doing this query
but str_valor is an alphanumeric value

TRANSFORM AVG(TBL1.STR_VALOR)
SELECT TBL1.STR_NUMEROLISTA AS NL, TBL1.STR_NOMBREABREVIADO AS NOMBRE
FROM
[SELECT TBL_ALUMNOS.STR_NOMBREABREVIADO,
TBL_ALUMNOS.STR_NUMEROLISTA, TBL_ALUMNOS.STR_CVEALUMNO,
TBL_CALIFICACIONES.STR_VALOR, TBL_CALIFICACIONES.STR_CVEDATO
FROM TBL_ALUMNOS
LEFT JOIN TBL_CALIFICACIONES ON TBL_ALUMNOS.STR_CVEALUMNO =
TBL_CALIFICACIONES.STR_CVEALUMNO
WHERE TBL_ALUMNOS.STR_CVEGRADO='1'
AND TBL_ALUMNOS.STR_CVEGRUPO='B']. AS TBL1
LEFT JOIN
[SELECT
TBL_CALIFICACIONES.STR_CVEALUMNO,TBL_CALIFICACIONES.STR_CVEDATO,
TBL_DATOSCAPTURA.STR_DESCRIPCION
FROM TBL_CALIFICACIONES RIGHT JOIN TBL_DATOSCAPTURA ON
TBL_CALIFICACIONES.STR_CVEDATO = TBL_DATOSCAPTURA.STR_CVEDATO
WHERE TBL_DATOSCAPTURA.STR_CVEDATO IN
(SELECT TBL_DATOSPERIODO.STR_CVEDATO FROM TBL_DATOSPERIODO,
TBL_DATOSMATERIA
WHERE TBL_DATOSPERIODO.STR_CVEDATO=TBL_DATOSMATERIA.STR_CVEDATO AND
TBL_DATOSMATERIA.STR_CVEMATERIA='EspI)]. AS TBL2
ON (TBL1.STR_CVEDATO) =(TBL2.STR_CVEDATO)
GROUP BY TBL1.STR_NOMBREABREVIADO, TBL1.STR_NUMEROLISTA
ORDER BY TBL1.STR_NUMEROLISTA
PIVOT TBL2.STR_DESCRIPCION;
 
Using the VAL function you MIGHT be able to get results as long as Str_Valor
doesn't contain non-number characters or is not null. TRY

TRANSFORM Avg(Val(NZ(Tbl1.Str_Valor,"0")))
SELECT ...

You can only calculate average on a number. If you have Str_Valor equal to
something like "AX0123", the val function will return zero and that is likely to
give you unexpected results for your average.

Alejandra said:
I'm doing this query
but str_valor is an alphanumeric value

TRANSFORM AVG(TBL1.STR_VALOR)
SELECT TBL1.STR_NUMEROLISTA AS NL, TBL1.STR_NOMBREABREVIADO AS NOMBRE
FROM
[SELECT TBL_ALUMNOS.STR_NOMBREABREVIADO,
TBL_ALUMNOS.STR_NUMEROLISTA, TBL_ALUMNOS.STR_CVEALUMNO,
TBL_CALIFICACIONES.STR_VALOR, TBL_CALIFICACIONES.STR_CVEDATO
FROM TBL_ALUMNOS
LEFT JOIN TBL_CALIFICACIONES ON TBL_ALUMNOS.STR_CVEALUMNO =
TBL_CALIFICACIONES.STR_CVEALUMNO
WHERE TBL_ALUMNOS.STR_CVEGRADO='1'
AND TBL_ALUMNOS.STR_CVEGRUPO='B']. AS TBL1
LEFT JOIN
[SELECT
TBL_CALIFICACIONES.STR_CVEALUMNO,TBL_CALIFICACIONES.STR_CVEDATO,
TBL_DATOSCAPTURA.STR_DESCRIPCION
FROM TBL_CALIFICACIONES RIGHT JOIN TBL_DATOSCAPTURA ON
TBL_CALIFICACIONES.STR_CVEDATO = TBL_DATOSCAPTURA.STR_CVEDATO
WHERE TBL_DATOSCAPTURA.STR_CVEDATO IN
(SELECT TBL_DATOSPERIODO.STR_CVEDATO FROM TBL_DATOSPERIODO,
TBL_DATOSMATERIA
WHERE TBL_DATOSPERIODO.STR_CVEDATO=TBL_DATOSMATERIA.STR_CVEDATO AND
TBL_DATOSMATERIA.STR_CVEMATERIA='EspI)]. AS TBL2
ON (TBL1.STR_CVEDATO) =(TBL2.STR_CVEDATO)
GROUP BY TBL1.STR_NOMBREABREVIADO, TBL1.STR_NUMEROLISTA
ORDER BY TBL1.STR_NUMEROLISTA
PIVOT TBL2.STR_DESCRIPCION;

Rick B said:
what are you "transforming" from and to?


Is it possible to make a TRANSFORM query with alphanumeric data???
Click to expand...
Click to expand...
 
Back
Top