Solved

Nested report and duplicates

Posted on 2009-07-16
25
480 Views
Last Modified: 2013-11-28
I have a report with several sub reports and I almost have the data the way I want but I can't get the last little tweak.  It was working until two dif Sales Rep could have the same Company.  With Company as a subrpt and Date & wage as a nested sub rpt within the Company sub the duplicates still show.  I have Hide Duplicates set to yes.  I even recreated the subreport.  I tried unnesting but then the data doesn't stay with the Sales Rep.  I've attached an Excel spreadsheet that hopefully makes this more understandable and shows what I want.

HideDuplicatesEE.xlsx
0
Comment
Question by:BobRosas
  • 13
  • 12
25 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 24872831
Did You try setting the unique values or the unique records property for the record source query?
0
 

Author Comment

by:BobRosas
ID: 24873029
Thank you for your quick response.  At least you gave me something to try.  However asking for unique property in the record source of the nested subreport with Date and wage data some how doubled the amount of records.  I attached what it changed my report to.  Any thoughts?

HideDuplicatesEE2.xlsx
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24873364
Hide duplicates will not work because the records are not duplicates. The highlighted rows have different dates and Wages. You will need to Create a totals (aggregate) query as the record source that will combine the records the way you want. Or you can place code in the detail format event to skip the "duplicate" lines.
0
 

Author Comment

by:BobRosas
ID: 24874433
I guess that makes sense.  My record source does not contain duplicates but since I only display Date and Wage in the subform (which appear like dups) I thought it would work.  So could you tell me more about creating an aggregate query?  So far I can't get that to work either.
Thanks
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24874849
Create a new query in design mode. Select the tables or queries you want to use and create the links. Then click on the button in the query design toolbar with the summation symbol (the Greek capital letter Sigma - see image below).  This will create a new row called "Total" with multiple tools in a Combobox. The most important tool is "Group By" - it will group records together by that field. I think you want to Group By your Company field. Play with the tools to see if you can create a query with the records you want. If you create the query you want, you can list that query in your subreport's record source or copy the Query SQL into the subreport's record source.  
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24874867
Here's the image
Sigma.gif
0
 

Author Comment

by:BobRosas
ID: 24874920
As soon as you suggested my record source I've been trying different things.  I came up with a record source that has the following result...
1County Betty Acompany 1/1/01   7
1County Jane  ACompany 1/1/01   7
1County Betty BCompany  2/2/09   6
Because of the Sales Rep, none of them are actual duplicates but don't I need the record source like this in order for the Sales rep to line up correctly with the date?  Then I only display the date and wage in the subreport.  You'd think it could figure out to hide the duplicate dates since I don't show he company.  Anyway, I tried some code in my nested subreport but then no dates showed at all.
So I moved the code to the main report and it appears to randomly hide dates...atleast I haven't found the pattern.
I really apprecate all your help.  I've attached the code that I put in the main form that isn't working.  Your continued help would really be appreciated.  I'd be glad to increase points.  I've spent a lot of time on something I didn't think should be that hard.  Thanks agan!
All this code is so duplicate dates don't show but it's not working.

Dim int_hide_duplicates_sometimes As Integer

Dim str_hide_duplicates_sometimes As String
 

Private Sub Report_Open(Cancel As Integer)

    int_hide_duplicates_sometimes = 0 ' no records printed yet, set it to 1 after first record printed

    str_hide_duplicates_sometimes = "" 'to field in currenr record, if current record changes it is assigned to this variable

End Sub
 

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

    Debug.Print int_hide_duplicates_sometimes, str_hide_duplicates_sometimes, [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

'    Debug.Print int_hide_duplicates_sometimes, str_hide_duplicates_sometimes, PlacementDate

    If int_hide_duplicates_sometimes = 0 Then

        str_hide_duplicates_sometimes = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

    End If

    If int_hide_duplicates_sometimes = 1 Then

        If str_hide_duplicates_sometimes = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate] Then

        'current PlacementDate = previous PlacementDate , hide PlacementDate

        [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate].Visible = False

        Else

            'current PlacementDate different from previous, show PlacementDate

            str_hide_duplicates_sometimes = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

            [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate].Visible = True

        End If

    End If

    int_hide_duplicates_sometimes = 1
 

End Sub

