H
hermie
In my query I try to make an expression for special days, the query runs
good but the output is wrong. It shows double (wrong) information for the
semestre like:
Nombre semestre daysprisem absents specdays
total
Anastacia 1 102 1
101
Anastacia 1 102 6
96
Anastacia 2 101 1 1
99
Anastacia 2 101 1 6
94
What I want to see is:
Nombre semestre daysprisem absents specdays
total
Anastacia 1 102 6
96
Anastacia 2 101 1 1
99
the expression =
specdays: IIf([dia especiales Query]![Diaespec By Year]="2004",[dia
especiales Query]![Sum Of IDdia],IIf([dia especiales Query]![Diaespec By
Year]="2005",[dia especiales Query]![Sum Of IDdia]))
and the SQL code looks like:
SELECT DISTINCT [students activo].Grupo, [students activo].ss, [students
activo].Nombre, [students activo].ApellidoP, [students activo].ApellidoM,
IIf([promedio y nota]!semestre=1 Or [promedio y nota]!semestre=2,[pro1]) AS
ave1, IIf([promedio y nota]!semestre=1 Or [promedio y
nota]!semestre=2,[notaps]) AS nota1, [promedio y nota].semestre,
IIf([promedio y nota]!semestre=1,(Work_Days([dia presentes]!begdate,[dia
presentes]!enddate)),(Work_Days2([dia presentes]!begdates,[dia
presentes]!enddates))) AS daysprisem, IIf([promedio y
nota]!semestre=1,[Ausente de estudiantes_Crosstab by
ss]![2004],IIf([promedio y nota]!semestre=2,[Ausente de estudiantes_Crosstab
by ss]![2005],0)) AS absents, IIf([dia especiales Query]![Diaespec By
Year]="2004",[dia especiales Query]![Sum Of IDdia],IIf([dia especiales
Query]![Diaespec By Year]="2005",[dia especiales Query]![Sum Of IDdia])) AS
specdays,
IIf(IsNull([absents]),[daysprisem]-[specdays],[daysprisem]-([absents]+[specd
ays])) AS pres1
FROM [dia especiales Query] INNER JOIN ([Ausente de estudiantes_Crosstab by
ss] INNER JOIN ([dia presentes] INNER JOIN (Querypresentes INNER JOIN
([promedio y nota] INNER JOIN ([students activo] INNER JOIN promedio ON
[students activo].ss = promedio.ss) ON [promedio y nota].ss = [students
activo].ss) ON Querypresentes.ss = [students activo].ss) ON [dia
presentes].ss = [students activo].ss) ON [Ausente de estudiantes_Crosstab by
ss].ss = [students activo].ss) ON [dia especiales Query].IDdia = [students
activo].IDdia
ORDER BY [students activo].Grupo;
I Think that the expression of specdays is wrong ?
Hope someone have a solution for me
Hermie
good but the output is wrong. It shows double (wrong) information for the
semestre like:
Nombre semestre daysprisem absents specdays
total
Anastacia 1 102 1
101
Anastacia 1 102 6
96
Anastacia 2 101 1 1
99
Anastacia 2 101 1 6
94
What I want to see is:
Nombre semestre daysprisem absents specdays
total
Anastacia 1 102 6
96
Anastacia 2 101 1 1
99
the expression =
specdays: IIf([dia especiales Query]![Diaespec By Year]="2004",[dia
especiales Query]![Sum Of IDdia],IIf([dia especiales Query]![Diaespec By
Year]="2005",[dia especiales Query]![Sum Of IDdia]))
and the SQL code looks like:
SELECT DISTINCT [students activo].Grupo, [students activo].ss, [students
activo].Nombre, [students activo].ApellidoP, [students activo].ApellidoM,
IIf([promedio y nota]!semestre=1 Or [promedio y nota]!semestre=2,[pro1]) AS
ave1, IIf([promedio y nota]!semestre=1 Or [promedio y
nota]!semestre=2,[notaps]) AS nota1, [promedio y nota].semestre,
IIf([promedio y nota]!semestre=1,(Work_Days([dia presentes]!begdate,[dia
presentes]!enddate)),(Work_Days2([dia presentes]!begdates,[dia
presentes]!enddates))) AS daysprisem, IIf([promedio y
nota]!semestre=1,[Ausente de estudiantes_Crosstab by
ss]![2004],IIf([promedio y nota]!semestre=2,[Ausente de estudiantes_Crosstab
by ss]![2005],0)) AS absents, IIf([dia especiales Query]![Diaespec By
Year]="2004",[dia especiales Query]![Sum Of IDdia],IIf([dia especiales
Query]![Diaespec By Year]="2005",[dia especiales Query]![Sum Of IDdia])) AS
specdays,
IIf(IsNull([absents]),[daysprisem]-[specdays],[daysprisem]-([absents]+[specd
ays])) AS pres1
FROM [dia especiales Query] INNER JOIN ([Ausente de estudiantes_Crosstab by
ss] INNER JOIN ([dia presentes] INNER JOIN (Querypresentes INNER JOIN
([promedio y nota] INNER JOIN ([students activo] INNER JOIN promedio ON
[students activo].ss = promedio.ss) ON [promedio y nota].ss = [students
activo].ss) ON Querypresentes.ss = [students activo].ss) ON [dia
presentes].ss = [students activo].ss) ON [Ausente de estudiantes_Crosstab by
ss].ss = [students activo].ss) ON [dia especiales Query].IDdia = [students
activo].IDdia
ORDER BY [students activo].Grupo;
I Think that the expression of specdays is wrong ?
Hope someone have a solution for me
Hermie