subform based on crosstab query

[sigava77 via AccessMonster.com]

Hi,
Is it possible to design a form or subform based on crosstab query? How can i
do this?
Thanks,
Carla

 

[Duane Hookom]

Set the Column Headings property of the crosstab to all possible column
heading values.

Another method is to set the source object property of the subform control
to the crosstab query. There is a sample of how to do this at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
DyamicSubfrm.mdb

 

[sigava77 via AccessMonster.com]

Hi,
I don't know what i'm ding wrong. My crosstabquery is

PARAMETERS [forms]![f_paciente].[nid] Text ( 255 );
TRANSFORM (IIf(IsNull(valor),IIf(IsNull(estado),coddiagnostico,estado),valor))
AS Expr1 ' coddiagnostico doesn't appear, i think becouse of the iif function

SELECT q_aux_seguimento_pediatria_visualizar.coddiagnostico,
q_aux_seguimento_pediatria_visualizar.Estado,
q_aux_seguimento_pediatria_visualizar.Valor,
q_aux_seguimento_pediatria_visualizar.nid
FROM q_aux_seguimento_pediatria_visualizar
WHERE (((q_aux_seguimento_pediatria_visualizar.nid)=[forms]![f_paciente].[nid]
))
GROUP BY q_aux_seguimento_pediatria_visualizar.coddiagnostico,
q_aux_seguimento_pediatria_visualizar.Estado,
q_aux_seguimento_pediatria_visualizar.Valor,
q_aux_seguimento_pediatria_visualizar.nid
ORDER BY Format([dataseguimento],"yyyy-mm-dd")
PIVOT Format([dataseguimento],"yyyy-mm-dd");

when i run the form it doesn't display nothing,

 

[Duane Hookom]

Run what form that doesn't display anything?
I think you can replace:
TRANSFORM
(IIf(IsNull(valor),IIf(IsNull(estado),coddiagnostico,estado),valor))
With
TRANSFORM First(Nz(Nz(valor,Nz(estado,Coddiagnostico))))

--
Duane Hookom
MS Access MVP
--

Hi,
I don't know what i'm ding wrong. My crosstabquery is

PARAMETERS [forms]![f_paciente].[nid] Text ( 255 );
TRANSFORM
(IIf(IsNull(valor),IIf(IsNull(estado),coddiagnostico,estado),valor))
AS Expr1 ' coddiagnostico doesn't appear, i think becouse of the iif
function

SELECT q_aux_seguimento_pediatria_visualizar.coddiagnostico,
q_aux_seguimento_pediatria_visualizar.Estado,
q_aux_seguimento_pediatria_visualizar.Valor,
q_aux_seguimento_pediatria_visualizar.nid
FROM q_aux_seguimento_pediatria_visualizar
WHERE
(((q_aux_seguimento_pediatria_visualizar.nid)=[forms]![f_paciente].[nid]
))
GROUP BY q_aux_seguimento_pediatria_visualizar.coddiagnostico,
q_aux_seguimento_pediatria_visualizar.Estado,
q_aux_seguimento_pediatria_visualizar.Valor,
q_aux_seguimento_pediatria_visualizar.nid
ORDER BY Format([dataseguimento],"yyyy-mm-dd")
PIVOT Format([dataseguimento],"yyyy-mm-dd");

when i run the form it doesn't display nothing,

Set the Column Headings property of the crosstab to all possible column
heading values.

Another method is to set the source object property of the subform control
to the crosstab query. There is a sample of how to do this at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
DyamicSubfrm.mdb

 

[sigava77 via AccessMonster.com]

Thank you,
I'm talking about the subform. I set the source object of the subform control
to the crosstab query. When i load the form it doen't display no value.
Thanks,
Carla

Run what form that doesn't display anything?
I think you can replace:
TRANSFORM
(IIf(IsNull(valor),IIf(IsNull(estado),coddiagnostico,estado),valor))
With
TRANSFORM First(Nz(Nz(valor,Nz(estado,Coddiagnostico))))

Hi,
I don't know what i'm ding wrong. My crosstabquery is

[quoted text clipped - 35 lines]

 

[Duane Hookom]

Could you share the code you are using to assign the crosstab as the Source
Object?

--
Duane Hookom
MS Access MVP
--

Thank you,
I'm talking about the subform. I set the source object of the subform
control
to the crosstab query. When i load the form it doen't display no value.
Thanks,
Carla

Run what form that doesn't display anything?
I think you can replace:
TRANSFORM
(IIf(IsNull(valor),IIf(IsNull(estado),coddiagnostico,estado),valor))
With
TRANSFORM First(Nz(Nz(valor,Nz(estado,Coddiagnostico))))

