how can i propogate a subreport filter up to the main report?

[every1luvsVB via AccessMonster.com]

Hello,

I have a fairly standard main report based on a parent table, with a
subreport based on a related child table. Link Master Fields and Link Child
Fields are configured appropriately and I can filter on the subreport,
seemingly, without an issue, via the subreport's filter property.

However, although the subreport returns only the child records that I qualify
in the subreport filter, the main report still returns all parent records.. -
the problem is that I only wish to return the parent records in the main
report that are associated with the child records returned in the subreport..

Hope that makes sense. Does anyone have any ideas?

 

[Duane Hookom]

Could you share how you are filtering the subreport?

 

[every1luvsVB via AccessMonster.com]

Thanks for help Duane..

My subreport filter is:

txtMemberID="H162"

-with txtMemberID being a db column..

There should be 2 child recs returning. This works; except that 189 parent
recs also return rather than only the associated 2. (all other parent recs
have no subreport details..)

Could you share how you are filtering the subreport?

Hello,

[quoted text clipped - 13 lines]

Hope that makes sense. Does anyone have any ideas?

 

[Duane Hookom]

You need to get the txtMemeberID into the main reports query. This might be
done with a domain aggregate function, simple join of a child table, or a
subquery. Since we don't know your significant table or field information,
it is a bit difficult to be more specific.

--
Duane Hookom
MS Access MVP

Thanks for help Duane..

My subreport filter is:

txtMemberID="H162"

-with txtMemberID being a db column..

There should be 2 child recs returning. This works; except that 189 parent
recs also return rather than only the associated 2. (all other parent recs
have no subreport details..)

Could you share how you are filtering the subreport?

Hello,

[quoted text clipped - 13 lines]

Hope that makes sense. Does anyone have any ideas?

 

[every1luvsVB via AccessMonster.com]

Thanks Duane,

I assumed that was the purpose of the Link Child / Master Fields
functionality?

my significant table (tblGradings) has a primary key autonumber (long int)
column 'lngGradingID' which relates to the child table's (tblGradingResults)
foreign long int column 'lngGradingID'. my link master / child fields is
using this relationship..

txtMemberID however, is only in my child table. this is a foreign key to
another table called tblMembers..

-my recordsource for the report's significant table is: tblGradings

-and for the (subreport's) child table is:

SELECT tblGradingResults.lngResultID, tblGradingResults.txtMemberID,
tblGradingResults.lngGradingID, tblGradingResults.txtResult,
tblGradingResults.lngGradeBefore, tblGradingResults.lngGradeAfter,
tblGradingResults.txtComments FROM tblGradingResults;

(-this sql was generated completely automatically by the subreport wizard..)

thanks for advice.

You need to get the txtMemeberID into the main reports query. This might be
done with a domain aggregate function, simple join of a child table, or a
subquery. Since we don't know your significant table or field information,
it is a bit difficult to be more specific.

Thanks for help Duane..

[quoted text clipped - 15 lines]

 

[Duane Hookom]

Set the main report's record source to something like:

SELECT *
FROM tblGradings
WHERE lngGradingID IN (SELECT lngGradingID FROM tblGradingResults WHERE
txtMemberID="H162")

--
Duane Hookom
MS Access MVP

Thanks Duane,

I assumed that was the purpose of the Link Child / Master Fields
functionality?

my significant table (tblGradings) has a primary key autonumber (long int)
column 'lngGradingID' which relates to the child table's
(tblGradingResults)
foreign long int column 'lngGradingID'. my link master / child fields is
using this relationship..

txtMemberID however, is only in my child table. this is a foreign key to
another table called tblMembers..

-my recordsource for the report's significant table is: tblGradings

-and for the (subreport's) child table is:

SELECT tblGradingResults.lngResultID, tblGradingResults.txtMemberID,
tblGradingResults.lngGradingID, tblGradingResults.txtResult,
tblGradingResults.lngGradeBefore, tblGradingResults.lngGradeAfter,
tblGradingResults.txtComments FROM tblGradingResults;

(-this sql was generated completely automatically by the subreport
wizard..)

thanks for advice.

You need to get the txtMemeberID into the main reports query. This might
be
done with a domain aggregate function, simple join of a child table, or a
subquery. Since we don't know your significant table or field information,
it is a bit difficult to be more specific.

Thanks for help Duane..

[quoted text clipped - 15 lines]

Hope that makes sense. Does anyone have any ideas?

 

[every1luvsVB via AccessMonster.com]

Duane thankyou. This works.

I am a little confused however; as writing the record source SQLs so
explicitly for both the significant report AND the subreport almost negates
the need to have the link child fields master fields doesnt it?

Anyway thanks for your help!

Set the main report's record source to something like:

SELECT *
FROM tblGradings
WHERE lngGradingID IN (SELECT lngGradingID FROM tblGradingResults WHERE
txtMemberID="H162")

Thanks Duane,

[quoted text clipped - 35 lines]

 

[every1luvsVB via AccessMonster.com]

Actually the penny just dropped..

the link child / master fields is needed to link each parent report page to
each associated subreport page -the subset of pages you want to return is
another issue.. -I was confusing myself..

thanks again.

Duane thankyou. This works.

I am a little confused however; as writing the record source SQLs so
explicitly for both the significant report AND the subreport almost negates
the need to have the link child fields master fields doesnt it?

Anyway thanks for your help!

Set the main report's record source to something like:

[quoted text clipped - 8 lines]