SQL Function problem 2

[ItIsHardToProgram]

Hello, I currently have a SQL for my database that sorts out some data, innerjoins it and groups it in the same time.

strSQL = "SELECT T.IdProjet, P.TempsPrevue as Budget, Sum(E.SalaireHoraire*((T.Lundi)+T.Mardi+T.Mercredi+T.Jeudi+T.Vendredi+" & _
        "T.Samedi+T.Dimanche+T.LundiRD+T.MardiRD+T.MercrediRD+T.JeudiRD+T.VendrediRD+T.SamediRD+T.DimancheRD))" & _
        "AS [Honoraire utilisé], P.TempsPrevue-[Honoraire utilisé] AS [Honoraire Restant]"
               
        strSQL = strSQL + " FROM (tblTimeSheet AS T INNER JOIN tblProjet AS P ON T.IdProjet=P.IdProjet) INNER JOIN" & _
                          " tblEmployer AS E ON T.IdEmployer=E.IdEmployer" & _
                          " WHERE P.IdEmployerResponsable = " & SelectEmp & _
                          " GROUP BY T.IdProjet, P.TempsPrevue;"

This works perfectly fine, but I would like it to do the following: When a project is on tblTimeSheet, but is not on TblProjet, I want it to appear any way, with the same fields.

Fields I want: SELECT T.IdProjet and Sum(E.SalaireHoraire*((T.Lundi)+T.Mardi+T.Mercredi+T.Jeudi+T.Vendredi+"etc...)

Fields I want when available: P.TempsPrevue

Is this possible?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[PHV]

You want a LEFT JOIN tblProjet but your WHERE clause will defeat this purpose.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

appear anyway" means you want a LEFT OUTER JOIN

SELECT T.IdProjet
     , P.TempsPrevue as Budget
     , Sum(E.SalaireHoraire*
         ((T.Lundi)+T.Mardi+T.Mercredi+T.Jeudi+T.Vendredi+T.Samedi+T.Dimanche
          +T.LundiRD+T.MardiRD+T.MercrediRD+T.JeudiRD+T.VendrediRD+T.SamediRD+T.DimancheRD))
             AS [Honoraire utilisé]
     , P.TempsPrevue-[Honoraire utilisé] AS [Honoraire Restant]
  FROM (
       tblTimeSheet AS T 
INNER 
  JOIN tblEmployer AS E 
    ON E.IdEmployer = T.IdEmployer
       )
LEFT OUTER
  JOIN tblProjet AS P 
    ON (
       P.IdProjet = T.IdProjet
   AND P.IdEmployerResponsable = " & SelectEmp 
       )
GROUP 
    BY T.IdProjet
     , P.TempsPrevue

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ItIsHardToProgram]

Thanks alot, it is what I wanted, even though I realize this won't help. Thanks for the effort and time of answering me.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.