Open in new window

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24875106
I have a meeting to run to. I'll look at it tomorrow.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24878285
In the Detail_Format event to skip a line just set
Cancel = True
So I'm guessing:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static strPreviousPlacementDate  As String

If strPreviousPlacementDate  = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate] Then
   Cancel = True
Else
   strPreviousPlacementDate  = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]
End If

End Sub


While reviewing your code, I noticed a few points:

Private Sub Report_Open(Cancel As Integer)
    int_hide_duplicates_sometimes = 0 ' no records printed yet, set it to 1 after first record printed
    str_hide_duplicates_sometimes = "" 'to field in currenr record, if current record changes it is assigned to this variable
End Sub

Above is not needed. VBA initializes integers to 0 and strings to zero length with the dim statement.


Dim int_hide_duplicates_sometimes As Integer
If int_hide_duplicates_sometimes will be 0 or 1 only, wouldn't Boolean be better ?


    If int_hide_duplicates_sometimes = 0 Then
       ...
    End If
    If int_hide_duplicates_sometimes = 1 Then
       ...
    End if
could be replaced with
    If int_hide_duplicates_sometimes = 0 Then
       ...
    Else
       ...
    End if


str_hide_duplicates_sometimes
It's best to avoid special characters in naming objects.  IMHO
strHideDuplicatesSometimes
would be better and easier to read.

It is best to avoid spaces and other special characters in your naming of objects. An extreme example of the problems you will have using special characters in your names:
I named a textbox
!@#$%^&*()_+= -{}:;"'<,>?/|~
and had Access create an event procedure.  Access converted the name of the textbox to:
Ctl_____________________________
so if the form had the name:
~!@#$%^&*()_+= -{}:;"'<,>?/|~
referencing the textbox would be:
Forms!l_____________________________!Ctl_____________________________
A useful tip for someone who doesn't want someone else (probably even themselves) from reading their code.
And spaces will sometimes cause problems in VBA references even after years of trouble free operation.

References:
http://www.xoc.net/standards/rvbanc.asp
http://www.dhdurso.org/articles/ms-access-naming.html
http://www.acc-technology.com/namconv.htm
http://www.databasedev.co.uk/naming_conv.html
http://en.wikipedia.org/wiki/Leszynski_naming_convention
0
 

Author Comment

by:BobRosas
ID: 24880166
Wow!  All your comments have been very helpful.  Thank you so much.  I've tried to incorporate all of your suggestions but I still can't get it to work.  I'm attaching the revised code and was hoping you could tell me where I went wrong.  I've also increased points.  You've earned them.
Option Compare Database

Option Explicit
 

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

    Static strPreviousPlacementDate  As String

    Dim strHideDuplicatesSometimes As Integer

    

    If strHideDuplicatesSometimes = 0 Then

        strPreviousPlacementDate = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

    Else

        If strPreviousPlacementDate = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate] Then

            Cancel = True

        Else

            strPreviousPlacementDate = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

        End If

    End If

End Sub

Open in new window

0
 

Author Comment

by:BobRosas
ID: 24880404
When I tried testing I wasn't even fallng thru the code so I think I took your example to litteraly and left out more code than I should have.  So I changed the code again (see attached) and now I'm back to the random missing data.  I'll keep trying but obviously I can really use your help.
Thanks
Option Compare Database

Option Explicit

Dim strHideDuplicatesSometimes As Boolean
 

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

    Static strPreviousPlacementDate  As String

    

    If strHideDuplicatesSometimes = False Then

        strPreviousPlacementDate = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

    Else

        If strPreviousPlacementDate = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate] Then

            [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate].Visible = False

            Cancel = True

        Else

            strPreviousPlacementDate = [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate]

            [fsubPlacementCommunity].Report![rptPlacementCommunity].Report![PlacementDate].Visible = True

        End If

    End If

    strHideDuplicatesSometimes = True

End Sub

