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

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

Post by Jame » Thu, 10 Jul 2003 14:10:27


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;
 
 
 

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

Post by Allen Brow » Thu, 10 Jul 2003 14:57:54

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.
Tips for Access users - http://www.yqcomputer.com/
Reply to the newsgroup. (Email address has spurious "_SpamTrap")



this

 
 
 

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

Post by Dale Fy » Thu, 10 Jul 2003 21:18:05

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;


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]))



are no
only the
not
the days
confusing
this
tblEvents.PUDate
 
 
 

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

Post by Jame » Thu, 10 Jul 2003 23:09:21

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






no
the
days
 
 
 

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

Post by Allen Brow » Thu, 10 Jul 2003 23:23:25

Yes, you have posted this a new thread. Makes sense.