hide duplicates for same subset of data

[diwin]

Can I control Hide duplicates in code?

I have created a query with a few inner joins for a report recordsource.

Main table has Sites
Secondary table has Points
Tertiary table has Bugs
Quarternary table has LifeStages

This results in each LifeStage producing its own record in the report in single row, containing about 20 fields from all the above tables.

In the report there are no headers and the records are grouped in the right order, Site/Point/Bug/LifeStage. And the textboxes are set to hide duplicates.

Too many duplicates are being hidden, though. Using BugName as an example, if a BugName record ("Asellidae") is part of the only record for that entire Site (BRCBR1) and the BugName in the next record, which is for the next Site (BRCL09), is the same as the record preceding, the datum "Asellidae" does not appear.

So I want to hide duplicates for the BugName field only if it is in the same Site or Point for instance. This way, all LifeStages will list beside/under the one BugName, UNTIL there is a change in Point OR (Site AND Point).

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[diwin]

Is this even possible?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[dhookom]

I don't think you need any code or special tricks. Just make sure you have group headers everywhere. Duplicates are not hidden across group sections.

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

For everything...there is a trick. I have tried in vain and gotten results that are wildly different (...including 2 different records printing on the same line!) and I don't understand why. Mostly because I don't understand the way "movelayout" actually works. Is there anyway you could pinpoint what I am doing wrong? I have supplied my attempts that are coded wrongly or too heavily, and one that looks the way I want it to look when done right (which is unsuitable since it skipps most columns when exported to excel). It must be obvious to an expert.

My frustrations verbalized:

1. With 3 grouping headers, do I use "movelayout = false" 3 times or just twice?

2. Where do I put it -- format of detail or header? Where do I put the invisible text box -- format of detail or header, and which header?

3. Do I use one of those invisible textboxes for each header? Which one is included with each version of "movelayout = false"?

4. which fields do I keep in the detail section, which do I copy into each of the header section?


Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[dhookom]

I think we have lost sight of your original question. My assumption was that you wanted to hide specific field values in the detail section when they were the same as the previous record. You stated

there are no headers

. I believe you also wanted to make sure that when a grouping level value changed the duplicate value actually displayed rather than being hidden.

I want you to remove all of the code and then just add a single group header section for all of your groups.


Duane
Hook'D on Access
MS Access MVP

 

[diwin]

ok. done. now what?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[dhookom]

Tell us what you are seeing in your report and how it does or doesn't meet your expectations. It would help if you also replied back whether I understand your situation or not. I am feeling like the blind leading the blind.

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

Your description of my original question seems right to me. I want the duplicate record displayed in a textbox that is grouped in grouping level 7 even when value is changed for a textbox that is grouped in grouping level 5. Is this clear to you?

I have 8 headers, all empty.
All set to Whole Group.
Hide duplicates for all textboxes in Detail are set to NO.
I removed all lines, all code and all invisible textboxes with values of "=1"

So all is grouping well, but nothing is hidden.


Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[dhookom]

I'm not sure why you have 8 headers. You stated earlier these four "Site/Point/Bug/LifeStage" should be the headers. Are these all Group Headers? Are you displaying the group header sections of these?

You should have Hide Duplicates set to Yes for the field values you don't want to see duplicates within your group. Nothing will hide if you don't have the Hide Duplicates set to yes.

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

Thanks Duane.

Going home early with a headache. I will have a look on the weekend and reply by Monday.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[diwin]

Monday, Friday... What's the difference?!
I finished the database again (and passed it off). and still need to fix this one report again.

I was actually hoping to get it sorted out with just a couple headers and expand the solution for the others. What I have put in the zipped database that I uploaded to

http://www.savefile.com/files/2023541,

is the actual whole report. It is called rptInfo1A.

I have actually provided 3 reports:

rptInfo1A is sorted and has fields sorted, grouped and hidden with my coding technique. It looks great, but when exporting, turns into the attached xls file---Many of the fields are missing; Year and Point are there, but all fields between them do not export. You can try that by exporting rptInfo1A.

Ignore rptInfo1AClose.

rptInfo1AHeaders has all the headers for sorting and grouping, and there is no coding at all. Hide Duplicates is set to no for all fields. If I set it to Yes for all appropriate fields, values are hidden that should be shown, being the first instance of that value in that field in one of the groupings, in spite of the fact that the previous record has the same value in that field.

If this can be done with headers, I am all for it. I just could not get this to actually work, inspite of your advice, and bits and pieces I got off other sites.

My questions:
Can this be accomplished with headers?
How?
Is code needed too (i.e. movelayout=false)?
If so, which section(s) does this code go in?
Do I need invisible textbox(es) with ControlSource = 1 and RunningSum = Over Group?
If so, in which section do I place it (them)?

I will apply your advice to rptInfo1AHeaders. So you can test it to see if it will work, even if you just get it working with a couple headers, I can take it from there.


Daniel Dillon
O o . (<--- brain shrinking at rate shown.)