Subreport with where clause in query

[Max Yaffe]

Dear Group,

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]


The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?

Thanks,
Max

 

[Rick Brandt]

Dear Group,

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]


The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?

You should be able to set the MasterLink and ChildLink properties of the
subreport control to do this for you rather than using the WHERE clause in your
query.

 

[Marshall Barton]

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?

Sorry, but, since queries are executed outside the report,
they must use a full reference.

OTOH, you should be able to use the subreport control's Link
Master/Child properties to accomplish what you want. In
this case I think you want to set both properties to Part

 

[Max Yaffe]

I know about linking but that won't work in my case. Unfortunately, I
left out a critical detail - I want to get the TOP 5 records in the
subreport, i.e. the query really looks like:

SELECT TOP 5 Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]


If I were to use a linked sub-report, the query would return records
for all parts and then discard all but the last 5. I want it to
return the last 5 records for the part I'm interested in, hence the
offending where clause.

Any ideas?

Max

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?

Sorry, but, since queries are executed outside the report,
they must use a full reference.

OTOH, you should be able to use the subreport control's Link
Master/Child properties to accomplish what you want. In
this case I think you want to set both properties to Part

 

[Marshall Barton]

Since you must use a full reference for this, the only idea
I have is to park the Part value in a central location the
the query can get to.

A hidden text box on an always open form (or a hidden form
that's used for this kind of thing) is a good place.

Another way is to put the Part value in a global variable
and create a Public function that the query can use to
retrieve the value.
--
Marsh
MVP [MS Access]

I know about linking but that won't work in my case. Unfortunately, I
left out a critical detail - I want to get the TOP 5 records in the
subreport, i.e. the query really looks like:

SELECT TOP 5 Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

If I were to use a linked sub-report, the query would return records
for all parts and then discard all but the last 5. I want it to
return the last 5 records for the part I'm interested in, hence the
offending where clause.

I'm having a problem with designing queries for subreports. I'm
selected a group of records for the subreport where I want to limit
the set accoring to a field specified in the main report. It looks
somewhat like the following:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = [reports]![MAIN_REPORT].[Part]

The subreport uses this either as an SQL query or a stored query.
Works great.

I'd like to be able to use this subreport & query in other forms so I
need to do some sort of relative addressing like:

SELECT Part, Revision, RevDate, Designer
FROM tblRevision
WHERE [Part] = Parent.[Part]

But I can't get this to work. What is the correct syntax for
specifing the parent report of a sub? Is there any clean way to do
this?

Sorry, but, since queries are executed outside the report,
they must use a full reference.

OTOH, you should be able to use the subreport control's Link
Master/Child properties to accomplish what you want. In
this case I think you want to set both properties to Part