1 2 3
| SELECT tblImmeubles.ImmId, tblPeriodes.PerId, tblPeriodes.PerImm, tblImmeubles.ImmNumero, tblImmeubles.ImmNatureTravaux, tblImmeubles.ImmAdresse, tblCommunes.CommuneNom, tblProprietaires.ProprietaireNom, tblProprietaires.ProprietaireLocalite, tblGerances.GeranceNom, tblGerances.GeranceLocalite, tblPeriodes.PerDateDebut, tblPeriodes.PerDateFin, [PerCFC0Logements]+[PerCFC1Logements]+[PerCFC2Logements]+[PerCFC4Logements]+[PerCFC5Logements]-[PerReductionLogements] AS PerCoutTotalLogements, tblLogements.LogEntree, tblLogements.LogEtage, tblLogements.LogNumGerance, IIf(Len([Loc1Nom] & " " & [Loc1Prenom])>20,Left([Loc1Nom] & " " & [Loc1Prenom],19) & ".",[Loc1Nom] & " " & [Loc1Prenom]) AS LocNomPrenom, tblLocataires.LocDebutPeriode, tblLocataires.LocFinPeriode, tblLocataires.LocDecisionCanton, IIf(([PerDateDebut]<=PaiementDebut()),IIf(([LocDebutPeriode]>PaiementDebut()),[LocDebutPeriode],PaiementDebut()),IIf(([LocDebutPeriode]<[PerDateDebut]),[PerDateDebut],[LocDebutPeriode])) AS DateDeterminanteDebut, IIf(([PerDateFin]>PaiementFin()),IIf(([LocFinPeriode] Is Null),PaiementFin(),[LocFinPeriode]),IIf(([PerDateFin]<[LocFinPeriode]),[PerDateFin],IIf(([LocFinPeriode] Is Null),[PerDateFin],[LocFinPeriode]))) AS DateDeterminanteFin, Int(([PerAideCanton]/[PerCoutTotalLogements])*[PerCoutTotalLogements]*[LogCoutPourcent]/12+1*0.5) AS LogAideCanton, Int(([PerAideCommune]/[PerCoutTotalLogements])*[PerCoutTotalLogements]*[LogCoutPourcent]/12+1*0.5) AS LogAideCommune, Int(([PerAideTiers]/[PerCoutTotalLogements])*[PerCoutTotalLogements]*[LogCoutPourcent]/12+1*0.5) AS LogAideTiers, IIf([LocDecisionCanton]>2,[LogAideCanton],0) AS LogAideCantonSupp, IIf([LocDecisionCanton]>2,[LogAideCommune],0) AS LogAideCommuneSupp, IIf([LocDecisionCanton]>2,[LogAideTiers],0) AS LogAideTiersSupp, DateDiff("m",[DateDeterminanteDebut],[DateDeterminanteFin]+1) AS DureeLocation, ([LogAideCanton]-[LogAideCantonSupp])*[DureeLocation] AS LogAideCantonPayee, ([LogAideCommune]-[LogAideCommuneSupp])*[DureeLocation] AS LogAideCommunePayee, ([LogAideTiers]-[LogAideTiersSupp])*[DureeLocation] AS LogAideTiersPayee
FROM tblProprietaires INNER JOIN ((((tblGerances INNER JOIN (tblCommunes INNER JOIN tblImmeubles ON tblCommunes.CommuneId = tblImmeubles.ImmNumeroCommune) ON tblGerances.GeranceId = tblImmeubles.ImmGerance) INNER JOIN tblPeriodes ON tblImmeubles.ImmId = tblPeriodes.PerImm) INNER JOIN tblLogements ON tblImmeubles.ImmId = tblLogements.LogImm) INNER JOIN (tblDecisions INNER JOIN tblLocataires ON tblDecisions.DecisionId = tblLocataires.LocDecisionCanton) ON tblLogements.LogId = tblLocataires.LocLog) ON tblProprietaires.ProprietaireId = tblImmeubles.ImmProprietaire
WHERE (((tblPeriodes.PerDateDebut)<PaiementFin()) AND ((tblPeriodes.PerDateFin)>PaiementDebut()) AND ((tblLocataires.LocDebutPeriode)<[PerDateFin] And (tblLocataires.LocDebutPeriode)<PaiementFin()) AND ((tblLocataires.LocFinPeriode) Is Null Or (tblLocataires.LocFinPeriode)>[PerDateDebut])); |
Partager