crosstab query problem

[sigava77 via AccessMonster.com]

I Have a crosstab query based in an union query.

I can assign and visualize the data of the crosstab query in a subform

The problem started when I modified the union query to read a parameter from
a report control.
After that i could run all the queries in the queries windows(fine), but i
cannot read the data from the subform. The subform doesn't display the data.
I've put a debug after the sql query (in the vba), but it works fine. The
problem is in display the information after the alteration

What can be the problem?

Thanks,

Carla

 

[karl dewey]

You have --
- crosstab query
- union query
- subform (no form mentioned)
- report control
- report

What is your data flow? How is a subform in data flow to report?

 

[sigava77 via AccessMonster.com]

Hi,

I've been out of office and without internet access.

I did say report by mistake. The subform is in a form. The controls that
contain the parameter are in the form. The data from the crosstab query has
to be displayed in the subform.

there is not report control ou report (sorry)

the data flow is union query>crosstab query>subform

Thanks,

Carla

You have --
- crosstab query
- union query
- subform (no form mentioned)
- report control
- report

What is your data flow? How is a subform in data flow to report?

I Have a crosstab query based in an union query.

[quoted text clipped - 12 lines]

Carla

 

[sigava77 via AccessMonster.com]

Just to add,

The union query read the parameters from the controls placed at the parent
form

thx,

C

Hi,

I've been out of office and without internet access.

I did say report by mistake. The subform is in a form. The controls that
contain the parameter are in the form. The data from the crosstab query has
to be displayed in the subform.

there is not report control ou report (sorry)

the data flow is union query>crosstab query>subform

Thanks,

Carla

You have --
- crosstab query

[quoted text clipped - 10 lines]

 

[KARL DEWEY]

Post the SQL of the union and crosstab queries.
--
KARL DEWEY
Build a little - Test a little

Just to add,

The union query read the parameters from the controls placed at the parent
form

thx,

C

Hi,

I've been out of office and without internet access.

I did say report by mistake. The subform is in a form. The controls that
contain the parameter are in the form. The data from the crosstab query has
to be displayed in the subform.

there is not report control ou report (sorry)

the data flow is union query>crosstab query>subform

Thanks,

Carla

You have --
- crosstab query

[quoted text clipped - 10 lines]

Carla

 

[sigava77 via AccessMonster.com]

Union Query

PARAMETERS [forms]![f_processopaciente]![nid] Text ( 255 );
SELECT t_diagnosticoseguimento.coddiagnostico, "X" AS Valor, t_seguimento.
dataseguimento, t_seguimento.nid, "3Diagnóstico" as Tipo
FROM t_seguimento INNER JOIN t_diagnosticoseguimento ON t_seguimento.
idseguimento = t_diagnosticoseguimento.idseguimento
where t_seguimento.nid = [forms]![f_processopaciente]![nid]

union

select codestado,valor,data,nid,"2Exame"
from t_observacaopaciente
where codestado is not null and valor is not null and codobservacao is not
null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codobservacao,codestado,data,nid,"2Exame"
from t_observacaopaciente
where codestado is not null and valor is null and codobservacao is not null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codobservacao,valor,data,nid,"2Exame"
from t_observacaopaciente
where codestado is null and codobservacao is not null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codexame,resultado,dataresultado, nid,"5Laboratório"
from t_resultadoslaboratorio
where codparametro is null and codexame is not null and resultado is not null
and t_resultadoslaboratorio.nid = [forms]![f_processopaciente]![nid]


Simple query of union query

SELECT q_aux_resumo_paciente.*
FROM q_aux_resumo_paciente
ORDER BY q_aux_resumo_paciente.Tipo;



crosstab query (that works fine)


TRANSFORM last(Nz(valor, coddiagnostico)) AS Expr1
SELECT q_aux_resumo_dados_paciente_visualizar.coddiagnostico AS Descrição,
q_aux_resumo_dados_paciente_visualizar.nid AS Nid
FROM q_aux_resumo_dados_paciente_visualizar
GROUP BY q_aux_resumo_dados_paciente_visualizar.Tipo,
q_aux_resumo_dados_paciente_visualizar.coddiagnostico,
q_aux_resumo_dados_paciente_visualizar.nid
ORDER BY Format([dataseguimento],'yyyy-mm-dd')
PIVOT Format([dataseguimento],'yyyy-mm-dd');


the subform is based at the following
code (when i debug the strSQL it works very well, but it still to not display
the data on the subform):

Private Sub CmdVisualizar_Click()


