Help with query for a calendar

[Kristi_Christopherson]

I'm familiar with some queries but the previous programmer knew a lot more than me. The query is for a calendar we use in an Access database to schedule vacation for the employees in our department. The issue is when the report is run for a certain month, if the last week of the month ends in the next month, it doesn't show on the current month. For example, in August 2022 the last week ending date is 9/3/2022, but individuals who are off the entire week don't show on the August calendar and only show when the September calendar is run. I feel like it's got to be something in the query that has to do with the month but the query is very extensive. I'll post the first part of the query below, after that it just repeats for each calendar day so it would be the same thing. I think it's something that starts after the Union so I've bolded that part. Any help would be very appreciated!



SELECT Left([FirstName],1) & [LastName] & IIf([tbAttendance_Schedule].[TypeID]=1," - S",IIf([tbAttendance_Schedule].[TypeID]=4," - DD",IIf([tbAttendance_Schedule].[TypeID]=8," - F",IIf([tbAttendance_Schedule].[TypeID]=9," - JD",IIf([tbAttendance_Schedule].[TypeID]=10," - FMLA",IIf([tbAttendance_Schedule].[TypeID]=11," - DIS",IIf([tbAttendance_Schedule].[TypeID]=13," - ML",IIf([tbAttendance_Schedule].[TypeID]=5," - " & [Hours] & " DH","N/A")))))))) AS EMAID, IIf([tbAttendance_Schedule.PlannedFlag]=True And [tbAttendance_Schedule.TypeID]>13,"Planned Time Off",IIf([tbAttendance_Schedule.PlannedFlag]=False And [tbAttendance_Schedule.TypeID]>13,"Unplanned Time","N/A")) AS RG, Day(tbAttendance_Dates.Date) AS [Day], GetWeekEndingActual([tbAttendance_Dates.Date]) AS [W/E], Year(tbAttendance_Dates.Date) AS [Year], Month(tbAttendance_Dates.Date) AS [Month], [MonthName] & " " & [Year] AS MY FROM (tbUser INNER JOIN tbUser_Skill ON tbUser.SkillID = tbUser_Skill.SkillID) INNER JOIN (((tbAttendance_Dates INNER JOIN tbDates ON tbAttendance_Dates.Date = tbDates.Date) INNER JOIN (tbAttendance_Schedule LEFT JOIN tbAttendance_Hours ON tbAttendance_Schedule.SchedID = tbAttendance_Hours.SchedID) ON tbAttendance_Dates.VacID = tbAttendance_Schedule.VacID) LEFT JOIN tbAttendance_Reason ON tbAttendance_Schedule.SchedID = tbAttendance_Reason.SchedID) ON tbUser.OLMSID = tbAttendance_Schedule.OLMSID WHERE (((tbAttendance_Schedule.TypeID) In (1,4,5,8,9,10,11,13)) AND ((tbAttendance_Schedule.DeletedFlag)=False) AND ((tbUser.ActiveFlag)=True)); Union SELECT Left([FirstName],1) & [LastName] & " - VW" AS EMAID, "" AS RG, Day(tbAttendance_Dates.Date-5) AS [Day], GetWeekEndingActual([tbAttendance_Dates.Date]) AS [W/E], Year(tbAttendance_Dates.Date) AS [Year], Month(tbAttendance_Dates.Date) AS [Month], [MonthName] & " " & [Year] AS MY FROM (tbUser INNER JOIN tbUser_Skill ON tbUser.SkillID = tbUser_Skill.SkillID) INNER JOIN (((tbAttendance_Dates INNER JOIN tbDates ON tbAttendance_Dates.Date = tbDates.Date) INNER JOIN (tbAttendance_Schedule LEFT JOIN tbAttendance_Hours ON tbAttendance_Schedule.SchedID = tbAttendance_Hours.SchedID) ON tbAttendance_Dates.VacID = tbAttendance_Schedule.VacID) LEFT JOIN tbAttendance_Reason ON tbAttendance_Schedule.SchedID = tbAttendance_Reason.SchedID) ON tbUser.OLMSID = tbAttendance_Schedule.OLMSID WHERE (((tbAttendance_Schedule.TypeID) In (2)) AND ((tbAttendance_Schedule.DeletedFlag)=False) AND ((tbUser.ActiveFlag)=True));

