join all tasks

[phpatrick]

YEAR	MNR    ID TASK  MAAND	TOTUURSHIFT1
2006	08	4 WORT	august	49,13
2006	08	3 POMP	august	8,68
2006	08	7 ONDE	august	3,77
2006	08	6 GRAS	august	3,82
2006	08	8 DIVE	august	171,55
2006	08	2 COUR	august	118,25
2006	08	1 AARD	august	124,55
2006	08		august	0,00

I have this question : there are for the moment 8 task (in the future it could be less or more). During one month you worked on some tasks. But sometimes not. Above there is one task where I did not work on. I know this but with this query I don't know for which task. I should know becuase I will related this with other tables to get a total for a task in a month.

SELECT Format([DAG],"yyyy") AS [YEAR], Format([DAG],"mm") AS MNR, T_00_TEELT.ID_TEELT, T_00_TEELT.TEELT_TAKEN, Format([DAG],"mmmm") AS MAAND, Sum(DateDiff("n",[SHIFT1VAN],[SHIFT1TOT])/60) AS TOTUURSHIFT1
FROM T_00_TEELT RIGHT JOIN T_03_TIME ON T_00_TEELT.ID_TEELT = T_03_TIME.refTEELTSHIFT1
WHERE (((T_03_TIME.DAG) Between DateSerial(Year(Date())-1,2,28) And DateSerial(Year(Date()),4,1)))
GROUP BY Format([DAG],"yyyy"), Format([DAG],"mm"), T_00_TEELT.ID_TEELT, T_00_TEELT.TEELT_TAKEN, Format([DAG],"mmmm")

Is this a join question. With this join he should take ALL the records of the TASKS (TEELT) even when there is none hour prestated for it.

 

[PHV]

In the SELECT list and the GROUP BY clause replace T_00_TEELT.ID_TEELT with T_03_TIME.refTEELTSHIFT1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886