Hi everyone,
I have an issue creating a Cross Tab query and hope somebody can help.
I´ve uploaded the crosstab result and hidden personal data for privacy reasons.
Rows: Mail, Name, Last Name, Phone
Column: Product: ("1+1","2+1","2+2"...). These numbers are product names
Value: if any of these persons (rows) want a 1+1, 2+1 ... etc
What i´m trying to do:
Instead of showing the count in the value field, i want to show the name of the column header (product), in this case in the first column to show "1+1" instead of 1 or 2 or any number.
In the attachment there is a png file that is explained clearer.
In the next product( "2+1"), show "2+1" instead of 1...and so on
In Excel is easy to do because you put an IF(ISBLANK(G2);"";G$1) and so on.
But in Access i can´t do it and i have tried to have a calculated field for the value, but cant referrence to the "product".
Disclaimer: in the SQL code, "product" is tbl_tipologias.tipologia
Here is the code
Sorry for my English and thanks in advance,
Great forum!
Peivand Perez
I have an issue creating a Cross Tab query and hope somebody can help.
I´ve uploaded the crosstab result and hidden personal data for privacy reasons.
Rows: Mail, Name, Last Name, Phone
Column: Product: ("1+1","2+1","2+2"...). These numbers are product names
Value: if any of these persons (rows) want a 1+1, 2+1 ... etc
What i´m trying to do:
Instead of showing the count in the value field, i want to show the name of the column header (product), in this case in the first column to show "1+1" instead of 1 or 2 or any number.
In the attachment there is a png file that is explained clearer.
In the next product( "2+1"), show "2+1" instead of 1...and so on
In Excel is easy to do because you put an IF(ISBLANK(G2);"";G$1) and so on.
But in Access i can´t do it and i have tried to have a calculated field for the value, but cant referrence to the "product".
Disclaimer: in the SQL code, "product" is tbl_tipologias.tipologia
Here is the code
Code:
TRANSFORM Count(tbl_busca_inversiones.inversion_vigente) AS CountOfinversion_vigente
SELECT tbl_personas.correo_persona, tbl_personas.nombre_persona, tbl_personas.apellido_persona, tbl_personas.telefono_persona
FROM tbl_personas INNER JOIN (tbl_busca_inversiones INNER JOIN tbl_tipologias ON tbl_busca_inversiones.tipologia = tbl_tipologias.Id) ON tbl_personas.Id_persona = tbl_busca_inversiones.persona
WHERE (((tbl_personas.correo_persona) Is Not Null) AND ((tbl_busca_inversiones.comuna)=1))
GROUP BY tbl_personas.correo_persona, tbl_personas.nombre_persona, tbl_personas.apellido_persona, tbl_personas.telefono_persona, tbl_busca_inversiones.comuna
PIVOT tbl_tipologias.tipologia;Sorry for my English and thanks in advance,
Great forum!
Peivand Perez
Attachments
-
Crosstab.jpg
Last edited by a moderator:
