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.)

 

[dhookom]

Untested...
You could try create a duplicate sorting and grouping level perhaps on Bugs. Make it the same height as the detail section and add the same controls as the detail section. This should display exactly the same as the first record of the detail section in the Bugs section.

Add a text box to the detail section
Name: txtCountLifeStages
Control Source: =1
Running Sum: Over All
Visible: No

Go into the code of the On Format event of the detail section and add these lines of code:

    If Me.txtCountLifeStages = 1 Then
        Me.MoveLayout = False
    End If

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

Thanks Duane. I tried it.

Not sure how to evaluate what I see here. There are several fields that require this manipulation. I believe they would include all the fields used for sorting (named below), and others. Would I have to create a header like this for each one? -- or would that not work?

Attached is a .snp of the report with test data in it. I put the original report at the attache url too. I put a red line at the top of the detail section. It appears 3 times in this report, ...meaning I guess that seemingly that there are 3 instances where the BugName would not have appeared for the first record of some higher level of sorting. So your method appears to work.

I made the new header grey, sorted on BugName.
Other sorting, without headers, are Year, Season, SubWaterShed, WatercourseName, SiteCode, and Point.

Here is the source query...

SELECT tblSiteInfo.ID AS CompareID, tblSiteInfo.SiteID, tblPointInfo.ID, tblBugInfo.f_Point, tblPointInfo.MainPointID, tblPointInfo.Point, tblPointInfo.PointType, tblPointInfo.YearID, tblYearData.Year, tblPointInfo.SeasonID, list_Season.Season, tblSiteInfo.SiteCode, tblSiteInfo.Subwatershed, tblSiteInfo.WatercourseName, tblPointInfo.FBI, tblPointInfo.MainFBI, tblSiteInfo.Easting, tblSiteInfo.Northing, tblSiteInfo.Township, tblSiteInfo.Lot, tblSiteInfo.Con, tblSiteInfo.RoadName, tblSiteInfo.Notes AS SiteNotes, tblSiteInfo.DateStamp, tblBugInfo.ID AS BugInfoID, tblBugInfo.BugID, tblBugInfo.BugName, tblBugInfo.Notes AS BugNotes, tblBugLifeStageInfo.ID AS StageInfoID, tblBugLifeStageInfo.f_BugStageID, tblBugLifeStageInfo.Quantity, tblBugLifeStageInfo.LifeStage, tblBugLifeStageInfo.Hilsenhoff, tblBugLifeStageInfo.Notes AS StageNotes, tblSiteInfo.Version, tblSiteInfo.VersionDate
FROM ((((tblBugLifeStageInfo INNER JOIN tblBugInfo ON tblBugLifeStageInfo.f_Bug=tblBugInfo.ID) INNER JOIN tblPointInfo ON tblBugInfo.f_Point=tblPointInfo.ID) INNER JOIN tblSiteInfo ON tblPointInfo.f_SiteID=tblSiteInfo.ID) INNER JOIN tblYearData ON tblPointInfo.YearID=tblYearData.ID) INNER JOIN list_Season ON tblPointInfo.SeasonID=list_Season.ID
ORDER BY tblYearData.Year DESC , tblPointInfo.SeasonID, tblSiteInfo.Subwatershed, tblSiteInfo.WatercourseName, tblSiteInfo.SiteCode;

Are we on the right track?

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

 

[dhookom]

I can't view your SNP file. I guess you would need to use similar code for every group header.

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

I tried adding other headers but I honestly don't know what I am doing, even after reading a bit in the Developer's
Handbook. It looked nuts.

I replaced the snp files with pdf's

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

 

[diwin]

Oh. and I set hide duplicates for the 3 right-most textboxes to "NO".

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

 

[diwin]

I discovered this tutorial about .isvisible, but I can't get it to work either. Any ideas???

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

 

[diwin]

I got it, but there ought to be a simpler way. or a simpler life!

Below is the code and then I added a third pdf, "SUCCESS.pdf" to the url below to show the results in comparison to the others. Notice in the code how it gets thicker as I add a new line at the top of each if..then for each table inner-joined in the recordsource.

I give myself a purple STAR (on a trendy purple background)...

[tab][tab][tab][tab][tab] ^
[tab][tab][tab][tab][tab] < >
[tab][tab][tab][tab][tab] V V

Option Compare Database

Dim LastYear As String
Dim LastSeason As String
Dim LastShed As String
Dim LastCourse As String
Dim LastSite As String
Dim LastMainFBI As String
Dim LastPoint As String
Dim LastPointType As String
Dim LastFBI As String
Dim LastEasting As String
Dim LastNorthing As String
Dim LastTownship As String
Dim LastLot As String
Dim LastCon As String
Dim LastRoadName As String
Dim LastSiteNotes As String
Dim LastBug As String
Dim LastBugNotes As String
Dim LastStage As String
Dim LastQuantity As String
Dim LastHilsenhoff As String
Dim LastStageNotes As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtSeason.Visible = False
Else
    Me.txtSeason.Visible = True
