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_candi
not sure of the syntax or where to put this
The subreport links by CandidateId and that works great.
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_candi
not sure of the syntax or where to put this
The subreport links by CandidateId and that works great.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What I think I need to do is filter the subreport like this:
On_Load event:
Me.Position_Candidate_Id <> [main report].Position_Candidate
is this right ? How does the subreport reference the main report?
I also tried an expression but that didn't work either.
On_Load event:
Me.Position_Candidate_Id <> [main report].Position_Candidate
is this right ? How does the subreport reference the main report?
I also tried an expression but that didn't work either.
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
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
ASKER
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.
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.
<attached is the database scrubbed as requested.>
Attached where?
Attached where?
ASKER
oops, didn't hit the Attach button, here, sorry about that.
Candidates-EE.accdb
Candidates-EE.accdb
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
(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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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]![rptPositionsAnd
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.
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]![rptPositionsAnd
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.
ASKER
increasing points
>> 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:
https://www.experts-exchange.com/questions/20077038/Handling-a-SubReport-that-has-NO-DATA.html
;-)
JeffCoachman
- 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:
https://www.experts-exchange.com/questions/20077038/Handling-a-SubReport-that-has-NO-DATA.html
;-)
JeffCoachman
ASKER
ok, great, thanks (I've already posted my next question [but I did my research first])
ASKER
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)