Din SQL sætning bag om forespørgslen skal indeholde dette :
SELECT Tabel.dbo_TIME_CONTROL_END, Sum(IIf([Tabel]![WORK_ACT_TEXT]=40,[Tabel]![WORK_USED_TIME]*-1,[Tabel]![WORK_USED_TIME])) AS WORK_USED_TIME FROM Tabel GROUP BY Tabel.dbo_TIME_CONTROL_END;
Jeg har ikke helt fået det løst, men kan se hvordan, skal bare lige have den smeltet sammen med min forespørgsel.
Nemlig denne:
SELECT dbo_PERSON_INFO.PERSON_NUMBER, dbo_PERSON_INFO.NAME, dbo_TIME_CONTROL_END.PERSON_GROUP, dbo_TIME_CONTROL_END.WORK_TRANS_DATE, dbo_TIME_CONTROL_END.WORK_ACT_CODE, dbo_TIME_CONTROL_END.WORK_ACT_TEXT, dbo_TIME_CONTROL_END.WORK_USED_TIME, dbo_TIME_CONTROL_END.DAYTIME_START_1, dbo_TIME_CONTROL_END.DAYTIME_END_1, dbo_TIME_CONTROL_END.WORK_START_TIME, dbo_TIME_CONTROL_END.WORK_END_TIME FROM dbo_PERSON_INFO INNER JOIN dbo_TIME_CONTROL_END ON dbo_PERSON_INFO.PERSON_NUMBER = dbo_TIME_CONTROL_END.PERSON_NUMBER GROUP BY dbo_PERSON_INFO.PERSON_NUMBER, dbo_PERSON_INFO.NAME, dbo_TIME_CONTROL_END.PERSON_GROUP, dbo_TIME_CONTROL_END.WORK_TRANS_DATE, dbo_TIME_CONTROL_END.WORK_ACT_CODE, dbo_TIME_CONTROL_END.WORK_ACT_TEXT, dbo_TIME_CONTROL_END.WORK_USED_TIME, dbo_TIME_CONTROL_END.DAYTIME_START_1, dbo_TIME_CONTROL_END.DAYTIME_END_1, dbo_TIME_CONTROL_END.WORK_START_TIME, dbo_TIME_CONTROL_END.WORK_END_TIME HAVING (((dbo_TIME_CONTROL_END.PERSON_GROUP) Like [Indtast Mekaniker gruppe]) AND ((dbo_TIME_CONTROL_END.WORK_TRANS_DATE) Between [fra dato format dd-mm-åååå] And [til dato format dd-mm-åååå]) AND ((dbo_TIME_CONTROL_END.WORK_ACT_CODE)="00" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="02" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="40" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="17" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="18" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="20" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="21" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="22" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="23" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="24" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="25" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="26" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="28" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="29")) ORDER BY dbo_PERSON_INFO.PERSON_NUMBER;
Hvis du i stedet vil lave din opsummering i rapporten, men bare have vendt fortegnet på dit WORK_USED_TIME felt når WORK_ACT_CODE feltet er "40" i forespørgslen, kan du sikkert nøjes med dette:
SELECT dbo_PERSON_INFO.PERSON_NUMBER, dbo_PERSON_INFO.NAME, dbo_TIME_CONTROL_END.PERSON_GROUP, dbo_TIME_CONTROL_END.WORK_TRANS_DATE, dbo_TIME_CONTROL_END.WORK_ACT_CODE, dbo_TIME_CONTROL_END.WORK_ACT_TEXT, IIf(dbo_TIME_CONTROL_END.WORK_ACT_CODE="40", - dbo_TIME_CONTROL_END.WORK_USED_TIME, dbo_TIME_CONTROL_END.WORK_USED_TIME) AS WORK_USED_TIME, dbo_TIME_CONTROL_END.DAYTIME_START_1, dbo_TIME_CONTROL_END.DAYTIME_END_1, dbo_TIME_CONTROL_END.WORK_START_TIME, dbo_TIME_CONTROL_END.WORK_END_TIME FROM dbo_PERSON_INFO INNER JOIN dbo_TIME_CONTROL_END ON dbo_PERSON_INFO.PERSON_NUMBER = dbo_TIME_CONTROL_END.PERSON_NUMBER WHERE (((tec.PERSON_GROUP) Like [Indtast Mekaniker gruppe]) AND ((tec.WORK_TRANS_DATE) Between [fra dato format dd-mm-åååå] And [til dato format dd-mm-åååå]) AND ((tec.WORK_ACT_CODE) IN ("00", "02", "40", "17", "18", "20", "21", "22", "23", "24", "25", "26", "28", "29")) ORDER BY dbo_PERSON_INFO.PERSON_NUMBER;
Hov, sorry, jeg havde leget lidt med en lidt kortere alias for tabelnavnet.
SELECT dbo_PERSON_INFO.PERSON_NUMBER, dbo_PERSON_INFO.NAME, dbo_TIME_CONTROL_END.PERSON_GROUP, dbo_TIME_CONTROL_END.WORK_TRANS_DATE, dbo_TIME_CONTROL_END.WORK_ACT_CODE, dbo_TIME_CONTROL_END.WORK_ACT_TEXT, IIf(dbo_TIME_CONTROL_END.WORK_ACT_CODE="40", - dbo_TIME_CONTROL_END.WORK_USED_TIME, dbo_TIME_CONTROL_END.WORK_USED_TIME) AS WORK_USED_TIME, dbo_TIME_CONTROL_END.DAYTIME_START_1, dbo_TIME_CONTROL_END.DAYTIME_END_1, dbo_TIME_CONTROL_END.WORK_START_TIME, dbo_TIME_CONTROL_END.WORK_END_TIME FROM dbo_PERSON_INFO INNER JOIN dbo_TIME_CONTROL_END ON dbo_PERSON_INFO.PERSON_NUMBER = dbo_TIME_CONTROL_END.PERSON_NUMBER WHERE (((dbo_TIME_CONTROL_END.PERSON_GROUP) Like [Indtast Mekaniker gruppe]) AND ((dbo_TIME_CONTROL_END.WORK_TRANS_DATE) Between [fra dato format dd-mm-åååå] And [til dato format dd-mm-åååå]) AND ((dbo_TIME_CONTROL_END.WORK_ACT_CODE) IN ("00", "02", "40", "17", "18", "20", "21", "22", "23", "24", "25", "26", "28", "29")) ORDER BY dbo_PERSON_INFO.PERSON_NUMBER;
(Du er en skat) havde jeg sagt hvis du var en pige... Det er perfekt, dog var der en syntax fejl (som jeg ikke kunne finde) så lavede den på denne måde, selvom jeg kan se din er meget smartere:
SELECT dbo_PERSON_INFO.PERSON_NUMBER, dbo_PERSON_INFO.NAME, dbo_TIME_CONTROL_END.PERSON_GROUP, dbo_TIME_CONTROL_END.WORK_TRANS_DATE, dbo_TIME_CONTROL_END.WORK_ACT_CODE, dbo_TIME_CONTROL_END.WORK_ACT_TEXT, IIf(dbo_TIME_CONTROL_END.WORK_ACT_CODE="40",-dbo_TIME_CONTROL_END.WORK_USED_TIME,dbo_TIME_CONTROL_END.WORK_USED_TIME) AS WORK_USED_TIME, dbo_TIME_CONTROL_END.DAYTIME_START_1, dbo_TIME_CONTROL_END.DAYTIME_END_1, dbo_TIME_CONTROL_END.WORK_START_TIME, dbo_TIME_CONTROL_END.WORK_END_TIME FROM dbo_PERSON_INFO INNER JOIN dbo_TIME_CONTROL_END ON dbo_PERSON_INFO.PERSON_NUMBER=dbo_TIME_CONTROL_END.PERSON_NUMBER GROUP BY dbo_PERSON_INFO.PERSON_NUMBER, dbo_PERSON_INFO.NAME, dbo_TIME_CONTROL_END.PERSON_GROUP, dbo_TIME_CONTROL_END.WORK_TRANS_DATE, dbo_TIME_CONTROL_END.WORK_ACT_CODE, dbo_TIME_CONTROL_END.WORK_ACT_TEXT, dbo_TIME_CONTROL_END.WORK_USED_TIME, dbo_TIME_CONTROL_END.DAYTIME_START_1, dbo_TIME_CONTROL_END.DAYTIME_END_1, dbo_TIME_CONTROL_END.WORK_START_TIME, dbo_TIME_CONTROL_END.WORK_END_TIME HAVING (((dbo_TIME_CONTROL_END.PERSON_GROUP) Like [Indtast Mekaniker gruppe]) AND ((dbo_TIME_CONTROL_END.WORK_TRANS_DATE) Between [fra dato format dd-mm-åååå] And [til dato format dd-mm-åååå]) AND ((dbo_TIME_CONTROL_END.WORK_ACT_CODE)="00" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="02" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="40" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="17" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="18" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="20" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="21" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="22" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="23" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="24" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="25" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="26" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="28" Or (dbo_TIME_CONTROL_END.WORK_ACT_CODE)="29")) ORDER BY dbo_PERSON_INFO.PERSON_NUMBER;
kan jeg sende dig point eller skal jeg lige oprette en tråd først, du kan svare på?
Nej, jeg skal ikke have noget. Løsningen var jo kommet fra jensen363, jeg har bare sat den ind i din sætning (og åbenbart introduceret en fejl i processen). Så ingen point til mig...
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.