What i need to ask is if you download my database, you shall see, a table named
proigoumenesorestbl in which the field ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ will contain the total of workhours employees have worked till time the database will start to be used. So lets say i fill in the worked hours. In the same table, there is a date field to help me in criteria later and of cource the name of empoyee, and his ID field (AM).
In the query used to calculate running sum, (Qrminas) , sql syntax is
SELECT Qrevdomada.[ΑΝΑ ΕΤΟΣ], Qrevdomada.[ΑΝΑ ΜΗΝΑ], Qrevdomada.ΕΠΩΝΥΜΟ, Sum(Qrevdomada.[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]) AS [ΣΥΝΟΛΟ ΜΗΝΟΣ], DSum("[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]","Qrevdomada","[ΑΝΑ ΜΗΝΑ] <= " & [ΑΝΑ ΜΗΝΑ] & " And [ΑΝΑ ΕΤΟΣ] <= " & [ΑΝΑ ΕΤΟΣ] & " And [ΥΠΑΛΛΗΛΟΣID] = " & [ΥΠΑΛΛΗΛΟΣID]) AS ΣΥΝΟΛΑ, Fix([ΣΥΝΟΛΑ]/([ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]+[ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]*DLookUp("[ΧΡΕΩΣΗ%]","[harakthrismosypiresiontbl]","[ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID]=1")/100)) AS [ΔΙΑΦΟΡΑ ΗΜΕΡΩΝ], ([ΣΥΝΟΛΑ])-([ΔΙΑΦΟΡΑ ΗΜΕΡΩΝ]*[ΚΑΝΟΝΙΚΗ ΧΡΕΩΣΗ]) AS [ΔΙΑΦΟΡΑ ΩΡΩΝ], ([ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]+[ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]*DLookUp("[ΧΡΕΩΣΗ%]","[harakthrismosypiresiontbl]","[ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID]=1")/100) AS [ΚΑΝΟΝΙΚΗ ΧΡΕΩΣΗ], proigoumenesorestbl.ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ
FROM Qrevdomada INNER JOIN proigoumenesorestbl ON Qrevdomada.ΥΠΑΛΛΗΛΟΣID = proigoumenesorestbl.ΠΡΟΗΓΟΥΜΕΝΕΣΩΡΕΣID
GROUP BY Qrevdomada.[ΑΝΑ ΕΤΟΣ], Qrevdomada.[ΑΝΑ ΜΗΝΑ], Qrevdomada.ΕΠΩΝΥΜΟ, ([ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]+[ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]*DLookUp("[ΧΡΕΩΣΗ%]","[harakthrismosypiresiontbl]","[ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID]=1")/100), proigoumenesorestbl.ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ, Qrevdomada.ΥΠΑΛΛΗΛΟΣID, Qrevdomada.ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ;
i ve included table (proigoumenesorestbl) so what i would like is if i run the query, specificaly in record no1 there is a ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ=(formertotalofhours) but not calculated correctly since its date is former to the year and month and what i want is value 150 (worhours) to be added only to value -34 and only for specific month 2010/02 as it is a value from 2010/01. So i was thinkink dsum again in field ΣΥΝΟΛΑ of Qrminas with some condition where the value is to be summed if the date is of previous -1 month only. Am i right? any suggestions?
proigoumenesorestbl in which the field ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ will contain the total of workhours employees have worked till time the database will start to be used. So lets say i fill in the worked hours. In the same table, there is a date field to help me in criteria later and of cource the name of empoyee, and his ID field (AM).
In the query used to calculate running sum, (Qrminas) , sql syntax is
SELECT Qrevdomada.[ΑΝΑ ΕΤΟΣ], Qrevdomada.[ΑΝΑ ΜΗΝΑ], Qrevdomada.ΕΠΩΝΥΜΟ, Sum(Qrevdomada.[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]) AS [ΣΥΝΟΛΟ ΜΗΝΟΣ], DSum("[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]","Qrevdomada","[ΑΝΑ ΜΗΝΑ] <= " & [ΑΝΑ ΜΗΝΑ] & " And [ΑΝΑ ΕΤΟΣ] <= " & [ΑΝΑ ΕΤΟΣ] & " And [ΥΠΑΛΛΗΛΟΣID] = " & [ΥΠΑΛΛΗΛΟΣID]) AS ΣΥΝΟΛΑ, Fix([ΣΥΝΟΛΑ]/([ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]+[ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]*DLookUp("[ΧΡΕΩΣΗ%]","[harakthrismosypiresiontbl]","[ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID]=1")/100)) AS [ΔΙΑΦΟΡΑ ΗΜΕΡΩΝ], ([ΣΥΝΟΛΑ])-([ΔΙΑΦΟΡΑ ΗΜΕΡΩΝ]*[ΚΑΝΟΝΙΚΗ ΧΡΕΩΣΗ]) AS [ΔΙΑΦΟΡΑ ΩΡΩΝ], ([ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]+[ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]*DLookUp("[ΧΡΕΩΣΗ%]","[harakthrismosypiresiontbl]","[ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID]=1")/100) AS [ΚΑΝΟΝΙΚΗ ΧΡΕΩΣΗ], proigoumenesorestbl.ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ
FROM Qrevdomada INNER JOIN proigoumenesorestbl ON Qrevdomada.ΥΠΑΛΛΗΛΟΣID = proigoumenesorestbl.ΠΡΟΗΓΟΥΜΕΝΕΣΩΡΕΣID
GROUP BY Qrevdomada.[ΑΝΑ ΕΤΟΣ], Qrevdomada.[ΑΝΑ ΜΗΝΑ], Qrevdomada.ΕΠΩΝΥΜΟ, ([ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]+[ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ]*DLookUp("[ΧΡΕΩΣΗ%]","[harakthrismosypiresiontbl]","[ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID]=1")/100), proigoumenesorestbl.ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ, Qrevdomada.ΥΠΑΛΛΗΛΟΣID, Qrevdomada.ΣΤΑΝΤΑΡΧΡΕΩΣΗΜΕΡΑΣ;
i ve included table (proigoumenesorestbl) so what i would like is if i run the query, specificaly in record no1 there is a ΠΡΟΗΓΟΥΜΕΝΟΣΥΝΟΛΟΩΡΩΝ=(formertotalofhours) but not calculated correctly since its date is former to the year and month and what i want is value 150 (worhours) to be added only to value -34 and only for specific month 2010/02 as it is a value from 2010/01. So i was thinkink dsum again in field ΣΥΝΟΛΑ of Qrminas with some condition where the value is to be summed if the date is of previous -1 month only. Am i right? any suggestions?
