Unique Values vs Unique Records

[Miaplacidus]

I have a query that produces a two diverent records for
the same individual. He holds two positions in the company.
On the organization report he should appear in two places
but in the head count report he should appear in only one.

I thought Unique values would take care of this, The help
screen says:

(1)"You can use the UniqueValues property when you want to
omit records that contain duplicate data in the fields
displayed..."

but the next line in the help screen says:

(2)"...if a query's output includes more than one field,
the combination of values from all fields must be unique
for a given record to be included in the results."

and those two statements are not the same.


Then in the setting box on the help screen it says:

(3)"Displays only the records in which the values of all
fields displayed in Datasheet view are unique."

This sounds to me as if it aggrees with (1), which is what
I want, and not with (2)

Then if you go look at the help screen for UniqueRecords
it says:

"You can use the UniqueRecords property to specify whether
to return only unique records based on all fields in the
underlying data source, not just those fields present in
the query itself."

This is still a different matter and it applies the
uniqueness criteria to values of fields which are not
shown in the results.

It seems to me that there are four conditions:

1)Are the records shown in the result distinct in that at
least one field (in the result) contains a different value
from otherwise similar records.

2)Are the records shown in the result distinct in that no
field (in the result) contains duplicate values.

3)Are the records shown in the result distinct in that at
least one field (in the underlying tables) contains a
different value from otherwise similar records.

4)Are the records shown in the result distinct in that no
field (in the underlying tables result) contains duplicate
values.

How can I achieve situation 2)?

In my case since I know where the duplication must lie I
can fix the problem with an additional subquery, but as a
general rule.......

 

[Liz]

I would do this in the report...not the query.......or set
up two queries to run each report.

If one query....on the Organization report Group by Name,
with Title in the detail.

In the Head Count Report....group by Name, no title
included on the report.

How do you have these reports/query(ies) set up?