On Error GoTo erro
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "q_aux_resumo_dados_paciente_crosstab"
Set qd = db.QueryDefs(strXTabQueryName)

If Not IsNull(Me.nid) Then


strSQL = " TRANSFORM last(Nz(valor, coddiagnostico)) As Expr1"
strSQL = strSQL & " SELECT q_aux_resumo_dados_paciente_visualizar.
coddiagnostico as Descrição, q_aux_resumo_dados_paciente_visualizar.nid as
Nid"
strSQL = strSQL & " FROM q_aux_resumo_dados_paciente_visualizar"

strSQL = strSQL & " GROUP BY q_aux_resumo_dados_paciente_visualizar.
Tipo, q_aux_resumo_dados_paciente_visualizar.coddiagnostico,
q_aux_resumo_dados_paciente_visualizar.nid"
strSQL = strSQL & " ORDER BY Format([dataseguimento],'yyyy-mm-dd')"
strSQL = strSQL & " PIVOT Format([dataseguimento],'yyyy-mm-dd');"



qd.sql = strSQL

Set qd = Nothing

Me.f_seguimento_crosstab.SourceObject = "Query." &
strXTabQueryName
Me.f_seguimento_crosstab.Visible = True

End If

Exit Sub
erro:
msgbox v Err.Description

Exit Sub

End Sub

Post the SQL of the union and crosstab queries.

Just to add,

[quoted text clipped - 26 lines]

 

[KARL DEWEY]

<< the subform is based at the following code
I can not help you with this as I only know how to use queries to feed forms.
--
KARL DEWEY
Build a little - Test a little

Union Query

PARAMETERS [forms]![f_processopaciente]![nid] Text ( 255 );
SELECT t_diagnosticoseguimento.coddiagnostico, "X" AS Valor, t_seguimento.
dataseguimento, t_seguimento.nid, "3Diagnóstico" as Tipo
FROM t_seguimento INNER JOIN t_diagnosticoseguimento ON t_seguimento.
idseguimento = t_diagnosticoseguimento.idseguimento
where t_seguimento.nid = [forms]![f_processopaciente]![nid]

union

select codestado,valor,data,nid,"2Exame"
from t_observacaopaciente
where codestado is not null and valor is not null and codobservacao is not
null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codobservacao,codestado,data,nid,"2Exame"
from t_observacaopaciente
where codestado is not null and valor is null and codobservacao is not null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codobservacao,valor,data,nid,"2Exame"
from t_observacaopaciente
where codestado is null and codobservacao is not null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codexame,resultado,dataresultado, nid,"5Laboratório"
from t_resultadoslaboratorio
where codparametro is null and codexame is not null and resultado is not null
and t_resultadoslaboratorio.nid = [forms]![f_processopaciente]![nid]


Simple query of union query

SELECT q_aux_resumo_paciente.*
FROM q_aux_resumo_paciente
ORDER BY q_aux_resumo_paciente.Tipo;



crosstab query (that works fine)


TRANSFORM last(Nz(valor, coddiagnostico)) AS Expr1
SELECT q_aux_resumo_dados_paciente_visualizar.coddiagnostico AS Descrição,
q_aux_resumo_dados_paciente_visualizar.nid AS Nid
FROM q_aux_resumo_dados_paciente_visualizar
GROUP BY q_aux_resumo_dados_paciente_visualizar.Tipo,
q_aux_resumo_dados_paciente_visualizar.coddiagnostico,
q_aux_resumo_dados_paciente_visualizar.nid
ORDER BY Format([dataseguimento],'yyyy-mm-dd')
PIVOT Format([dataseguimento],'yyyy-mm-dd');


the subform is based at the following
code (when i debug the strSQL it works very well, but it still to not display
the data on the subform):

Private Sub CmdVisualizar_Click()


On Error GoTo erro
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "q_aux_resumo_dados_paciente_crosstab"
Set qd = db.QueryDefs(strXTabQueryName)

If Not IsNull(Me.nid) Then


strSQL = " TRANSFORM last(Nz(valor, coddiagnostico)) As Expr1"
strSQL = strSQL & " SELECT q_aux_resumo_dados_paciente_visualizar.
coddiagnostico as Descrição, q_aux_resumo_dados_paciente_visualizar.nid as
Nid"
strSQL = strSQL & " FROM q_aux_resumo_dados_paciente_visualizar"