Open in new window

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24885662
I see where you set strHideDuplicatesSometimes True but nowhere do you set it back to false. Are you using that to initialize strPreviousPlacementDate at form startup? If so, you can use:
If Len(strPreviousPlacementDate &"') = 0 Then
and eliminate strHideDuplicatesSometimes.

BTW: The "str" in strHideDuplicatesSometimes is used to communicate it is a string. You might want to use booHideDuplicatesSometimes instead. Access doesn't care. It is a commonly used naming system to make code more readable. See Naming Conventions below.

Try posting your database and I will look at it. To make it as small as possible and remove all sensitive material, do this:
1. Make a copy of your db.
2. Remove all tables, queries, forms, pages and reports that have nothing to do with the problem.
3. Delete all but 6-12 records in the remaining tables - use shift / Mouse select to select huge blocks of records, then delete.
4. Compact and repair
5. Zip (optional) and attach to a message.


Naming Conventions:
It is best to avoid spaces and other special characters in your naming of objects. An extreme example of the problems you will have using special characters in your names:
I named a textbox
!@#$%^&*()_+= -{}:;"'<,>?/|~
and had Access create an event procedure.  Access converted the name of the textbox to:
Ctl_____________________________
so if the form had the name:
~!@#$%^&*()_+= -{}:;"'<,>?/|~
referencing the textbox would be:
Forms!l_____________________________!Ctl_____________________________
A useful tip for someone who doesn't want someone else (probably even themselves) from reading their code.
And spaces will sometimes cause problems in VBA references even after years of trouble free operation.

References:
http://www.xoc.net/standards/rvbanc.asp
http://www.dhdurso.org/articles/ms-access-naming.html
http://www.acc-technology.com/namconv.htm
http://www.databasedev.co.uk/naming_conv.html
http://en.wikipedia.org/wiki/Leszynski_naming_convention
 

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:BobRosas
ID: 24897323
I'm attaching a portion of the database.  Hopefully it's enough info that you can help me with it.  
I commented out the code in rptPlacementCommunity because nothing was showing.  I tried to leave enough data so each senerio would show.  A reports form should open when you open the database.  If you enter a from date of 1/1/09 and a To date of 6/3/09 the data I'm talking about should alll show.
1)  For Dana Brown I would like the 2nd 1/19/09 and $8.40 not to show similiar to "Senior Living" not showing.
2) For Pat Jones, I would like the 2nd thru 5th dates and wages not to show since they all repeat.
However I added Gary White to show that when the Placement Company changes I would like data and wage to show even if it does repeat.
I actually created the database with 2007 but even tho I added it to a zip file it would not let me attach it.  So I saved it as Access 2000.  I hope all this makes sense.  Thank you for your continued help.  I've raised points again.
DupDb.mdb
0
 

Author Comment

by:BobRosas
ID: 24910277
Is there anything else I can send?  Have you had any luck at all?  I was really hoping that having the database would help.  I could still really use your assistance!
Thanks in advance!
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24910549
I finally got a chance to look at the db. You should have no need to have subreports to create this report. Just link the three tables:
tblMTPlacementCommRpt
dbo_tblFunds
tblMTPlacementCommRpt
in a query in the record source.

You can use grouping, new row and hide duplicates to create the indented report you want. Create the query linking the three tables. Then use the report wizard to create the basic report using the query. Then move specific controls to the Group headers as needed.  This is so fast and easy, you can do it 3-4 times until you get what you want.

You did not include the tables:
dbo_tblFunds
tblMTPlacementCommRpt
But in a few minutes, I created a report looking close to yours. It is called rptDEMO in the attached db.

DupDb.mdb
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24910561
Here is rptDEMO with hide duplicates on
DupDb.mdb
0
 

Author Comment

by:BobRosas
ID: 24917186
Wow!  Thank you so much for all your work.  I didn't realize the subreports aren't needed.  That makes things much simpler.  I max'd out your points.
I made a bunch of changes (called it rptDemo2) and attached my results because unfortunately I still seem to be having my initial problem.  I don't think I gave you enough records for that to show so I added more.  I'd like duplicates to show if the company changes.  For example, I'd lke Date and Wage to show for Donna even tho they are duplicates from above, because the company changed.  If I change the setting to show duplicates then Pat Jones Date, Wage and Hrs show for each benefit, (which I don't want) because the company is the same.
Does that make sense?  I'm sorry to be such a bother.  
DupDb2.mdb
0
 
LVL 39

Accepted Solution

by:
thenelson earned 500 total points
ID: 24919431
In tblMTPlacementCommRpt, ClientFileNo is an autonumber but has duplicates. An autonumber should always be indexed with no duplicates.  In this case it should be a key field.  Since ClientFileNo is a foreign key in dbo_tblFunds, it becomes confusing which record in dbo_tblFunds should refer to which record in tblMTPlacementCommRpt.

