How to filter a subreport to now display the parent

Posted on 2012-04-09
Last Modified: 2012-04-10
I have a subreport that works great, but I'm refining the report bit by bit

I want to filter the subreport so that it filters out records that display in the parent record.

So the report is like this:

Position (=Accounting Director)   (Position Candidate Id = 14)
    Candidate (=John Doe)  (Candidate Id = 12)
          (subreport = Candidates Other positions)
               Sales: Sales manager (Position Candidate Id = 23, Candidate Id = 12)
               Marketing: Marketing Director (Position Candidate Id = 34, Candidate Id = 12)
               Accounting: Accounting Director (Position Candidate Id = 14, Candidate Id = 12)

So I don't want the Accounting Director to show up in the subreport, since it's the same as the parent record, so the filter would be "where main_report.position_candidate_id <> subreport.position_candidate_id"

not sure of the syntax or where to put this

The subreport links by CandidateId and that works great.
Question by:Alaska Cowboy
  • 8
  • 5
LVL 30

Assisted Solution

hnasr earned 25 total points
ID: 37825639
Linking the main report with sub report, in the sub report control properties sheet, through :
master link fields: Position Candidate Id , Candidate Id
child link fields: Position Candidate Id , Candidate Id.
The two fields in the sub report will be hidden.

Author Comment

by:Alaska Cowboy
ID: 37825733
hnasr, ok, I did this with Candidate Id and it's just like I want it. But it DON'T want Position Candidate Id to be equal . . .

so if an individual is being considered for 3 positions, I want the related positions displayed, but not the main position

Accting Director (pos cand id = 31
     John Doe
           other positions (linked by Candidate Id)
                Sales Mgr
                 Marketing Director
                (but don't display Acctg Director again, pos cand id = 31)

Author Comment

by:Alaska Cowboy
ID: 37826097
What I think I need to do is filter the subreport like this:

On_Load event:
Me.Position_Candidate_Id <> [main report].Position_Candidate_Id

is this right ? How does the subreport reference the main report?

I also tried an expression but that didn't work either.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37826322
The load even will only trigger once (after the report is opened...
What is your plan to keep this synchronized after that?

"A picture is worth a thousand words..."
Why not simply post a sample of this database?

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


Author Comment

by:Alaska Cowboy
ID: 37827664
attached is the database scrubbed as requested.

to see the issue, run the report "rptPositionsAndCandidates"

The first position is "Computer Director", with two candidates:
- Chris Johnson
- Marion Motley

The subreport displays the OTHER positions that they are candidates for, but it ALSO re-displays the position "Computer Director".
- So the subreport should NOT display the position of the parent report.

I need to filter out the record on the subreport that matches the main report.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37827863
<attached is the database scrubbed as requested.>
Attached where?

Author Comment

by:Alaska Cowboy
ID: 37827878
oops, didn't hit the Attach button, here, sorry about that.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37828309
Then I am confused by your Reports design
(use of a Sub report inside a Grouped report...?)
Perhaps you have tried to display too much with this one report...?

If you Group by the Country, then each country's records will be grouped together.
(Ex: In the USA group, USA Records will be listed...)

You are asking that the grouped record *Not* be listed in the group???
(ex.: Under the USA group, you *do not* want USA records listed?, ...very confusing)

If you don't want "Computer Director" listed under "Computer Director", then where would it be listed?

By your same logic, COO also repeats for those records...
Why not request that they be filtered out as well....?

Again, I am confused by your report's somewhat contradictory design.
You seem to be wanting to display all of a persons "Interesting Positions" (with the subreport), under a grouping of a "Specific Position"...?
...Then hide the record that does not match the grouping...?
again, all very confusing...

Perhaps you should rethink this report's design and purpose.
Then include a clear graphical representation of the *Exact* new results you are expecting, based on the sample data.

...or simplify it...
(For example, either use a grouped report or subreports, but try not to mix the two...)


Author Comment

by:Alaska Cowboy
ID: 37828598

ok, I can see where it's confusing, so that will help me make the report better.

The confusing part, on 2nd glance, is that I don't have a title for the sub-report. I've added that and re-posted, the sub-report is titled "Other Positions [name] is being considered for"

So the logic is this:

- display the position and the candidates
    so for computer director, there are two candidates (Johnson, Motley)

- then also display the OTHER positions this candidate is being considered for (in the sub-report)

So as I'm looking at Chris Johnson and considering him for computer director, I would also see, "Oh, yeah, he's being considered for two OTHER positions".

So, focusing on Chris Johnson, who is being considered for three positions, I would see

Position = Computer Director
       Chris Johnson [ various other info]
             Other positions Chris is being considered for
                   Marketing: Print / Radio Advertising
                   COO: Sports Complex Director
                    (but I would not see again "Computer Director")        

Hope that helps . . .

(and the reason I didn't have a sub-report heading is that the subreport "grows", but I don't know how to make a subreport HEADING grow (so the subreport heading shows up [currently] whether or not there are subreport records.)
LVL 74

Accepted Solution

Jeffrey Coachman earned 225 total points
ID: 37829086
<"Other Positions [name] is being considered for">
But the query and the subform show "ALL" Positions [name] is being considered for"

Again, my feeling is that your design here is flawed.
You cant "Hide" a record that is in the group.
No offense, but it makes no sense ..., and besides, can't be done (easily).

Other factors here...:
... your subreport will display even if no records are present
...You are mixing subreports within a grouped report have an addition "Department" top level Grouping field
...You have an external "SortOrder" field...
...The Subreport query is complex...

......all complicate this more...
I still think you should rethink/simplify this report...

With this all being said...
Try this, (sample attached)
I added the Position Description to the subreport's recordsource, and added the criteria that it is *not equal to* the Position Description on the main report.

Does this give you what you wanted?


Author Comment

by:Alaska Cowboy
ID: 37829584

thanks, it was exactly what I was looking for !

but . . . since I'm not a total neophyte, you could have done it by saying "where [Position Candidates].ID <> [Reports]![rptPositionsAndCandidates]![Position_Candidate_Id]", which was my original question, I just didn't know where to put it :-)

>> your subreport will display even if no records are present
- but I originally had it as height = 0, but "Can Grow - Yes", so it wouldn't display if there were no records (I just don't know how to make the subreport header "Can Grow"

>>The Subreport query is complex...
- I don't understand why you say this, it's dirt simple . . .

>> You cant "Hide" a record that is in the group.
- but you just did !

anyway, I got exactly what I was looking for, thanks for your patience.

Author Comment

by:Alaska Cowboy
ID: 37829588
increasing points
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829727
>> You cant "Hide" a record that is in the group.
- but you just did !

Yes, but seriously, I was just trying to get to to re-evaluate the Report's design...

But to be fair, perhaps I did not understand full the purpose and requirements of the report, so I posted that to see if it would get you what you wanted.

As a side note, you can work around the issue of the subreport appearing when no child records are present by using the techniques listed here:



Author Comment

by:Alaska Cowboy
ID: 37830005
ok, great, thanks (I've already posted my next question [but I did my research first])

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

685 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