count ONLY one shift per workday

[Manos39]

[FONT=&quot]Please help me: my db permits duplicates shifts for employees that work same workday.. And had to be like that since it has started. Problem is that in a query to display monthly needed values I need to count only per once in a day when a person has worked on a Holyday (day) or Sunday [/FONT]
[FONT=&quot]maybe a little help on this expression because the whole sql might be too big?[/FONT]
[FONT=&quot]ΗΜΕΡΑΣ: Count(IIf([WORKDAY]=[HOLYDAY] And nz([SHIFTID])<>5 And nz([SHIFTID])<>0;IIf(Weekday([WORKDAY])=1 And nz([ΩΡΑΡΙΟID])<>5 And nz([SHIFTID ])<>0 And [WORKDAY]<>NZ([HOLYDAY]);1;0)))[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]this is the query..
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]SELECT Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy") AS [ΑΝΑ ΕΤΟΣ], Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m") AS [ΑΝΑ ΜΗΝΑ], [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ] AS ΟΝΟΜΑΤΕΠΩΝΥΜΟ, ypaliloitbl.ΕΠΩΝΥΜΟ, ypaliloitbl.ΟΝΟΜΑ, IIf(nz([ΩΡΑΡΙΟID])=3 And Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])<>1 And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>NZ([ΑΡΓΙΑ]),"ΝΥΧΤΑ",IIf([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]=[ΑΡΓΙΑ] And nz([ΩΡΑΡΙΟID])<>5 And nz([ΩΡΑΡΙΟID])<>0,"ΑΡΓΙΑ",IIf(Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=1 And nz([ΩΡΑΡΙΟID])<>5 And nz([ΩΡΑΡΙΟID])<>0 And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>NZ([ΑΡΓΙΑ]),"ΚΥΡΙΑΚΗ"))) AS ΝΑΚ, DatePart("d",[ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]) AS ΗΜΕΡΟΜΗΝΙΑ, IIf(nz([ΩΡΑΡΙΟID])=3 And Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])<>1 And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>NZ([ΑΡΓΙΑ]),"8",0) AS [ΠΡΟΣ ΣΥΜΠΛΗΡ], Count(IIf([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]=[ΑΡΓΙΑ] And nz([ΩΡΑΡΙΟID])<>5 And nz([ΩΡΑΡΙΟID])<>0,IIf(Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=1 And nz([ΩΡΑΡΙΟID])<>5 And nz([ΩΡΑΡΙΟID])<>0 And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>NZ([ΑΡΓΙΑ]),1,0))) AS ΗΜΕΡΑΣ, IIf([ΧΡΕΩΣΗID]=3,8,0) AS ΗΜΕΡΗΣΙΑ, IIf([ΧΡΕΩΣΗID]=4,8,0) AS [ΧΡ ΣΑΒΒΑΤΟΥ], ypiresiestbl.ΧΡΕΩΣΗID, ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ[/FONT]
[FONT=&quot]FROM ypaliloitbl INNER JOIN (harakthrismosypiresiontbl INNER JOIN (eidiyphresiontbl INNER JOIN (eidikeshreoseistbl INNER JOIN (ypiresiestbl LEFT JOIN argiestbl ON ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ = argiestbl.ΑΡΓΙΑ) ON eidikeshreoseistbl.ΕΙΔΙΚΗΧΡΕΩΣΗID = ypiresiestbl.ΧΡΕΩΣΗID) ON eidiyphresiontbl.ΕΙΔΟΣΥΠΗΡΕΣΙΑΣID = ypiresiestbl.ΕΙΔΟΣΥΠΗΡΕΣΙAΣID) ON harakthrismosypiresiontbl.ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID = eidiyphresiontbl.ΧΑΡΑΚΤΗΡΙΣΜΟΣΥΠΗΡΕΣΙΑΣID) ON ypaliloitbl.ΥΠΑΛΛΗΛΟΣID = ypiresiestbl.ΕΠΩΝΥΜΟID[/FONT]

[FONT=&quot]GROUP BY Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"), Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypaliloitbl.ΕΠΩΝΥΜΟ, ypaliloitbl.ΟΝΟΜΑ, DatePart("d",[ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), ypiresiestbl.ΧΡΕΩΣΗID, ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ, ypiresiestbl.ΩΡΑΡΙΟID, ypaliloitbl.ΥΠΑΛΛΗΛΟΣID, argiestbl.ΑΡΓΙΑ[/FONT]​

[FONT=&quot]HAVING (((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"))=[Forms]![ypobolesfrm]![ΕΤΟΣ1]) AND ((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"))=[Forms]![ypobolesfrm]![ΜΗΝΑΣ1]) AND ((ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>58 And (ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>60))[/FONT]
[FONT=&quot]ORDER BY Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"), Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypaliloitbl.ΕΠΩΝΥΜΟ, DatePart("d",[ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]);[/FONT]

 

[gemma-the-husky]

i think we will struggle with greek for the most part.

is this in a visual query design? If so, right click the top pane, and look at the query properties. One will be "unique values". Set this

this will tell access to ignore duplicates, which may give you the result you want.


kalispera

 

[Manos39]

Thank you for replying I looked over for query properties where is that ?

 

[Manos39]

Thank you so much solved great with your help!