Active/Inactive - REPORTING

[Ray via AccessMonster.com]

Hi there..

i got this report the displays trainees with no training history and they
need to be scheduled for training.

with this code, it works fine.

SELECT Trainees.TraineeID, Trainees.FirstName, Trainees.LastName, Trainees.
JobTitle, Trainees.ContractorName, Trainees.Language, Trainees.TraineeStatus
FROM Trainees
WHERE (((Trainees.TraineeID) Not In (SELECT DISTINCT TraineeID FROM [Training
Records])));

now, i added a TraineeStatus field on Trainees table with a lookup data so
the user can make a selection from Active, Inactive & OnVacation.

Now, what the user want to display on the report, in addition to showing
trainees with NO TRAINING HISTORY, is to show trainees tagged with ACTIVE on
their status. So, Inactive & OnVacation tagged trainees will not be shown as
they are not required to do training session.

Thanks.

 

[Allen Browne]

1. Create a query using both the Trainees and Training Records tables.

2. In the upper pane of query design, double-click the line joining the 2
tables. Access pops up a dialog with 3 options. Choose the one that says:
All records from Trainees, and any matches from Training Records.
(Technically, this is known as an outer join.)

3. Depress the Total button on the toolbar/ribbon.
Access adds a Total row to the design grid.
Accept Group By under the fields from the Trainees table.

4. Under the primary key field from the Training Records table, choose Where
in the Group By row, uncheck the Show box, and enter this in the Criteria
row:
Is Null

5. Drag the TraineeStatus field into the query grid again. In the Total row
under this field, choose Where, uncheck the Show box, and enter the Criteria
you want.

The query will give you one record:
- for each trainee (since its a Totals query)
- restricted to the right status (because of the criteria)
- and only if they have no training record (outer join plus the criteria.)