End If

If LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtSubwatershed.Visible = False
Else
    Me.txtSubwatershed.Visible = True
End If

If LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtWatercourseName.Visible = False
Else
    Me.txtWatercourseName.Visible = True
End If

If LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtSiteCode.Visible = False
Else
    Me.txtSiteCode.Visible = True
End If

If LastMainFBI = Trim(Me.txtMainFBI) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtMainFBI.Visible = False
Else
    Me.txtMainFBI.Visible = True
End If

If LastEasting = Trim(Me.txtEasting) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtEasting.Visible = False
Else
    Me.txtEasting.Visible = True
End If

If LastNorthing = Trim(Me.txtNorthing) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtNorthing.Visible = False
Else
    Me.txtNorthing.Visible = True
End If

If LastTownship = Trim(Me.txtTownship) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtTownship.Visible = False
Else
    Me.txtTownship.Visible = True
End If

If LastLot = Trim(Me.txtLot) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtLot.Visible = False
Else
    Me.txtLot.Visible = True
End If

If LastCon = Trim(Me.txtCon) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtCon.Visible = False
Else
    Me.txtCon.Visible = True
End If

If LastRoadName = Trim(Me.txtRoadName) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtRoadName.Visible = False
Else
    Me.txtRoadName.Visible = True
End If

If LastSiteNotes = Trim(Me.txtSiteNotes) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtSiteNotes.Visible = False
Else
    Me.txtSiteNotes.Visible = True
End If

If LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtPoint.Visible = False
Else
    Me.txtPoint.Visible = True
End If

If LastPointType = Trim(Me.txtPointType) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtPointType.Visible = False
Else
    Me.txtPointType.Visible = True
End If

If LastFBI = Trim(Me.txtFBI) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtFBI.Visible = False
Else
    Me.txtFBI.Visible = True
End If

If LastBug = Trim(Me.txtBugName) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtBugName.Visible = False
Else
    Me.txtBugName.Visible = True
End If

If LastBugNotes = Trim(Me.txtBugNotes) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtBugNotes.Visible = False
Else
    Me.txtBugNotes.Visible = True
End If

If LastStage = Trim(Me.txtLifeStage) & "" _
    And LastBug = Trim(Me.txtBugName) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtLifeStage.Visible = False
Else
    Me.txtLifeStage.Visible = True
End If

If LastQuantity = Trim(Me.txtQuantity) & "" _
    And LastStage = Trim(Me.txtLifeStage) & "" _
    And LastBug = Trim(Me.txtBugName) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtQuantity.Visible = False
Else
    Me.txtQuantity.Visible = True
End If

If LastHilsenhoff = Trim(Me.txtHilsenhoff) & "" _
    And LastStage = Trim(Me.txtLifeStage) & "" _
    And LastBug = Trim(Me.txtBugName) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtHilsenhoff.Visible = False
Else
    Me.txtHilsenhoff.Visible = True
End If

If LastStageNotes = Trim(Me.txtStageNotes) & "" _
    And LastStage = Trim(Me.txtLifeStage) & "" _
    And LastBug = Trim(Me.txtBugName) & "" _
    And LastPoint = Trim(Me.txtPoint) & "" _
    And LastSite = Trim(Me.txtSiteCode) & "" _
    And LastCourse = Trim(Me.txtWatercourseName) & "" _
    And LastShed = Trim(Me.txtSubwatershed) & "" _
    And LastSeason = Trim(Me.txtSeason) & "" _
    And LastYear = Trim(Me.txtYear) & "" Then
    Me.txtStageNotes.Visible = False
Else
    Me.txtStageNotes.Visible = True
End If
'******************************************************
If LastYear = Me.txtYear & "" Then
    Me.LineYear.Visible = False
Else
    Me.LineYear.Visible = True
End If

If LastSeason = Me.txtSeason & "" Then
    Me.LineSeason.Visible = False
Else
    Me.LineSeason.Visible = True
End If

If LastShed = Me.txtSubwatershed & "" Then
    Me.LineShed.Visible = False
Else
    Me.LineShed.Visible = True
End If

If LastCourse = Me.txtWatercourseName & "" Then
    Me.LineCourse.Visible = False
Else
    Me.LineCourse.Visible = True
End If

If LastSite = Me.txtSiteCode & "" Then
    Me.LineSite.Visible = False
Else
    Me.LineSite.Visible = True
End If

