Show all if yes in parameter

[Marie]

I have a database that keeps track of the traffic violations our drivers
receive while driving our buses. If we pay their fine, the driver needs to
sign a release form that allows us to deduct the amount from their pay check.
I need a report to keep track of any violations that we paid that are pending
the driver's signature. This wouldn't be a problem if I just wanted the
"open" releases; I could just create a parameter query with No for the
releases signed. If there is a pending release signature, my company wants a
list of all previous violations, whether the release is signed or not in
order to keep track of how many violations the driver is getting within a
couple of months. When I create a parameter for Release Signed, NO, only the
blank releases come up. In other words, we don't need to see any drivers at
all if they have no pending releases. We need only those with pending
releases INCLUDING all past releases, whether they are signed or not. Is
anyone able to help me with this? Thanks.

 

[John Spencer]

You need to use two queries or a subquery in the where clause.

SQL would look something like the following
SELECT V.DriverID, V.Violation
FROM Drivers as D INNER JOIN Violations as V
WHERE D.DriverID in
(SELECT V2.DriverID
FROM Violations as V2
WHERE ReleaseSigned = False)

Two query solution
-- Write a query that shows all the violations with pending releases and
save the query

-- Write a second query to show all the information you want from the tables
and include the query above. Join the the query to the Driver table

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marie]

I created a query using the violations table and I included Drivers' SS# and
pending releases. I then created another query using the violations table,
the drivers table, and the firest query. I linked the SS# field on the first
query with the drivers table SS#. It is still showing drivers who have no
pending releases. I know I must doing something wrong. Is this what I was
supposed to do?

 

[John Spencer]

It sounds correct, but if it is not working perhaps you can post the SQL of
your queries.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marie]

OK. Here it is:

SELECT DISTINCTROW tblViolations.ViolationNumber,
tblEmployeeInfo.First_Name, tblEmployeeInfo.Last_Name,
tblViolations.PayrollReleaseYN, tblViolations.PayrollReleaseDate,
tblEmployeeInfo.Emp_Status, tblViolations.ViolationDate,
tblEmployeeInfo.Last_Name & ", " & tblEmployeeInfo.First_Name AS Name
FROM (Test1 INNER JOIN tblEmployeeInfo ON Test1.SSN2 = tblEmployeeInfo.SSN)
INNER JOIN tblViolations ON tblEmployeeInfo.SSN = tblViolations.SSN2;

 

[John Spencer]

You didn't post Test1. I'm guessing that you want the people where
PayrollReleaseDate is Blank (null)

If I guessed right, then you should be able to use the following query.

SELECT DISTINCTROW tblViolations.ViolationNumber,
tblEmployeeInfo.First_Name, tblEmployeeInfo.Last_Name,
tblViolations.PayrollReleaseYN, tblViolations.PayrollReleaseDate,
tblEmployeeInfo.Emp_Status, tblViolations.ViolationDate,
tblEmployeeInfo.Last_Name & ", " & tblEmployeeInfo.First_Name AS Name
FROM tblEmployeeInfo INNER JOIN tblViolations ON tblEmployeeInfo.SSN =
tblViolations.SSN2
WHERE tblEmployeeInfo.SSN IN
(SELECT SSN
FROM TblViolations
WHERE tblViolations.PayrollReleaseDate is Null)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marie]

I just tried the code you gave me, but the drivers who have no pending
releases are still coming up. I have posted both querys again (this time I
included the first one as well...sorry about that)

SELECT tblViolations.PayrollReleaseYN, tblViolations.SSN2
FROM tblViolations
WHERE (((tblViolations.PayrollReleaseYN)=No));

SELECT DISTINCTROW tblViolations.ViolationNumber,
tblViolations.PayrollReleaseYN, tblViolations.PayrollReleaseDate,
tblViolations.ViolationDate, tblEmployeeInfo.Last_Name & ", " &
tblEmployeeInfo.First_Name AS Name
FROM (tblEmployeeInfo INNER JOIN Test1 ON tblEmployeeInfo.SSN = Test1.SSN2)
INNER JOIN tblViolations ON tblEmployeeInfo.SSN = tblViolations.SSN2;

 

[John Spencer]

SELECT tblViolations.PayrollReleaseYN, tblViolations.SSN2
FROM tblViolations
WHERE (((tblViolations.PayrollReleaseYN)=No));

Does the above return only the drivers you want to see? If so, then the
following should give you the results you want

SELECT DISTINCT tblViolations.ViolationNumber
, tblViolations.PayrollReleaseYN
, tblViolations.PayrollReleaseDate
, tblViolations.ViolationDate
, tblEmployeeInfo.Last_Name & ", " & tblEmployeeInfo.First_Name AS
DriverName
FROM tblEmployeeInfo INNER JOIN tblViolations
ON tblEmployeeInfo.SSN = tblViolations.SSN2
WHERE tblEmployeeInfo.SSN IN
(SELECT tblViolations.SSN2
FROM tblViolations
WHERE tblViolations.PayrollReleaseYN=No)

If that does not work then I am stumped.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marie]

Perfect! It worked! Thank you so much.

SELECT tblViolations.PayrollReleaseYN, tblViolations.SSN2
FROM tblViolations
WHERE (((tblViolations.PayrollReleaseYN)=No));

Does the above return only the drivers you want to see? If so, then the
following should give you the results you want

SELECT DISTINCT tblViolations.ViolationNumber
, tblViolations.PayrollReleaseYN
, tblViolations.PayrollReleaseDate
, tblViolations.ViolationDate
, tblEmployeeInfo.Last_Name & ", " & tblEmployeeInfo.First_Name AS
DriverName
FROM tblEmployeeInfo INNER JOIN tblViolations
ON tblEmployeeInfo.SSN = tblViolations.SSN2
WHERE tblEmployeeInfo.SSN IN
(SELECT tblViolations.SSN2
FROM tblViolations
WHERE tblViolations.PayrollReleaseYN=No)

If that does not work then I am stumped.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..