show all of the record from one table but filtered by a different table.

  • Thread starter Thread starter James
  • Start date Start date
J

James

I am trying to show all of the dates in the calendar even if there are no
events for that date. However when I try to filter them and show only the
events for a certain employee but still show all of the days it does not
work. I can only show all of the record with no employee or only the days
where the employee and the date are equal. If this is not to confusing this
is the current code. Can anyone help? Thanks in advance.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate = tblEvents.PUDate
WHERE (((tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID;
 
The EmployeeID field will be null when there are not records, so you need:

WHERE ((tblEvents.EmployeeID Is Null) Or
(tblEvents.EmployeeID = [forms]![frmDriver]![cmbDriver]))
 
Allen,

That won't work if there are other employees on a given date.

I think he needs a nested subquery to limit the records from tblEvents
that he joins to the calendar. That way, he can join only those
records from tblEvents that belong to a specific driver to the
calendar.

SELECT Calendar.CalDate, E.DODate, E.PUDate,
E.TotalHours, E.rndhours, E.LimoID
FROM Calendar
LEFT JOIN
[SELECT tblEvents.*
FROM tblEvents
WHERE tblEvents.EmployeeID = Forms!frmDriver!cmbDriver]. as E
ON Calendar.CalDate = E.PUDate
GROUP BY Calendar.CalDate, E.DODate, E.PUDate,
E.TotalHours, E.rndhours, E.LimoID;


--
HTH

Dale Fye


The EmployeeID field will be null when there are not records, so you
need:

WHERE ((tblEvents.EmployeeID Is Null) Or
(tblEvents.EmployeeID = [forms]![frmDriver]![cmbDriver]))
 
Thank you both. So far Allen's way worked but, thank for your help Dale.
Whoever, I have a form that has one combo box with all of the employee's in
it, and later will add a date range. When I select the employee from the
list and then hit the view report button nothing is diplayed and no error
messages. If I don't have an employee in it it shows the error message to
select the employee like I want. The code is below for both the form and
the report:

This is from the Form:

Private Sub btnDriver_Click()
If IsNull([cmbDriver]) Then
MsgBox "You must select a driver's name from the list."
DoCmd.GoToControl "cmbDriver"
Else
Me.Visible = False
End If
End Sub


This is from the report:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "frmDriver"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmDriver", , , , , acDialog, "Driver"
If Not IsLoaded("Driver") Then
Cancel = True
End If
End Sub


Allen Browne said:
The EmployeeID field will be null when there are not records, so you need:

WHERE ((tblEvents.EmployeeID Is Null) Or
(tblEvents.EmployeeID = [forms]![frmDriver]![cmbDriver]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

James said:
I am trying to show all of the dates in the calendar even if there are no
events for that date. However when I try to filter them and show only the
events for a certain employee but still show all of the days it does not
work. I can only show all of the record with no employee or only the days
where the employee and the date are equal. If this is not to confusing this
is the current code. Can anyone help? Thanks in advance.

SELECT Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID
FROM Calendar LEFT JOIN tblEvents ON Calendar.CalDate = tblEvents.PUDate
WHERE (((tblEvents.EmployeeID)=[forms]![frmDriver]![cmbDriver]))
GROUP BY Calendar.CalDate, tblEvents.DODate, tblEvents.PUDate,
tblEvents.TotalHours, tblEvents.rndhours, tblEvents.LimoID;
Click to expand...
Click to expand...
 
Back
Top