Hiding duplicate values of a field in an Access Report

[Guest]

I have a query that is working just fine. There are some records in the
query that have 1 value in one field and more than one value in another
field, so even with "unique values" turned on, I get duplicate values in some
fields which I understand just fine. There are eight different fields that
this affects. The problem is when I go to the report, I want to hide those
duplicate values, but since I the duplicates show up in any one (perhaps more
than one) field, it is impossible to sort and then hide duplicates. When it
is sorted by one of these fields, and then duplicates show up in another
field, the duplicates are sometimes separated by another value, thus hiding
duplicates does not work. Here are a couple of samples (on a smaller scale)
of what I am trying to explain:

Column1 Column2 Column3
value1,1 value2,1 value3,1
value1,1 value2,2 value3,1
value1,2 value2,1 value3,1
value1,2 value2,2 value3,1

As you can see, if I sort on column1, the hide duplicate will not work in
column 2, and if I sort on column2, the hide duplicate will not work in
column 1. The hide duplicate will work in column 3. Any ideas?

 

[Steve Schapel]

Lanita,

In effect, you want the records returned by the query to behave as if
they were all one record, with each column showing just a list of the
unique values in that field. Regardless of order, and with no
meaningful correspondence between the column values for each row. Am I
right?

If so, there are two basic approaches I can think of, both of which are
admittedly kludgey, but it depends on the amount of data we are talking
about I suppose.

One would be to build your report using a separate subreport for each
affected column, with each subreport's Record Source using a SELECT
DISTINCT query on the records you are woking with.

The other is to use a custom concatenation function in the report's
query to pull together the unique values in each field, separated by a
carriage return. This would be slow if you are trying to apply it to
multiple columns with a large amount of data. Have a look at the
"Generic Function To Concatenate Child Records" link at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for a
starting point on this one.