Access Query substraction 1

[ItIsHardToProgram]

Hello Everyone,

I am looking for some one to help me with my access query that is currently not working...

The database infrastructure goes like this:

[tt]
TblTimeSheet tblprojet tblemployer
Idprojet IdProjet IdEmployer
Idemployer TempsPrevue SalaireHoraire
Lundi
Mardi
mercredi
Vendredi
Samedi
Dimanche[/TT]


To make this understandable for you, I will translate. Note, in the SQL I use the tbls and fields mentioned above.

[TT]
TblTimeSheet TblProjet TblEmployer
IdProject IdProject IdEmployee
IdEmployee BudgetedTime HourlyRate
Monday
Tuesday
Wenesday
Thursday
Friday
Saturday
Sunday
[/TT]

The SQL goes like this (N.B.* Don't hesitate optimizing it):

SELECT tblTimeSheet.IdProjet, tblTimeSheet.IdEmployer, tblProjet.TempsPrevue, Sum(tblEmployer.SalaireHoraire*tblTimeSheet.Lundi+tblEmployer.
SalaireHoraire*tblTimeSheet.Mardi+tblEmployer.SalaireHoraire*tblTimeSheet.
Mercredi+tblEmployer.SalaireHoraire*tblTimeSheet.Jeudi+tblEmployer.
SalaireHoraire*tblTimeSheet.Vendredi+tblEmployer.SalaireHoraire*
tblTimeSheet.Samedi+tblEmployer.SalaireHoraire*tblTimeSheet.Dimanche+
tblEmployer.SalaireHoraire*tblTimeSheet.LundiRD+tblEmployer.SalaireHoraire
*tblTimeSheet.MardiRD+tblEmployer.SalaireHoraire*tblTimeSheet.MercrediRD+
tblEmployer.SalaireHoraire*tblTimeSheet.JeudiRD+tblEmployer.SalaireHoraire
*tblTimeSheet.VendrediRD+tblEmployer.SalaireHoraire*tblTimeSheet.SamediRD+
tblEmployer.SalaireHoraire*tblTimeSheet.DimancheRD) AS TempsTotal, tblProjet.TempsPrevue-TempsTotal AS TempsRestant
FROM tblProjet INNER JOIN (tblEmployer INNER JOIN tblTimeSheet ON 
tblEmployer.IdEmployer = tblTimeSheet.IdEmployer) ON (tblProjet.IdEmployer 
= tblEmployer.IdEmployer) AND (tblProjet.IdProjet = tblTimeSheet.IdProjet)
GROUP BY tblTimeSheet.IdProjet, tblTimeSheet.IdEmployer;

[Blue]When I execute it, It gives me an error, saying TempsPrevue is not part of the aggregate function (best translation I could do from french to english)[/Blue]

Thank you for helping me understand this problem, so I don't have to come here everytime it pops up.

Your help is much appreciated.
Cordialy,

Julien Roy


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

 

[ItIsHardToProgram]

N.B.* as I am working on everything i got it to work partialy, I still can't have the substraction, it dosnt let me aggregate it with a sum function.

The query looks like this right now:

SELECT tblTimeSheet.IdProjet, tblTimeSheet.IdEmployer, 
sum(tblProjet.TempsPrevue) AS Budget, 
Sum(tblEmployer.SalaireHoraire*tblTimeSheet.Lundi+tblEmployer.
SalaireHoraire*tblTimeSheet.Mardi+tblEmployer.SalaireHoraire*tblTimeSheet.
Mercredi+tblEmployer.SalaireHoraire*tblTimeSheet.Jeudi+tblEmployer.
SalaireHoraire*tblTimeSheet.Vendredi+tblEmployer.SalaireHoraire*
tblTimeSheet.Samedi+tblEmployer.SalaireHoraire*tblTimeSheet.Dimanche+
tblEmployer.SalaireHoraire*tblTimeSheet.LundiRD+tblEmployer.SalaireHoraire
*tblTimeSheet.MardiRD+tblEmployer.SalaireHoraire*tblTimeSheet.MercrediRD+
tblEmployer.SalaireHoraire*tblTimeSheet.JeudiRD+tblEmployer.SalaireHoraire
*tblTimeSheet.VendrediRD+tblEmployer.SalaireHoraire*tblTimeSheet.SamediRD
+tblEmployer.SalaireHoraire*tblTimeSheet.DimancheRD) AS TempsTotal
FROM tblProjet INNER JOIN (tblEmployer INNER JOIN tblTimeSheet ON 
tblEmployer.IdEmployer=tblTimeSheet.IdEmployer) ON 
tblProjet.IdProjet=tblTimeSheet.IdProjet
GROUP BY tblTimeSheet.IdProjet, tblTimeSheet.IdEmployer;

Sorry for the back and forth, you know how it is... I think?

Thank you for your help.

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

 

[ItIsHardToProgram]

Well, sorry for wasting your time everyone, I got it to work after all, this is the SQL code I used:

SELECT tblTimeSheet.IdProjet, tblTimeSheet.IdEmployer, 
MAX(tblProjet.TempsPrevue) AS Budget, 
Sum(tblEmployer.SalaireHoraire*tblTimeSheet.Lundi+tblEmployer.
SalaireHoraire*tblTimeSheet.Mardi+tblEmployer.SalaireHoraire*tblTimeSheet.
Mercredi+tblEmployer.SalaireHoraire*tblTimeSheet.Jeudi+tblEmployer.
SalaireHoraire*tblTimeSheet.Vendredi+tblEmployer.SalaireHoraire*
tblTimeSheet.Samedi+tblEmployer.SalaireHoraire*tblTimeSheet.Dimanche+
tblEmployer.SalaireHoraire*tblTimeSheet.LundiRD+tblEmployer.SalaireHoraire
*tblTimeSheet.MardiRD+tblEmployer.SalaireHoraire*tblTimeSheet.MercrediRD+
tblEmployer.SalaireHoraire*tblTimeSheet.JeudiRD+tblEmployer.SalaireHoraire
*tblTimeSheet.VendrediRD+tblEmployer.SalaireHoraire*tblTimeSheet.SamediRD+
tblEmployer.SalaireHoraire*tblTimeSheet.DimancheRD) 
AS TempsTotal, Budget-TempsTotal AS TempsRestant

FROM tblProjet INNER JOIN (tblEmployer INNER JOIN tblTimeSheet ON 
tblEmployer.IdEmployer=tblTimeSheet.IdEmployer) ON 
tblProjet.IdProjet=tblTimeSheet.IdProjet

GROUP BY tblTimeSheet.IdProjet, tblTimeSheet.IdEmployer;

I will definatly notice if you post to optimize it though, and it will be much appreciated.

Thank you,

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

 

[PHV]

What about this ?

SELECT T.IdProjet, T.IdEmployer, P.TempsPrevue, 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 TempsTotal, P.TempsPrevue - TempsTotal AS TempsRestant
FROM (tblTimeSheet T 
INNER JOIN tblProjet P ON T.IdProjet = P.IdProjet)
INNER JOIN tblEmployer E ON T.IdEmployer = E.IdEmployer
GROUP BY T.IdProjet, T.IdEmployer, P.TempsPrevue

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

 

[ItIsHardToProgram]

Great, clear, and simply astonishing, this is why you guys are the best!

I am currently having another problem though, I will create another post as it is the procedure, but I don't know if I should post it in queries, please advise me if I should post it in VBA or forms instead...

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