Hide Duplicates selectively

[SpaceEd]

Hi

I'm trying to make a report that hides duplicates. I have several categories such as "Function," "Responsibility" and "Action." They have something similar to a one-to-many relationship in that there are several Actions for each Responsibility, and several Responsibilities to each Function.

The problem is the same values disappear although they may be in different groups. It is the exact problem discussed here:

http://allenbrowne.com/casu-21.html

I tried this solution, but it doesn't seem to work for Access 2010. When I try entering the solution, Access can't seem to refer to itself from the Control Source code. Would anyone know the solution for the Access 2010 version?

Any help would be really appreciated.


Thank you!

[June7]

I just tested this with Access2010. Only works properly when the report is run in PrintPreview or sent to printer. This is a recurring situation with reports. Some things only work in PrintPreview.

[SpaceEd]

Thanks for your response.

I understand it only works for print preview, but what I mean is it will still hide the duplicates even for different groups. For example, if I have

Function 1...Responsibility A
Function 1...Responsibility B
Function 2...Responsibility B
Function 2...Responsibility C

Responsibility B for Function 2 is still hidden even though I don't want it to be hidden.

It's the problem described in the link above.

Thanks again. Your help would be appreciated.

[June7]

I tested and cannot replicate the issue. Do you want to provide project for analysis?

[SpaceEd]

Essentially what I would like it to do would be to blank Action out only if both Function and Responsibility are duplicates, and Action is a duplicate. However, the same action could appear in two different functions.

Function 1...Resp A...Action A
.............................Action B
.............................Action C
.................Resp B...Action A
.............................Action D
Function 2...Resp A...Action E
.............................Action F
.................Resp D...Action F
.................Resp E...Action A
.............................Action G


Is there an expression to do something like
Iif(ISDUPLICATE?([Function]),Iif(ISDUPLICATE?([Resp]),null,[Resp]),[Function])

[SpaceEd]

Sure I can send it via email if you want

[June7]

Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post. The Attachment Manager is below the Advanced post editor.

I think you should use report Grouping & Sorting to accomplish what you want. Have a group for Function and a group for Responsibility. Action could be detail.

[SpaceEd]

If you go to "Search" then click the line that has "15" and then click the "Go to Form" button. The form shows the data it should have. Then if you click the "View Report" button, you may see what I'm talking about. In the third and fourth columns, it is missing data it shouldn't be.

[June7]

Your data does not fit either the Allen Browne solution or the Hide Duplicates. You will have to work with data in report Group headers and the stair-step effect of display. Anything else would probably involve elaborate code and temp table.

[SpaceEd]

Ok is there an sql statement or vba code that checks iif(THIS FIELD IS DUPLICATE, Hide Field, Enter this value) type function?

Thanks for your help again and taking the time to look. I appreciate it.

[June7]

No function.

As I said, anything else would probably require elaborate VBA code and a temp table.

[SpaceEd]

Hmm... ok the only problem with the stepped display is because there are so many categories, one line of info could take a whole page. I've played around with iif...isnull...dlookups... but unfortunately that slows it down.

Ok thanks.

[SpaceEd]

Hi June7,

I've been reading up on Temp tables, but would you be able to point me in the right direction as to begin getting this done? I'm not quite sure where to start? Can you think of what type of functions I might need.

[June7]

This thread has example of one of my uses of a temp table http://forums.aspfree.com/microsoft-...ry-322123.html

[SpaceEd]

I created a pretty elaborate vba code with the associated form, and then linked it to the expression in the report. It worked somewhat but it turned out to be really complex and gimmicky upon updating.

Can you or anyone think of another simpler solution? Is there anything I can fix with the groupings? What is with the Hide Duplicates property that still hides the field when it is in a different grouping?