strSQL = strSQL & " GROUP BY q_aux_resumo_dados_paciente_visualizar.
Tipo, q_aux_resumo_dados_paciente_visualizar.coddiagnostico,
q_aux_resumo_dados_paciente_visualizar.nid"
strSQL = strSQL & " ORDER BY Format([dataseguimento],'yyyy-mm-dd')"
strSQL = strSQL & " PIVOT Format([dataseguimento],'yyyy-mm-dd');"



qd.sql = strSQL

Set qd = Nothing

Me.f_seguimento_crosstab.SourceObject = "Query." &
strXTabQueryName
Me.f_seguimento_crosstab.Visible = True

End If

Exit Sub
erro:
msgbox v Err.Description

Exit Sub

End Sub

Post the SQL of the union and crosstab queries.

Just to add,

[quoted text clipped - 26 lines]

Carla

 

[sigava77 via AccessMonster.com]

Help!!!!!

Union Query

PARAMETERS [forms]![f_processopaciente]![nid] Text ( 255 );
SELECT t_diagnosticoseguimento.coddiagnostico, "X" AS Valor, t_seguimento.
dataseguimento, t_seguimento.nid, "3Diagnóstico" as Tipo
FROM t_seguimento INNER JOIN t_diagnosticoseguimento ON t_seguimento.
idseguimento = t_diagnosticoseguimento.idseguimento
where t_seguimento.nid = [forms]![f_processopaciente]![nid]

union

select codestado,valor,data,nid,"2Exame"
from t_observacaopaciente
where codestado is not null and valor is not null and codobservacao is not
null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codobservacao,codestado,data,nid,"2Exame"
from t_observacaopaciente
where codestado is not null and valor is null and codobservacao is not null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codobservacao,valor,data,nid,"2Exame"
from t_observacaopaciente
where codestado is null and codobservacao is not null
and t_observacaopaciente.nid = [forms]![f_processopaciente]![nid]

union

select codexame,resultado,dataresultado, nid,"5Laboratório"
from t_resultadoslaboratorio
where codparametro is null and codexame is not null and resultado is not null
and t_resultadoslaboratorio.nid = [forms]![f_processopaciente]![nid]

Simple query of union query

SELECT q_aux_resumo_paciente.*
FROM q_aux_resumo_paciente
ORDER BY q_aux_resumo_paciente.Tipo;

crosstab query (that works fine)

TRANSFORM last(Nz(valor, coddiagnostico)) AS Expr1
SELECT q_aux_resumo_dados_paciente_visualizar.coddiagnostico AS Descrição,
q_aux_resumo_dados_paciente_visualizar.nid AS Nid
FROM q_aux_resumo_dados_paciente_visualizar
GROUP BY q_aux_resumo_dados_paciente_visualizar.Tipo,
q_aux_resumo_dados_paciente_visualizar.coddiagnostico,
q_aux_resumo_dados_paciente_visualizar.nid
ORDER BY Format([dataseguimento],'yyyy-mm-dd')
PIVOT Format([dataseguimento],'yyyy-mm-dd');

the subform is based at the following
code (when i debug the strSQL it works very well, but it still to not display
the data on the subform):

Private Sub CmdVisualizar_Click()


On Error GoTo erro
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "q_aux_resumo_dados_paciente_crosstab"
Set qd = db.QueryDefs(strXTabQueryName)

If Not IsNull(Me.nid) Then


strSQL = " TRANSFORM last(Nz(valor, coddiagnostico)) As Expr1"
strSQL = strSQL & " SELECT q_aux_resumo_dados_paciente_visualizar.
coddiagnostico as Descrição, q_aux_resumo_dados_paciente_visualizar.nid as
Nid"
strSQL = strSQL & " FROM q_aux_resumo_dados_paciente_visualizar"

strSQL = strSQL & " GROUP BY q_aux_resumo_dados_paciente_visualizar.
Tipo, q_aux_resumo_dados_paciente_visualizar.coddiagnostico,
q_aux_resumo_dados_paciente_visualizar.nid"
strSQL = strSQL & " ORDER BY Format([dataseguimento],'yyyy-mm-dd')"
strSQL = strSQL & " PIVOT Format([dataseguimento],'yyyy-mm-dd');"

qd.sql = strSQL

Set qd = Nothing

Me.f_seguimento_crosstab.SourceObject = "Query." &
strXTabQueryName
Me.f_seguimento_crosstab.Visible = True

End If

Exit Sub
erro:
msgbox v Err.Description

Exit Sub

End Sub

Post the SQL of the union and crosstab queries.

Just to add,

[quoted text clipped - 26 lines]

Carla