If LastPoint = Me.txtPoint & "" Then
    Me.LinePoint.Visible = False
Else
    Me.LinePoint.Visible = True
End If

If LastBug = Me.txtBugName & "" Then
    Me.LineBug.Visible = False
Else
    Me.LineBug.Visible = True
End If

LastYear = Me.txtYear & ""
LastSeason = Me.txtSeason
LastShed = Me.txtSubwatershed & ""
LastCourse = Me.txtWatercourseName & ""
LastSite = Me.txtSiteCode
LastMainFBI = Me.txtMainFBI & ""
LastPoint = Me.txtPoint & ""
LastPointType = Me.txtPointType & ""
LastFBI = Me.txtFBI & ""
LastEasting = Me.txtEasting & ""
LastNorthing = Me.txtNorthing & ""
LastTownship = Me.txtTownship & ""
LastLot = Me.txtLot & ""
LastCon = Me.txtCon & ""
LastRoadName = Me.txtRoadName & ""
LastSiteNotes = Me.txtSiteNotes & ""
LastBug = Me.txtBugName & ""
LastBugNotes = Me.txtBugNotes & ""
LastStage = Me.txtLifeStage & ""
LastQuantity = Me.txtQuantity & ""
LastHilsenhoff = Me.txtHilsenhoff & ""
LastStageNotes = Me.txtStageNotes & ""

End Sub

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

 

[dhookom]

Good luck with the code. I still think there is a different solution with using the grouping and extra group header sections. However, if your solution works as expected, go with it.

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

Thanks for your suggestion Duane.

My successful report won't export cleanly to Excel so I am still playing with movelayout and am getting nowhere. I still can't get it to work at all, and I cannot even wrap my brain around it, even after reading as much as I can stand.

Can you show me a Northwind sample with 2 grouping levels using your technique? Here is a Northwind query that I created that should be of the right type.

SELECT Products.ProductID, Products.ProductName, Products.SupplierID, [Order Details].OrderID, [Order Details].Quantity, Orders.CustomerID, Orders.ShipCity, Customers.CompanyName
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((InStr([orders].[customerid],"au"))<>False));

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

 

[dhookom]

Great idea to post the sql from Northwinds. Can you tell me what you want for grouping in the above query?

I don't have time at the moment to test this but should be able to in the next couple days.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I reviewed my solution and think the running sum should be:
Running Sum: Over Group


Duane
Hook'D on Access
MS Access MVP

 

[diwin]

I just need any 2 groupings so I can see how they interact. Then I can duplicate. I will try Over Group with my several test reports.

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

 

[diwin]

No joy..the resulting reports are just a total mess. every column is visible and every row repeats several times.

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

 

[diwin]

Hi Duane.

I keep trying and failing.

Do I need to put txtCountLifeStagesin and the On Format in the Detail section?

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

 

[dhookom]

txtCountLifeStagesin is a text box in the detail section of the report. I will try to create a sample of this and post my results.

Duane
Hook'D on Access
MS Access MVP

 

[diwin]

I keep trying. Can't get it.
Attached is an almost empty db with necessary objects and 2 reports--One shows the way I want it to look, the other, having colour-coded headers, is based on your technique (or so I thought). If you can even delete all headers but two and show me how to do that, it would be enough. Thanks for your help.

The goal is to generate this report in a format that allows clean export to xls format, which my "Good" version won't do. This is the last item in a 4-month db creation project that I am desperate to hand off, and I am stumped.

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

 

[diwin]

Oops!

That was over 2Mb. Take this zip file instead.

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

 

[diwin]

I am slowly learning how Me.Movelayout works, but I still don't fully comprehend.

I found this advice to someone else with the same issue in another forum

I have a "solution" that works, but it does have one small issue which I will
explain at the end.

Set the report with the grouping headers for Stock Type and Stock On Hand.
Place the controls for these header groupings within their respective headers
(align as though they are on the same row).

For each of the two headers, add the Event Procedure for the On Format of:
Me.MoveLayout = False

This aligns all the data across the row to the top.

Put the rest fields in the detail section (make sure to move far enough to
the right as not to run into the Stock On Hand column).

As to the issue...
This works great when you have a defined field lengths or have the controls
set to Can Grow = No.

If you have a long value in Stock Type or Stock On Hand, which requires two
or more lines or will vary from record to record (memo field), you will need
to stretch the Detail section to accommodate.

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

 

[diwin]

Gee. I am getting close. This one is almost there. But I don't know why. Can you tell me what I did right or wrong? --or even better, what I did right??? There is still some weird duplication and some skipping.

The first light blue record for WaterCourseName = "Plum Creek" should be preceded by Subwatershed = "Black Creek". Maybe you understand why.

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