1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SELECT tblBaseTerr.[No] AS NoTerr, tblBaseTerr.Desc AS DescTerr, Format([tblBaseTerr].[Index],"00") & " - " & [tblBaseTerr].[DescCourt] AS AffTerr, tblBaseTerr.EstGeographique AS EstGeographiqueTerr, tblBaseMois.[No] AS NoMois, tblBaseMois.AffMois_Initiale, reqBaseParam_NoMois.NoMois AS NoMoisCourant,
"[NoTerr]=" & [tblBaseTerr].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]=" & [tblBaseMois].[No] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS CritereMens,
"[NoTerr]=" & [tblBaseTerr].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [tblBaseMois].[No] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS CritereCumul,
DSum("IndicateurNonDummy","reqHP_TDBEntretien",[CritereMens])/1 AS NbEntretienMens,
IIf((Month([reqBaseParam_DateHeureDebutSAPEntretien].[DateHeure])<=[tblBaseMois].[No] And [tblBaseMois].[No]<=[reqBaseParam_NoMois].[NoMois]),
DSum("IndicateurNonDummy","reqHP_TDBEntretien",[CritereCumul]),
Null)/1
AS NbEntretienCumul,
DSum("TempsReel","reqSAP_Entretien",[CritereMens])/1 AS TempsReelMens,
IIf((Month([reqBaseParam_DateHeureDebutSAPEntretien].[DateHeure])<=[tblBaseMois].[No] And [tblBaseMois].[No]<=[reqBaseParam_NoMois].[NoMois]),
DSum("TempsReel","reqSAP_Entretien",[CritereCumul]),
Null)/1
AS TempsReelCumul,
IIf(Not IsNull([NbEntretienMens]),IIf([NbEntretienMens]<>0,[TempsReelMens]/[NbEntretienMens],Null),Null)/1 AS TempsReelMoyenMens, IIf(Not IsNull([NbEntretienCumul]),IIf([NbEntretienCumul]<>0,[TempsReelCumul]/[NbEntretienCumul],Null),Null)/1 AS TempsReelMoyenCumul
FROM tblBaseTerr, tblBaseMois, reqBaseParam_NoMois, reqBaseParam_DateHeureDebutSAPEntretien, reqBaseParam_Annee
WHERE (((tblBaseTerr.EstGeographique)=True) AND ((tblBaseTerr.EstActif)=True))
ORDER BY Format([tblBaseTerr].[Index],"00") & " - " & [tblBaseTerr].[DescCourt], tblBaseMois.[No]; |
Partager