Solved

How to filter a subreport to now display the parent

Posted on 2012-04-09
14
364 Views
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.
0
Comment
Question by:Alaska Cowboy
  • 8
  • 5
14 Comments
 
LVL 30

Assisted Solution

by:hnasr
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.
0
 
LVL 1

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)
0
 
LVL 1

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

JeffCoachman
0
 
LVL 1

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.
0
 
LVL 74

Expert Comment

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

Author Comment

by:Alaska Cowboy
ID: 37827878
oops, didn't hit the Attach button, here, sorry about that.
Candidates-EE.accdb
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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

JeffCoachman
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37828598
Jeff,

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.)
Candidates-EE.accdb
0
 
LVL 74

Accepted Solution

by:
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, ...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...
(Whew!)
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?

JeffCoachman
Candidates-EE-1-.accdb
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37829584
Jeff,

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

also,
>> 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.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37829588
increasing points
0
 
LVL 74

Expert Comment

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

LOL
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20077038.html

;-)

JeffCoachman
0
 
LVL 1

Author Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

9 Experts available now in Live!

Get 1:1 Help Now