Grouping Question - Filtering?

[Matthew S]

I am trying to have a report group people that meet certain conditions. For
example lets say my report shows the three names below. I only want Joe Smith
and John Smith to show because they have the OS II Title. Where I run in to
trouble is when I get only Joe and John to display I still want to see their
other titles. It may be how our database is set up with the table structure,
however I am wondering if there is any VB code that would be able to only
display groups that have the OS II tile in it.

Name
Joe Smith

Title
OS II
OS IV

Jane Smith

Title
OS I
OS V

John Smith

Title
OS II
OS V

 

[Matthew S]

My Table Structure as shown in my query that runs the report. I showed the
three tables involved with the fields I use to get my data (Not all fields in
tables are listed):

TBLTemps1
EmployeeID JOINS to Employee ID in TBLAssignments1
Temp_Name
Status (only shows active employees)

TBLAssignments1
EmployeeID
Temp_Pay links to Pay_Rate

TBLRates
Title_Code
Pay_Rate


This is my query which shows all employees grouped correctly however I want
to see only the employees with Specific Title_Code's. However I do not want
just the employee and the one Title_Code I am looking for... I want to see
other Title_COdes they had in the past which is why I use TBLAssignments1.

 

[John Spencer]

Sounds as if you need a subquery to identify the employees that have the
appropriate title codes. One way to do this is to use something like the
following:

SELECT tblTemp1.EmployeeID, tblRates.Title_Code
FROM (tblTemp1 INNER JOIN TblAssignments1
on Tbltemp1.EmployeeID = tblAssignments1.EmployeeID)
INNER JOIN tblRates
On tblAssignments.Temp_Pay = tblRates.Pay_Rate
WHERE TblTemp1.EmployeeID in
(SELECT A1.EmployeeID
FROM TblAssignments1 as A1 INNER JOIN tblRates as R1
ON A1.Temp_Pay = R1.Pay_Rate
WHERE R_1.Title_Code = IN ("a","B","C")



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Matthew S]

Excellent.