[CJ_London]

so you have a union query with 31? separate select statements? If so, how is it working since you have a ; just before the part you have highlighted (; Union......)

there is a lot that can be simplified

IIf([tbAttendance_Schedule].[TypeID]=1," - S",IIf([tbAttendance_Schedule].[TypeID]=4," - DD",IIf([tbAttendance_Schedule].[TypeID]=8," - F",IIf([tbAttendance_Schedule].[TypeID]=9," - JD",IIf([tbAttendance_Schedule].[TypeID]=10," - FMLA",IIf([tbAttendance_Schedule].[TypeID]=11," - DIS",IIf([tbAttendance_Schedule].[TypeID]=13," - ML",IIf([tbAttendance_Schedule].[TypeID]=5," - " & [Hours] & " DH","N/A")))))))) AS EMAID

could be

" - " choose(([tbAttendance_Schedule].[TypeID],"S","VW","","DD", [Hours] & " DH","","","F"" - JD","FMLA","DIS","","N/A") AS EMAID

and modify your criteria to

tbAttendance_Schedule.TypeID) In (1,2,4,5,8,9,10,11,13)

and you don't need the union second query - perhaps you don't need the rest either with a few more tweaks

Better still, include a scheduletype table to store 'S','VW' etc

these bits will never be true because your criteria excludes anything over 13

[tbAttendance_Schedule.TypeID]>13,"Unplanned Time","N/A"))
[tbAttendance_Schedule.TypeID]>13,"Planned Time Off"

Day, month, year and date are all reserved words and should not be used as field names

As far as your question about not getting the rest of the week, there is nothing in the criteria restricting this so far as I can see

So it might have something to do with your GetWeekEndingActual([tbAttendance_Dates.Date]) function call or more likely something to do with your tbAttendance_Dates and/or tbAttendance_Schedule tables

[June7]

And what do you want to happen at first of month when it starts in middle of week? Do you want that entire week to display?

What day do you consider first day of week - Monday? Calculate the month for Monday of week each date falls in and use that value to sort/filter records.

[Kristi_Christopherson]

Thank you so much for ways to simplify what was previously written. I wondered about the GetWeekEndingActual too and this is what the VBA says for that, and before that is a GetWeekEnding which is included in the query builder for the report:

Public Function GetWeekEnding(ByVal dDate As Date) As Date


Dim mInt As Long
Dim tDate As Date


mInt = Month(dDate)
tDate = dDate + (7 - DatePart("w", dDate))


If Month(tDate) = mInt Then
GetWeekEnding = tDate
ElseIf Month(tDate) <> mInt Then
Do Until Month(tDate) = mInt
tDate = tDate - 1
Loop
End If


GetWeekEnding = tDate


End Function


Public Function GetWeekEndingActual(ByVal dDate As Date) As Date


Dim tDate As Date


tDate = dDate + (7 - DatePart("w", dDate))


GetWeekEndingActual = tDate


End Function

If you think any of that seems off, I would appreciate more help. I'll look into the tables as well. Thanks again for your response!

[Kristi_Christopherson]

June7, the beginning of the month calendar pages look fine. It's basically a report for each month so if a person is on vacation during a week that falls on 2 months, their name - VW should show on both the end of the month their vacation starts, and then beginning of the month their vacation ends. Below is an example for DParker - VW and SBlack - VW who don't show in August but do show in September.

[CJ_London]

Your function is only considering week endings that are in the current month. With a vague (and not recommended) field name ‘date’ not clear whether this is a start date, end date or a specific date - so back to reviewing your tables.

have to say this seems a very complex way of producing this report