Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to filter a subreport to now display the parent

Posted on 2012-04-09
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
LVL 31

Assisted Solution

hnasr earned 100 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.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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, ...post 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 900 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, ...it 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
...you 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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