Listbox of Employees on First Sunday, First Monday, First Tuesday... of each month

[JustinN1]

I have a table of employees. I need a column that lists a scheduled day for them for the 1st Sunday, 1st Monday, 1st Tuesday, 2nd Sunday, 2nd Monday, or 2nd Tuesday of each month. This will only be one column with the option of the days of the month I just mentioned. The next problem I have is I have a form with a listbox on it. I need those employees listed in the listbox if they match with the current day (1st Sunday, 1st Monday, 1st Tuesday.....)

Sorry if I have not provided enough information. I am fairly new to Access and would appreciate any help.

 

[UncleCatwoman]

You need three tables:

tbl_Employees [with fields EmpID, EmpName and anything else you need for employees]

tbl_WorkDays [with field WorkDay]
I would list these as Sun1, Sun2, Sun3, Sun4, Sun5, Mon1, Mon2, Mon3 ... you get the picture.
This is your control table for these.

tbl_Availability [with fields EmpID and WorkDay]
Set your relationships so the EmpID has to be in tbl_Employees and the WorkDay has to be in tbl_WorkDays.
Each employee gets a record for each day they're available:
EmpID 253, Sun1
EmpID 253, Sun2
EmpID 257, Mon1
So, at max, each employee has 35 records.

Then, on your form, assuming you have a drop-down to select the work day [cboWorkDay] and the a button [btnShowAvailable] to show the available employees in a list box [lstAvailableEmps] ...

I would use this in the button_click event:

Private Sub btnShowAvailable_Click()

' verify a work day has been selected
If Nz([cboWorkDay],"") = "" Then
' display appropriate error message
Exit Sub
End If

' declare variables
Dim strSQL as string

' build the SQL string to use for the source
strSQL = "SELECT tbl_Employees.EmpName FROM tbl_Availability INNER JOIN tbl_Employees ON tbl_Availability.EmpID = tbl_Employees.EmpID WHERE (((tbl_Availability.WorkDay) = '" & [cboWorkDay] & "'));"

' update the list box
Me.lstAvailableEmps.RowSource = strSQL
Me.lstAvailableEmps.requery

End Sub


Hope this helps!

UC