Select One Record Per Staff

[DrStephen]

I have a query that returns multiple records for users (staff taking exams). Each record indicates an untaken exam. I have a query that will deliver all of the untaken exams (one exam per record). Unfortunately, that produces a very large number of records.

I want to write a report that, independent of the number of exams listed as untaken for a staff person, only one record exists per staff person.

For example, if John Smith has 10 records returned (10 exams not taken), I only want 1 record with his name on it. The purpose is to produce a succinct nag list.

 

[dhookom]

It would really help if we had a clue what your tables and field names are and how your tables are related. Generally you should be able to get the desired results with a totals/Group by query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrStephen]

Sample of data follows:

userid lastname firstname testname grade

123456 Jones John Addictions
123456 Jones John Safety
123456 Jones John Compliance
784584 Smith Betty Addictions
789584 Smith Betty Safety
789584 Smith Betty Compliance

...and so on...there are actually 11 exams...my current report lists every one of the records returned in the query (so, 6 in the above sample). I'm trying to get it to return only 2 (an unduplicated listing of staff in the dataset)...

 

[PHV]

A starting point:
SELECT userid, lastname, firstname, Min(testname) AS Exam
FROM yourTable
GROUP BY userid, lastname, firstname

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jedraw]

This may be helpful.

SELECT distinct userid, lastname, firstname
FROM yourTable