How to filter a subreport to now display the parent

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.
Alaska CowboyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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.
Alaska CowboyAuthor Commented:
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)
Alaska CowboyAuthor Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
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.

Alaska CowboyAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
<attached is the database scrubbed as requested.>
Attached where?
Alaska CowboyAuthor Commented:
oops, didn't hit the Attach button, here, sorry about that.
Jeffrey CoachmanMIS LiasonCommented:
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...)

Alaska CowboyAuthor Commented:

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.)
Jeffrey CoachmanMIS LiasonCommented:
<"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?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alaska CowboyAuthor Commented:

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.
Alaska CowboyAuthor Commented:
increasing points
Jeffrey CoachmanMIS LiasonCommented:
>> 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:


Alaska CowboyAuthor Commented:
ok, great, thanks (I've already posted my next question [but I did my research first])
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.