Queries:Hiding Duplicate/Re-occuring Values

Preface: I had a database which was showing the data as follows:

training_date	employee	Class	Instructor
02-Jun-00	Juan Gradillas	DCAR's S-00-086 & S-00-087	Carey Burton
02-Jun-00	Ramon E. Cordova	DCAR's S-00-086 & S-00-087	Carey Burton
02-Jun-00	Anna Favorite	DCAR's S-00-086 & S-00-087	Carey Burton
02-Jun-00	Rosa Andrade	DCAR's S-00-086 & S-00-087	Carey Burton
02-Jun-00	Jeanne Hakey	DCAR's S-00-086 & S-00-087	Carey Burton
02-Jun-00	Francisca (Alicia) Tiznado	DCAR's S-00-086 & S-00-087	Carey Burton
11-Dec-00	Ana Andrade	M S D S - Liquid Nitrogen	Carey Burton
11-Dec-00	Felix Garcia	M S D S - Liquid Nitrogen	Carey Burton
11-Dec-00	Bernadette Sigarroa	M S D S - Liquid Nitrogen	Carey Burton
11-Dec-00	Kathleen Miranda	M S D S - Liquid Nitrogen	Carey Burton
11-Dec-00	Eva Tapia	M S D S - Liquid Nitrogen	Carey Burton
11-Dec-00	Juan Gradillas	M S D S - Liquid Nitrogen	Carey Burton
11-Dec-00	Valerie Quijada	M S D S - Liquid Nitrogen	Carey Burton
29-Jan-01	Eva I Tapia	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Konrad Ottleben	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Jeanne Hakey	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Ana Andrade	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Valerie Quijada	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Juan Gradillas	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Bernadette Sigarroa	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Ramon E. Cordova	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Kathleen Miranda	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Eva I Tapia	Hand Tool Safety	Carey Burton
29-Jan-01	Kathleen Miranda	Hand Tool Safety	Carey Burton
29-Jan-01	Anna Favorite	Hand Tool Safety	Carey Burton
29-Jan-01	Maria Cortes	Hand Tool Safety	Carey Burton
29-Jan-01	Elvia Noriega	Hand Tool Safety	Carey Burton
29-Jan-01	Dora Pena	Hand Tool Safety	Carey Burton
29-Jan-01	Bernadette Sigarroa	Hand Tool Safety	Carey Burton
29-Jan-01	Myrna Fuentes	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Felix Garcia	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Jeanne Hakey	Hand Tool Safety	Carey Burton
29-Jan-01	Anna Favorite	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Maria Cortes	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Elvia Noriega	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Dora Pena	Safety - 1st Rule (Know Your Job)	Carey Burton
29-Jan-01	Felix Garcia	First Aid for Burns	Carey Burton
29-Jan-01	Ramon E. Cordova	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Eva I Tapia	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Kathleen Miranda	First Aid for Burns	Carey Burton
29-Jan-01	Anna Favorite	First Aid for Burns	Carey Burton
29-Jan-01	Maria Cortes	First Aid for Burns	Carey Burton
29-Jan-01	Elvia Noriega	First Aid for Burns	Carey Burton
29-Jan-01	Felix Garcia	Hand Tool Safety	Carey Burton
29-Jan-01	Bernadette Sigarroa	First Aid for Burns	Carey Burton
29-Jan-01	Valerie Quijada	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Konrad Ottleben	First Aid for Burns	Carey Burton
29-Jan-01	Jeanne Hakey	First Aid for Burns	Carey Burton
29-Jan-01	Ana Andrade	First Aid for Burns	Carey Burton
29-Jan-01	Valerie Quijada	First Aid for Burns	Carey Burton
29-Jan-01	Ramon E. Cordova	First Aid for Burns	Carey Burton
29-Jan-01	Juan Gradillas	First Aid for Burns	Carey Burton
29-Jan-01	Debby Reyes	First Aid for Burns	Carey Burton
29-Jan-01	Dora Pena	First Aid for Burns	Carey Burton
29-Jan-01	Elvia Noriega	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Myrna Fuentes	First Aid for Burns	Carey Burton
29-Jan-01	Ana Andrade	Hand Tool Safety	Carey Burton
29-Jan-01	Valerie Quijada	Hand Tool Safety	Carey Burton
29-Jan-01	Juan Gradillas	Hand Tool Safety	Carey Burton
29-Jan-01	Myrna Fuentes	Hand Tool Safety	Carey Burton
29-Jan-01	Ramon E. Cordova	Hand Tool Safety	Carey Burton
29-Jan-01	Kathleen Miranda	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Myrna Fuentes	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Maria Cortes	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Juan Gradillas	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Dora Pena	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Bernadette Sigarroa	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Felix Garcia	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Konrad Ottleben	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Jeanne Hakey	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Ana Andrade	M S D S - What you'll find on an	Carey Burton
29-Jan-01	Konrad Ottleben	Hand Tool Safety	Carey Burton
29-Jan-01	Anna Favorite	M S D S - What you'll find on an	Carey Burton

Notice how, in this example, Carey Burton taught the same class on June 2, 2000, and on December 11th, 2000. The STUDENTS were different, but the CLASS and DATE were the same. I wanted it to screen out the employee name so that only one (1) entry would appear for each class. This was difficult to do, due to the fact that the table holding the information had an entry for each class and employee. Each of the 7 entries at the top of the above example represented seven (7) entries in the database's main table.

I redesigned it to a TOTALS query, which ended up looking like this:

Here is the newsgroup message I posted

From: Larry R Harrison Jr 
Subject: Preventing Duplicates in a Query
Date: Wednesday, June 13, 2001 3:33 PM

I have a query which plucks values from another query. This query summarizes
classes which are taught. It shows the instructor, the students, the nature
of each class. The way the query shows data, if ONE class has 5 employees in
it at a certain time, it has 5 entries, with the instructor's name and class
name repeating each time. (The contents of the EMPLOYEE field would change,
though.)

I want a query to summarize by INSTRUCTOR, to in effect so each class that
an instructor taught. Within each day & class, though, I want it to screen
out duplicate dates, so that, in the above example, only ONE entry would
appear--showing that a class was taught by that teacher on that date,
WITHOUT SHOWING EACH EMPLOYEE and resulting in 5 entries.

How could I do this?

Larry
microsoft.public.access.queries

From: Warren Alford [warren.alford@wdc.com]
Subject: Re: Preventing Duplicates in a Query
Date: Wednesday, June 13, 2001 4:00 PM

Make the query a summary/group by query and group on instructor and on date
and  count students if you want.

Warren