tblMTPlacementCommRpt is what in called "non normalized" meaning it has data in fields (rows) that should be in other tables. See below. Name (which should be split to FName and LName), CountyLkUpID, ProgramSpecialist, SupportStaffLkUpID, wage and hours should been one table since they don't change for each name. (I'm assuming they all don't change).  PlacementCompany and PlacementDate should be in another table referring to the "Name" table. PlacementBenefitsLkUpID should be in a third table refining to the "Name" or "Company Table".  I am guessing at the table break down. When you get done, You should not have duplicate records as you do now. This non normalized table is a large part of the problem you are now having with your report.


Maximizing rows (records) and minimizing columns (fields) is the way databases are designed to work and is called table normalization.   To demonstrate this point, notice that Access and all other databases limit the number of columns (fields) but rows (records) are limited only by storage space.  These are the two tests that I apply for normalization:

1. Are there horizontal lists (columns, fields) that can be converted to vertical lists (rows, records).  If I do what will it gain me?  (Usually reduced storage, memory requirements, increased speed and the ability to work with the data easily) What will I lose? (Usually nothing)

2. If I have nn number of similar fields, is there a possibilty that I might later have nn+1 number of those fields?

A very common table design error is something like:
RecordID   Type1    Type2   Type3    ...
1                23
2                              77
3                 17                       7

Which should be designed as:
RecordID   Type   TheValve
1                1           23
2                2           77
3                1           17
3                3             7

Here is a great real world example of the importance of normalization:
http://www.experts-exchange.com/Databases/MS_Access/Q_21540867.html#14773468

A great turorial on Data Normalization
http://www.datamodel.org/NormalizationRules.html

Database normalization basics in Access
http://support.microsoft.com/kb/283878/EN-US/

Understanding Relational Database Design
http://support.microsoft.com/?scid=kb;EN-US;234208


0
 

Author Closing Comment

by:BobRosas
ID: 31604368
Thank you again.  I will go ahead and close this and ask related questions if needed.  I need some time to process all this.  In short I think you are saying to do it all over...bummer!   The non normalized table (tblMTPlacementCommRpt) is actually a temporary table I created with a make table query using 9 other tables.  I then have an append query where I add data from 6 more tables.  I thought this would make things easier because the temp table has exactly what I need it's just not displaying correctly.  But since there doesn't appear to be a quick fix I will try to redo it.
Thanks again for all your time.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24920503
Try posting the db with the individual tables and I'll see what can be done with it.

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson
0
 

Author Comment

by:BobRosas
ID: 24928127
Wow!
That is really nice of you.  I've created a database with the individual tables that the querys are based on.  Part of my reason for the make table was because of the many variables involved especially with dates.  Hopefully I have the right data to account/test for all those variables.  I'd be glad to post another question and award you more points but I don't know how to go about that since others might reply.  Any thoughts?  Again thanks!
DupDb3.mdb
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24954153
The tables look good. They all seem to be normalized. Access performance analyzer had no suggestions for improvement. Access table analyzer had no improvement suggestions for your main table "dbo_tblClients".  I did not run the table analyzer on the other tables
0
 

Author Comment

by:BobRosas
ID: 24954242
Thank you so much!
So if the tables all look good then the problem must be because I'm using a make table with appended data as the source for the report.  Since the data I need is from so many tables I don't know of another way to gather all the info I need to creat the report.  Yet because of the duplicates in the make table, it just doesn't display the way I'd like it to.  Catch 22 I guess.  Thanks for all your work!
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24954917
You should not need to create a new table to run a report. a report can have a select query as its Record Source.  Instead of placing the filter in the query, You can place it in the OpenReport function which is a lot easier and allows the report to be opened without a filter. I made those changes to the form and rptDemo so you can see how to do it. I only placed one criteria in the OpenReport function to save time.

As for as the duplicate records, we can revisit that after you get the report redesigned.

< I'd be glad to post another question and award you more points but I don't know how to go about that since others might reply.>
Don't worry about that. When you post the new question, just post a comment in this question with the URL to the new one to make sure I see it.
DupDb3.mdb
0
 

Author Comment

by:BobRosas
ID: 24956132
Here's the link to part 2.  Hopefully it's ok to post the question the way I did.  
Thanks again!
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_24604435.html
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now