Hi,
I don't know what i'm ding wrong. My crosstabquery is

[quoted text clipped - 35 lines]

Thanks,
Carla

 

[sigava77 via AccessMonster.com]

Me.f_seguimento_crosstab.SourceObject = "Query.
q_aux_seguimento_pediatria_crosstab"

Could you share the code you are using to assign the crosstab as the Source
Object?

Thank you,
I'm talking about the subform. I set the source object of the subform

[quoted text clipped - 15 lines]

 

[Duane Hookom]

Does q_aux_seguimento_pediatria_crosstab require a criteria value? Does it
open on its own?

--
Duane Hookom
MS Access MVP
--

Me.f_seguimento_crosstab.SourceObject = "Query.
q_aux_seguimento_pediatria_crosstab"

Could you share the code you are using to assign the crosstab as the
Source
Object?

Thank you,
I'm talking about the subform. I set the source object of the subform

[quoted text clipped - 15 lines]

Thanks,
Carla

 

[sigava77 via AccessMonster.com]

Yes it does. Its based on the query(with the criteria) posted above.
Thanks,
Carla

Does q_aux_seguimento_pediatria_crosstab require a criteria value? Does it
open on its own?

Me.f_seguimento_crosstab.SourceObject = "Query.
q_aux_seguimento_pediatria_crosstab"

[quoted text clipped - 8 lines]

 

[sigava77 via AccessMonster.com]

Sorry. What i mean is that it doen't open on it own.to be opened it require
this criteria "[forms]![f_paciente].[nid]"

Yes it does. Its based on the query(with the criteria) posted above.
Thanks,
Carla

Does q_aux_seguimento_pediatria_crosstab require a criteria value? Does it
open on its own?

[quoted text clipped - 4 lines]

 

[Duane Hookom]

Is the form open and is there a value in the control?

--
Duane Hookom
MS Access MVP
--

Sorry. What i mean is that it doen't open on it own.to be opened it
require
this criteria "[forms]![f_paciente].[nid]"

Yes it does. Its based on the query(with the criteria) posted above.
Thanks,
Carla

Does q_aux_seguimento_pediatria_crosstab require a criteria value? Does
it
open on its own?

[quoted text clipped - 4 lines]

Thanks,
Carla

 

[sigava77 via AccessMonster.com]

Yes, it is. And it doesn't give any error.
Carla

Is the form open and is there a value in the control?

Sorry. What i mean is that it doen't open on it own.to be opened it
require

[quoted text clipped - 10 lines]

 

[Duane Hookom]

Have you identified how your version differs from my sample? I believe my
sample modifies the SQL property of a saved query. There is code like:
Private Sub cboYear_AfterUpdate()


--
Duane Hookom
MS Access MVP
--

Yes, it is. And it doesn't give any error.
Carla

Is the form open and is there a value in the control?

Sorry. What i mean is that it doen't open on it own.to be opened it
require

[quoted text clipped - 10 lines]

Thanks,
Carla

 

[sigava77 via AccessMonster.com]

strSQL = strSQL & " TRANSFORM(Nz(valor, coddiagnostico)) As Expr1"
strSQL = strSQL & " SELECT q_aux_seguimento_pediatria_visualizar.
coddiagnostico, q_aux_seguimento_pediatria_visualizar.Valor,
q_aux_seguimento_pediatria_visualizar.nid,
q_aux_seguimento_pediatria_visualizar.Tipo"
strSQL = strSQL & " FROM q_aux_seguimento_pediatria_visualizar"
strSQL = strSQL & " WHERE q_aux_seguimento_pediatria_visualizar.nid ='" &
[Forms]![f_seguimento_ficha_pediatria].[nid] & "'"
strSQL = strSQL & " GROUP BY q_aux_seguimento_pediatria_visualizar.Tipo,
q_aux_seguimento_pediatria_visualizar.coddiagnostico,
q_aux_seguimento_pediatria_visualizar.Valor,
q_aux_seguimento_pediatria_visualizar.nid"
strSQL = strSQL & " ORDER BY Format([dataseguimento],'yyyy-mm-dd')"
strSQL = strSQL & " PIVOT Format([dataseguimento],'yyyy-mm-dd');"

I've indentified the problem. At the first version i was declaring the
parameter on code while it's not necessary. I deleted the parameter and it's
working nice.
Thank you very Much Duane for your patience.
Carla

Have you identified how your version differs from my sample? I believe my
sample modifies the SQL property of a saved query. There is code like:
Private Sub cboYear_AfterUpdate()

Yes, it is. And it doesn't give any error.
Carla

[quoted text clipped - 6 lines]